It is difficult to understand... Your combined query is not the same as the splitted one...
groupID=db((db.groups.ownerID==5)& db((db.groups.ownerID==id)& In one case you pass id not 5... What you appear to do is a join... I don't know why it is not working because you don't explecitly tell us what is the difference in the output in both case split and "merged" query. Does it return error? Does it return rows but you don't get the rows you want... Notice that when you have more than one table in a query you have to specify the table and field to recorver row value not just the field name for disambiguation in case there is 2 fields with the same name in both table: Query one table : row[fieldname] Query two tables : row[table_name][fieldname] You can try with join web2py syntax that render an SQL Join request according to ANSI-92 join syntax (your request lead to ANSI-89 syntax) Try this : db((db.groups.ownerID == 5) & (db.node.computedSubClassID==SC_RELATEDNAMES)).select(join=[db.groups.on(db.groups.nodeID == db.node.id), db.membership.on(db.membership.nodeID == db.node.id), db.membership.on(db.membership.groupID == db.node.id]) Hope it helps, I am not sure if what I wrote is gonna lead to the result you want, but the idea is there. One last thing you can do for debugging is to extract the SQL of you web2py query : db((db.groups.ownerID == 5) & (db.node.computedSubClassID==SC_RELATEDNAMES)) ._select(join=[db.groups.on(db.groups.nodeID == db.node.id), db.membership.on(db.membership.nodeID == db.node.id), db.membership.on(db.membership.groupID == db.node.id]) Notice the underscode... It will return a string of the SQL generated by web2py for the query... So you may have more clue on what is wrong with the way to write you web2py query by seeing what the SQL is... Richard On Mon, Apr 15, 2013 at 3:43 AM, Annet <[email protected]> wrote: > I have a query, which when I split it up in two queries works and doesn't > work when I combine the two into one query. > > The following code works: > > groupID=db((db.groups.ownerID==5)&(db.groups.nodeID==db.node.id > )&(db.node.computedSubClassID==SC_RELATEDNAMES)).select().first(). > groups.id > > rows=db((db.membership.groupID==groupID)&(db.membership.nodeID==db.node.id > )).select() > > However, these two combined doesn't work: > > rows=db((db.groups.ownerID==id)&(db.groups.nodeID==db.node.id > )&(db.node.computedSubClassID==SC_RELATEDNAMES)&(db.groups.id > ==db.membership.groupID)&(db.membership.nodeID==db.node.id)).select() > > The problems is caused by this part of the code: (db.membership.nodeID== > db.node.id) Why? > > > Kind regards, > > Annet > > -- > > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
