[web2py] Re: Bug? The DAL gives different SQL when query is provided as string.
you're right.
the "query" actually is only the "where" part, and if you serialize that as
a string it loses other attributes necessary to construct the other parts
of the query.
Now, I don't understand why this is a problem: you can:
- cache the results without hitting the database twice using the cache
argument of the select
- display technically what query produced that results, you can use the
._select()
- save the query and then retrieve the results you can, the only caveat is
having the condition linking the tables "alive" and not serialized
linkwebpage2comment = (db.comment.page_id == db.webpage.id)
cond = (db.comment.id > 0) & (db.webpage.title == 'FAQ')
print cond
print 'a'
print db(linkwebpage2comment)(cond)._select(db.comment.body)
print 'b'
cond = str(cond)
print db(linkwebpage2comment)(cond)._select(db.comment.body)
- serialize full query with the _select() and then retrieve results with
executesql()
I'm just missing the point: is this functionality really a limit to your
application ?
Il giorno martedì 27 marzo 2012 01:38:32 UTC+2, Limedrop ha scritto:
>
> Hi Niphlod,
>
> Thanks for your reply. I'm running 1.99.7 and here's the model:
>
> db.define_table('webpage',
> Field('title'),
> Field('body', 'text'))
>
> db.define_table('comment',
> Field('page_id', db.webpage),
> Field('body', 'text'))
>
> The issue is when you have an implicit inner join, but where you only
> want one table in the output.
> I've found a thread where Massimo says that support for stings in
> queries is "clanky" and that
> you need a db(db.table) or db(db.table.field) to determine which table
> is needed.
>
> http://groups.google.com/group/web2py/browse_thread/thread/2024f660a8981558/d4b0e90d701833bb
>
>
> So I guess it isn't supported?
>
>
> On Mar 27, 12:15 am, Niphlod wrote:
> > damn mobile phone screen
> > I got it, seems a simple join, and for my simple model works without a
> > hitch.
> >
> > can you please post your model and tell what web2py version are you
> running
> > ?
> >
> > Il giorno lunedì 26 marzo 2012 01:58:31 UTC+2, Limedrop ha scritto:
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > > The difference is in the FROM clause. In the second SQL the webpage
> > > table is missing.
> >
> > > On Mar 26, 12:51 pm, Niphlod wrote:
> > > > is that because of the "extra" () ?
> > > > every time you call db() the where clause gets "encapsulated" in a
> pair
> > > of
> > > > parenthesis.
> >
> > > > db(db.auth_user.id>0)(db.auth_user.email==localhost)
> >
> > > > and
> >
> > > > db((db.auth_user.id>0) & (db.auth_user.email==localhost))
> >
> > > > give you different raw strings, but the same exact result.
> >
> > > > Il giorno sabato 24 marzo 2012 21:49:58 UTC+1, Limedrop ha scritto:
> >
> > > > > I've copied this from the bottom of another thread.
> >
> > > > > The DAL gives different SQL when a query is provided in string
> format.
> > > > > Is this a bug, or should I not expect this to work?
> >
> > > > > See the example below, the first SQL works, the second SQL raises
> an
> > > > > error.
> >
> > > > > >>>from gluon.dal import Query
> > > > > >>>query = (db.comment.id > 0) & (db.webpage.title == 'FAQ') &
> > > > > (db.comment.page_id == db.webpage.id)
> > > > > >>>query_as_string = str(query)
> > > > > >>>query_from_string = Query(db, query_as_string)
> > > > > >>>print db(query)._select(db.comment.body)
> >
> > > > > SELECT comment.body FROM comment, webpage WHERE (((comment.id >
> 0)
> > > > > AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id));
> >
> > > > > >>>print db(query_from_string)._select(db.comment.body)
> >
> > > > > SELECT comment.body FROM comment WHERE comment.id > 0) AND
> > > > > (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)));
> >
> > > > > Why do this? If the were possible it would enable you to easily
> save
> > > > > a query in the session. For example:
> >
> > > > > ==Controller 1==
> > > > > query1 = (db.comment.id > 0) & (db.webpage.title == 'FAQ') &
> > > > > (db.comment.page_id == db.webpage.id)
> > > > > session.query = str(query1)
> >
> > > > > ==Controller 2==
> > > > > from gluon.dal import Query
> > > > > query2 = Query(db, session.query)
> >
> > > > > This does NOT work because the DAL gives different SQL for query1
> and
> > > > > query2.
[web2py] Re: Bug? The DAL gives different SQL when query is provided as string.
Hi Niphlod,
Thanks for your reply. I'm running 1.99.7 and here's the model:
db.define_table('webpage',
Field('title'),
Field('body', 'text'))
db.define_table('comment',
Field('page_id', db.webpage),
Field('body', 'text'))
The issue is when you have an implicit inner join, but where you only
want one table in the output.
I've found a thread where Massimo says that support for stings in
queries is "clanky" and that
you need a db(db.table) or db(db.table.field) to determine which table
is needed.
http://groups.google.com/group/web2py/browse_thread/thread/2024f660a8981558/d4b0e90d701833bb
So I guess it isn't supported?
On Mar 27, 12:15 am, Niphlod wrote:
> damn mobile phone screen
> I got it, seems a simple join, and for my simple model works without a
> hitch.
>
> can you please post your model and tell what web2py version are you running
> ?
>
> Il giorno lunedì 26 marzo 2012 01:58:31 UTC+2, Limedrop ha scritto:
>
>
>
>
>
>
>
>
>
> > The difference is in the FROM clause. In the second SQL the webpage
> > table is missing.
>
> > On Mar 26, 12:51 pm, Niphlod wrote:
> > > is that because of the "extra" () ?
> > > every time you call db() the where clause gets "encapsulated" in a pair
> > of
> > > parenthesis.
>
> > > db(db.auth_user.id>0)(db.auth_user.email==localhost)
>
> > > and
>
> > > db((db.auth_user.id>0) & (db.auth_user.email==localhost))
>
> > > give you different raw strings, but the same exact result.
>
> > > Il giorno sabato 24 marzo 2012 21:49:58 UTC+1, Limedrop ha scritto:
>
> > > > I've copied this from the bottom of another thread.
>
> > > > The DAL gives different SQL when a query is provided in string format.
> > > > Is this a bug, or should I not expect this to work?
>
> > > > See the example below, the first SQL works, the second SQL raises an
> > > > error.
>
> > > > >>>from gluon.dal import Query
> > > > >>>query = (db.comment.id > 0) & (db.webpage.title == 'FAQ') &
> > > > (db.comment.page_id == db.webpage.id)
> > > > >>>query_as_string = str(query)
> > > > >>>query_from_string = Query(db, query_as_string)
> > > > >>>print db(query)._select(db.comment.body)
>
> > > > SELECT comment.body FROM comment, webpage WHERE (((comment.id > 0)
> > > > AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id));
>
> > > > >>>print db(query_from_string)._select(db.comment.body)
>
> > > > SELECT comment.body FROM comment WHERE comment.id > 0) AND
> > > > (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)));
>
> > > > Why do this? If the were possible it would enable you to easily save
> > > > a query in the session. For example:
>
> > > > ==Controller 1==
> > > > query1 = (db.comment.id > 0) & (db.webpage.title == 'FAQ') &
> > > > (db.comment.page_id == db.webpage.id)
> > > > session.query = str(query1)
>
> > > > ==Controller 2==
> > > > from gluon.dal import Query
> > > > query2 = Query(db, session.query)
>
> > > > This does NOT work because the DAL gives different SQL for query1 and
> > > > query2.
[web2py] Re: Bug? The DAL gives different SQL when query is provided as string.
damn mobile phone screen I got it, seems a simple join, and for my simple model works without a hitch. can you please post your model and tell what web2py version are you running ? Il giorno lunedì 26 marzo 2012 01:58:31 UTC+2, Limedrop ha scritto: > > The difference is in the FROM clause. In the second SQL the webpage > table is missing. > > On Mar 26, 12:51 pm, Niphlod wrote: > > is that because of the "extra" () ? > > every time you call db() the where clause gets "encapsulated" in a pair > of > > parenthesis. > > > > db(db.auth_user.id>0)(db.auth_user.email==localhost) > > > > and > > > > db((db.auth_user.id>0) & (db.auth_user.email==localhost)) > > > > give you different raw strings, but the same exact result. > > > > Il giorno sabato 24 marzo 2012 21:49:58 UTC+1, Limedrop ha scritto: > > > > > > > > > > > > > > > > > > > > > I've copied this from the bottom of another thread. > > > > > The DAL gives different SQL when a query is provided in string format. > > > Is this a bug, or should I not expect this to work? > > > > > See the example below, the first SQL works, the second SQL raises an > > > error. > > > > > >>>from gluon.dal import Query > > > >>>query = (db.comment.id > 0) & (db.webpage.title == 'FAQ') & > > > (db.comment.page_id == db.webpage.id) > > > >>>query_as_string = str(query) > > > >>>query_from_string = Query(db, query_as_string) > > > >>>print db(query)._select(db.comment.body) > > > > > SELECT comment.body FROM comment, webpage WHERE (((comment.id > 0) > > > AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)); > > > > > >>>print db(query_from_string)._select(db.comment.body) > > > > > SELECT comment.body FROM comment WHERE comment.id > 0) AND > > > (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id))); > > > > > Why do this? If the were possible it would enable you to easily save > > > a query in the session. For example: > > > > > ==Controller 1== > > > query1 = (db.comment.id > 0) & (db.webpage.title == 'FAQ') & > > > (db.comment.page_id == db.webpage.id) > > > session.query = str(query1) > > > > > ==Controller 2== > > > from gluon.dal import Query > > > query2 = Query(db, session.query) > > > > > This does NOT work because the DAL gives different SQL for query1 and > > > query2.
[web2py] Re: Bug? The DAL gives different SQL when query is provided as string.
The difference is in the FROM clause. In the second SQL the webpage table is missing. On Mar 26, 12:51 pm, Niphlod wrote: > is that because of the "extra" () ? > every time you call db() the where clause gets "encapsulated" in a pair of > parenthesis. > > db(db.auth_user.id>0)(db.auth_user.email==localhost) > > and > > db((db.auth_user.id>0) & (db.auth_user.email==localhost)) > > give you different raw strings, but the same exact result. > > Il giorno sabato 24 marzo 2012 21:49:58 UTC+1, Limedrop ha scritto: > > > > > > > > > > > I've copied this from the bottom of another thread. > > > The DAL gives different SQL when a query is provided in string format. > > Is this a bug, or should I not expect this to work? > > > See the example below, the first SQL works, the second SQL raises an > > error. > > > >>>from gluon.dal import Query > > >>>query = (db.comment.id > 0) & (db.webpage.title == 'FAQ') & > > (db.comment.page_id == db.webpage.id) > > >>>query_as_string = str(query) > > >>>query_from_string = Query(db, query_as_string) > > >>>print db(query)._select(db.comment.body) > > > SELECT comment.body FROM comment, webpage WHERE (((comment.id > 0) > > AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)); > > > >>>print db(query_from_string)._select(db.comment.body) > > > SELECT comment.body FROM comment WHERE comment.id > 0) AND > > (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id))); > > > Why do this? If the were possible it would enable you to easily save > > a query in the session. For example: > > > ==Controller 1== > > query1 = (db.comment.id > 0) & (db.webpage.title == 'FAQ') & > > (db.comment.page_id == db.webpage.id) > > session.query = str(query1) > > > ==Controller 2== > > from gluon.dal import Query > > query2 = Query(db, session.query) > > > This does NOT work because the DAL gives different SQL for query1 and > > query2.
[web2py] Re: Bug? The DAL gives different SQL when query is provided as string.
is that because of the "extra" () ? every time you call db() the where clause gets "encapsulated" in a pair of parenthesis. db(db.auth_user.id>0)(db.auth_user.email==localhost) and db((db.auth_user.id>0) & (db.auth_user.email==localhost)) give you different raw strings, but the same exact result. Il giorno sabato 24 marzo 2012 21:49:58 UTC+1, Limedrop ha scritto: > > I've copied this from the bottom of another thread. > > > The DAL gives different SQL when a query is provided in string format. > Is this a bug, or should I not expect this to work? > > > See the example below, the first SQL works, the second SQL raises an > error. > > > >>>from gluon.dal import Query > >>>query = (db.comment.id > 0) & (db.webpage.title == 'FAQ') & > (db.comment.page_id == db.webpage.id) > >>>query_as_string = str(query) > >>>query_from_string = Query(db, query_as_string) > >>>print db(query)._select(db.comment.body) > > SELECT comment.body FROM comment, webpage WHERE (((comment.id > 0) > AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)); > > > >>>print db(query_from_string)._select(db.comment.body) > > SELECT comment.body FROM comment WHERE comment.id > 0) AND > (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id))); > > > > Why do this? If the were possible it would enable you to easily save > a query in the session. For example: > > ==Controller 1== > query1 = (db.comment.id > 0) & (db.webpage.title == 'FAQ') & > (db.comment.page_id == db.webpage.id) > session.query = str(query1) > > ==Controller 2== > from gluon.dal import Query > query2 = Query(db, session.query) > > > This does NOT work because the DAL gives different SQL for query1 and > query2.
