sqlobject-discuss

[SQLObject] a complex query question

[Daniel F. <fet...@go...>]

I'm sorry to come up again with a complex query question but I simply
couldn't find a simple solution for this.

My schema is the following: there are zoos, zoos have cages, cages
have animals. And to any of these objects comments can be associated.
For a given zoo I need to select all comments that are either:
comments on the zoo OR comments on a cage belonging to this zoo OR
comments on an animal belonging to a cage belonging to this zoo.

The schema is this:

class zoo( SQLObject ):
    cages = MultipleJoin( 'cage' )

class cage( SQLObject ):
    animals = MultipleJoin( 'animal' )
    zoo = ForeignKey( 'zoo' )

class animal( SQLObject ):
    cage = ForeignKey( 'cage' )

class comment( SQLObject ):
    object_id = Int( ) # this is the 'id' of the object this comment
is associated to
    object_ = StringCol( validator=OneOf( [ 'animal', 'cage', 'zoo' ]
) ) # type of object
    content = UnicodeCol( ) # the actual comment

It might very well be the case that this setup is not optimal but
unfortunately I can't change it. So given a zoo instance with comments
on zoos, cages and animals, does anyone know an elegant query that
will select all comments that belong either to this zoo instance or to
any cage in this zoo or any animal that is in a cage in the zoo?

Thanks very much for any help,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] a complex query question

[Oleg B. <ph...@ph...>]

On Tue, Mar 10, 2009 at 11:59:33PM -0700, Daniel Fetchinson wrote:
> class comment( SQLObject ):
>     object_id = Int( ) # this is the 'id' of the object this comment
> is associated to
>     object_ = StringCol( validator=OneOf( [ 'animal', 'cage', 'zoo' ]
> ) ) # type of object
>     content = UnicodeCol( ) # the actual comment
> 
> select all comments that belong either to this zoo instance or to
> any cage in this zoo or any animal that is in a cage in the zoo?

   With such a setup the only way I can see is to issue a complex UNION
query that unions 3 different queries - SELECT comments for zoo, cages and
animals.
   UNION queries are, of course, impossible with SQLObject tables but
possible with SQLBuilder.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] a complex query question

[Daniel F. <fet...@go...>]

>> class comment( SQLObject ):
>>     object_id = Int( ) # this is the 'id' of the object this comment
>> is associated to
>>     object_ = StringCol( validator=OneOf( [ 'animal', 'cage', 'zoo' ]
>> ) ) # type of object
>>     content = UnicodeCol( ) # the actual comment
>>
>> select all comments that belong either to this zoo instance or to
>> any cage in this zoo or any animal that is in a cage in the zoo?
>
>    With such a setup the only way I can see is to issue a complex UNION
> query that unions 3 different queries - SELECT comments for zoo, cages and
> animals.
>    UNION queries are, of course, impossible with SQLObject tables but
> possible with SQLBuilder.

Thanks Oleg, I'll look at SQLBuilder. Unfortunately I haven't found
working examples of SQLBuilder + UNION constructions so far, if I
don't manage to get it right I'll probably come back :)

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

Re: [SQLObject] a complex query question

[Oleg B. <ph...@ph...>]

On Wed, Mar 11, 2009 at 06:52:17PM -0700, Daniel Fetchinson wrote:
> Unfortunately I haven't found
> working examples of SQLBuilder + UNION constructions so far, if I
> don't manage to get it right I'll probably come back :)

   If you manage to get it right please come back anyway and show us an
example! :)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] a complex query question

[Frank W. <fra...@no...>]

Hi Daniel
Hi Oleg,

I don´t know how to realize this with union but i think this should do it


# plain sql would be sth like this (untested)
select * from comment
where (object='zoo' and object_id=?)
or (object='cage' and object_id in (select id from cage where zoo=?))
or (object='animal' and object_id in (select id from animal where cage
in (select id from cage where zoo=?)))




# could be translated to python with sqlbuilder to sth like this (untested)

from sqlobject.sqlbuilder import *
aZoo = zoo.get(?)
someAnimals = [animal.id for animal in aCage.animals for aCage in aZoo.cages]

sql = SELECT(OR(
	AND(comment.q.object == 'zoo', comment.q.object_id == ? ),
	AND(comment.q.object == 'cage', IN(comment.q.object_id, aZoo.cages) ),
	AND(comment.q.object == 'animal', IN(comment.q.object_id, someAnimals))
))

result = comment._connection.queryAll(sql)

didn´t test it but i think this should work?
maybe the subselect for animals can be included into a single select
select statement in sqlbuilder as well...?

HTH,
Frank


On Thu, Mar 12, 2009 at 10:16, Oleg Broytmann <ph...@ph...> wrote:
> On Wed, Mar 11, 2009 at 06:52:17PM -0700, Daniel Fetchinson wrote:
>> Unfortunately I haven't found
>> working examples of SQLBuilder + UNION constructions so far, if I
>> don't manage to get it right I'll probably come back :)
>
>   If you manage to get it right please come back anyway and show us an
> example! :)
>
> Oleg.
> --
>     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>
> ------------------------------------------------------------------------------
> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
> software that enables intelligent coding and step-through debugging.
> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Re: [SQLObject] a complex query question

[Daniel F. <fet...@go...>]

> Hi Daniel
> Hi Oleg,
>
> I don´t know how to realize this with union but i think this should do it
>
>
> # plain sql would be sth like this (untested)
> select * from comment
> where (object='zoo' and object_id=?)
> or (object='cage' and object_id in (select id from cage where zoo=?))
> or (object='animal' and object_id in (select id from animal where cage
> in (select id from cage where zoo=?)))
>
>
>
>
> # could be translated to python with sqlbuilder to sth like this (untested)
>
> from sqlobject.sqlbuilder import *
> aZoo = zoo.get(?)
> someAnimals = [animal.id for animal in aCage.animals for aCage in
> aZoo.cages]
>
> sql = SELECT(OR(
> 	AND(comment.q.object == 'zoo', comment.q.object_id == ? ),
> 	AND(comment.q.object == 'cage', IN(comment.q.object_id, aZoo.cages) ),
> 	AND(comment.q.object == 'animal', IN(comment.q.object_id, someAnimals))
> ))
>
> result = comment._connection.queryAll(sql)
>
> didn´t test it but i think this should work?
> maybe the subselect for animals can be included into a single select
> select statement in sqlbuilder as well...?
>
> HTH,
> Frank
>
>
> On Thu, Mar 12, 2009 at 10:16, Oleg Broytmann <ph...@ph...> wrote:
>> On Wed, Mar 11, 2009 at 06:52:17PM -0700, Daniel Fetchinson wrote:
>>> Unfortunately I haven't found
>>> working examples of SQLBuilder + UNION constructions so far, if I
>>> don't manage to get it right I'll probably come back :)
>>
>>   If you manage to get it right please come back anyway and show us an
>> example! :)


Hi Frank, thanks a lot for this example! I'll test this and let you
guys know if it worked for me or not. Since I couldn't get this
working with a single query I simply did multiple different queries
and combined the results in python. But if your solution works that
would be even better.

Cheers,
Daniel


-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown