sqlobject-discuss

[SQLObject] Can table be joined with itself?

[Marcin W. <wo...@un...>]

Hello,

Is it possible to make a query like this in SQLObject?
SELECT B.id FROM B, A as A1, A as A2
WHERE A1.b_id =3D B.id AND A2.b_id =3D B.id AND A1.x =3D 123 AND A2.x =3D=
 234;

Marcin

--=20
Marcin Wojdyr  |  http://www.unipress.waw.pl/~wojdyr

Re: [SQLObject] Can table be joined with itself?

[Andrew B. <and...@pu...>]

On Fri, Aug 20, 2004 at 02:14:46PM +0200, Marcin Wojdyr wrote:
> Hello,
> 
> Is it possible to make a query like this in SQLObject?
> SELECT B.id FROM B, A as A1, A as A2
> WHERE A1.b_id = B.id AND A2.b_id = B.id AND A1.x = 123 AND A2.x = 234;

This might work:

B.select('A1.b_id = B.id AND A2.b_id = B.id AND A1.x = 123 AND A2.x = 234',
         clauseTables=['A as A1', 'A as A2'])
     
-Andrew.

Re: [SQLObject] Can table be joined with itself?

[Marcin W. <wo...@un...>]

>> Hello,
>> Is it possible to make a query like this in SQLObject?
>> SELECT B.id FROM B, A as A1, A as A2
>> WHERE A1.b_id =3D B.id AND A2.b_id =3D B.id AND A1.x =3D 123 AND A2.x =
=3D 234;
>
> Not sure what your intent is, but just in case its a simple "lookup" st=
yle
> reference back to the same table, here's a real life example:

No, it was not that case. Probably it can be done only with low-level
select(query_string), as Andrew Bennetts wrote.

BTW, I noticed that when I access MultipleJoin column, it results
in many DB queries. First only IDs are fetched, and then all rows/objects
one by one. Is there a reason, why all data is not fetched in one
query, like in eg. selectBy()?

I tried to change it to:

   def performJoin(self, inst):
       return self.otherClass.select("%s =3D %s" % (self.joinColumn, inst=
.id))

and it works for me, althogh probably won't work in every case (?).

Marcin

--=20
Marcin Wojdyr  |  http://www.unipress.waw.pl/~wojdyr