Re: [SQLObject] ticket/294
> > question is if there was any way to select the number of animals from > > zoo 'myzoo' in the above example (using inheritance) with a single > > query. Apparently the above query is not gonna work but is there > > another way? Of course one can do several queries but that might hurt > > performance. If there is such a single query I'll continue using > > inheritance if there is none then I'll just design my models such that > > there is no inheritance at all. > >If you don't need different classes from one .select() you probably > could go with simple *Python* inheritance: > > class named( SQLObject ): > name = StringCol( ) > > class zoo( named ): > # name = StringCol( ) > cages = MultipleJoin( 'cage' ) > > etc... >The column 'name' will be included in the every table. That sounds like a good idea, I'll give it a try, thanks. - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] ticket/294
On Mon, Oct 08, 2007 at 09:06:59PM +0200, Daniel Fetchinson wrote: > question is if there was any way to select the number of animals from > zoo 'myzoo' in the above example (using inheritance) with a single > query. Apparently the above query is not gonna work but is there > another way? Of course one can do several queries but that might hurt > performance. If there is such a single query I'll continue using > inheritance if there is none then I'll just design my models such that > there is no inheritance at all. If you don't need different classes from one .select() you probably could go with simple *Python* inheritance: class named( SQLObject ): name = StringCol( ) class zoo( named ): # name = StringCol( ) cages = MultipleJoin( 'cage' ) etc... The column 'name' will be included in the every table. Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] ticket/294
> > joins = [ ]
> > joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) )
> > joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) )
> >
> > print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
> >
> >generates the query
> >
> > SELECT COUNT(*) FROM named LEFT JOIN cage ON ((animal.cage_id) =
> (cage.id)) LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
> > WHERE (((named.name) = ('myzoo')) AND ((named.child_name) = ('animal')))
> >
> >for which SQLite returns the error:
> >
> > sqlobject.dberrors.OperationalError: no such column: animal.cage_id
>
>In case of inheritance a call to .select() can produce a list of rows
> from the different tables (an instances of the different descendant of the
> parent class). This is both the strong point of the inheritance and its
> limitation. Inheritance works in two stages. At the first stage it selects
> IDs from the parent table (hence "FROM named" in the query above); at the
> second stage it draws the real objects from different tables. Because of
> these two stages inheritance cannot be used with complex queries. Joins
> with tables that lay lower in the hierarchy certainly confuses inheritance.
Yes, I understand that implementing inheritance is very tricky. The
question is if there was any way to select the number of animals from
zoo 'myzoo' in the above example (using inheritance) with a single
query. Apparently the above query is not gonna work but is there
another way? Of course one can do several queries but that might hurt
performance. If there is such a single query I'll continue using
inheritance if there is none then I'll just design my models such that
there is no inheritance at all.
Thanks a lot,
Daniel
-
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] ticket/294
On Mon, Oct 08, 2007 at 08:48:09PM +0400, Oleg Broytmann wrote:
> joins = [ ]
> joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) )
> joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) )
>
> print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
>
>generates the query
>
> SELECT COUNT(*) FROM named LEFT JOIN cage ON ((animal.cage_id) = (cage.id))
> LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
> WHERE (((named.name) = ('myzoo')) AND ((named.child_name) = ('animal')))
>
>for which SQLite returns the error:
>
> sqlobject.dberrors.OperationalError: no such column: animal.cage_id
In case of inheritance a call to .select() can produce a list of rows
from the different tables (an instances of the different descendant of the
parent class). This is both the strong point of the inheritance and its
limitation. Inheritance works in two stages. At the first stage it selects
IDs from the parent table (hence "FROM named" in the query above); at the
second stage it draws the real objects from different tables. Because of
these two stages inheritance cannot be used with complex queries. Joins
with tables that lay lower in the hierarchy certainly confuses inheritance.
Oleg.
--
Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
-
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] ticket/294
On Mon, Oct 08, 2007 at 07:03:14PM +0200, Daniel Fetchinson wrote:
> > SELECT COUNT(*) FROM named LEFT JOIN cage ON ((animal.cage_id) = (cage.id))
> > LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
> > WHERE (((named.name) = ('myzoo')) AND ((named.child_name) = ('animal')))
> >
> >for which SQLite returns the error:
> >
> > sqlobject.dberrors.OperationalError: no such column: animal.cage_id
>
> The result should be 5 because that query should select the total
> number of animals in the zoo 'myzoo'. Without inheritance the query
> works and prints 5, hence my guess that there is a problem with
> inheritance. The query should return the same result with or without
> inheritance, or?
Without inheritance the query is completely different:
SELECT COUNT(*) FROM animal LEFT JOIN cage ON ((animal.cage_id) = (cage.id))
LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
WHERE ((zoo.name) = ('myzoo'))
Yes, that gives 5.
Oleg.
--
Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
-
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] ticket/294
> > > > http://sqlobject.gcu.info/trac/ticket/294
> > >
> > >If you replace the first join with (replace None with animal table)
> > > joins.append( LEFTJOINOn( animal, cage, animal.q.cageID==cage.q.id ) )
> > >would it help?
> >
> > Hi Oleg, this thing would be of interest to me as well.
> >
> > After doing the change you advised the query runs without an exception
> > but prints 0 which is obviously incorrect. I'm on sqlite, maybe
> > somebody else could test with other backends?
>
> joins = [ ]
> joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) )
> joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) )
>
> print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
>
>generates the query
>
> SELECT COUNT(*) FROM named LEFT JOIN cage ON ((animal.cage_id) = (cage.id))
> LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
> WHERE (((named.name) = ('myzoo')) AND ((named.child_name) = ('animal')))
>
>for which SQLite returns the error:
>
> sqlobject.dberrors.OperationalError: no such column: animal.cage_id
>
>The problem is that there is no table 'animal' in the FROM list. I am
> trying to put the table there:
>
> joins = [ ]
> joins.append( LEFTJOINOn( animal, cage, animal.q.cageID==cage.q.id ) )
> joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) )
>
> print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
>
>which leads to the query
>
> SELECT COUNT(*) FROM named, animal LEFT JOIN cage ON ((animal.cage_id) =
> (cage.id)) LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
> WHERE (((named.name) = ('myzoo')) AND ((named.child_name) = ('animal')))
>
>The result is '0', though - no rows are found. Now I don't know what
> a query you expected.
The result should be 5 because that query should select the total
number of animals in the zoo 'myzoo'. Without inheritance the query
works and prints 5, hence my guess that there is a problem with
inheritance. The query should return the same result with or without
inheritance, or?
-
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] ticket/294
On Fri, Oct 05, 2007 at 12:01:35AM +0200, Daniel Fetchinson wrote:
> > > http://sqlobject.gcu.info/trac/ticket/294
> >
> >If you replace the first join with (replace None with animal table)
> > joins.append( LEFTJOINOn( animal, cage, animal.q.cageID==cage.q.id ) )
> >would it help?
>
> Hi Oleg, this thing would be of interest to me as well.
>
> After doing the change you advised the query runs without an exception
> but prints 0 which is obviously incorrect. I'm on sqlite, maybe
> somebody else could test with other backends?
joins = [ ]
joins.append( LEFTJOINOn( None, cage, animal.q.cageID==cage.q.id ) )
joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) )
print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
generates the query
SELECT COUNT(*) FROM named LEFT JOIN cage ON ((animal.cage_id) = (cage.id))
LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
WHERE (((named.name) = ('myzoo')) AND ((named.child_name) = ('animal')))
for which SQLite returns the error:
sqlobject.dberrors.OperationalError: no such column: animal.cage_id
The problem is that there is no table 'animal' in the FROM list. I am
trying to put the table there:
joins = [ ]
joins.append( LEFTJOINOn( animal, cage, animal.q.cageID==cage.q.id ) )
joins.append( LEFTJOINOn( None, zoo, cage.q.zooID==zoo.q.id ) )
print animal.select( zoo.q.name=='myzoo', join=joins ).count( )
which leads to the query
SELECT COUNT(*) FROM named, animal LEFT JOIN cage ON ((animal.cage_id) =
(cage.id)) LEFT JOIN zoo ON ((cage.zoo_id) = (zoo.id))
WHERE (((named.name) = ('myzoo')) AND ((named.child_name) = ('animal')))
The result is '0', though - no rows are found. Now I don't know what
a query you expected.
PostgreSQL reports exactly the same error:
psycopg2.ProgrammingError: missing FROM-clause entry for table "animal"
so it's not a backend problem.
Oleg.
--
Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
-
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
[SQLObject] ticket/294
> > http://sqlobject.gcu.info/trac/ticket/294 > >If you replace the first join with (replace None with animal table) > joins.append( LEFTJOINOn( animal, cage, animal.q.cageID==cage.q.id ) ) >would it help? Hi Oleg, this thing would be of interest to me as well. After doing the change you advised the query runs without an exception but prints 0 which is obviously incorrect. I'm on sqlite, maybe somebody else could test with other backends? - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] ticket/294
On Wed, Oct 03, 2007 at 08:59:27PM +0200, Daniel Nogradi wrote: > http://sqlobject.gcu.info/trac/ticket/294 If you replace the first join with (replace None with animal table) joins.append( LEFTJOINOn( animal, cage, animal.q.cageID==cage.q.id ) ) would it help? Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
[SQLObject] ticket/294
I was wondering what the forseeable future of http://sqlobject.gcu.info/trac/ticket/294 is. Are there plans to fix this bug or is the general approach is that inheriable sqlobjects should only be used in simple cases? It would be great to know because I need to design my model accordingly. Cheers, Daniel - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
