The bottom line is that you cannot inner-join a table, then left-join that table to another. In the original query you are left joining audiofile to concert while concert is inner joined to venue.
web2py has a problem: when you perform an outer join, and then a left join, it does not let you specify which is the table (from the inner join) to left join to. Reading this again and again I realized I close this ticket too soon. The problem is more complex than I originally understood but better scoped. Since we cannot change the web2py syntax we need to figure out which table to left join to from the on(query) expression. I now think this is easier to fix than I thought. I will reopen the ticket. Richard. thanks for insisting on this issue. Massimo On Aug 23, 11:49 pm, Richard <richar...@gmail.com> wrote: > yeah I read howesc's diagnosis of the problem (and filed a bug report > with that explanation), but howesc was not able to solve the problem > until you posted a solution. > You said his query was fundamentally wrong. So I was wondering how to > do multiple joins properly in web2py, because I am also facing the > same problem. > > Richard > > On Aug 24, 10:01 am, mdipierro <mdipie...@cs.depaul.edu> wrote: > > > howesc explained it in his posts: "that mixing the join syntax has > > operator > > precedence issues, and that Postgres follows the standard while other > > databases let you get away with the "incorrect" > > syntax:http://archives.postgresql.org/pgsql-general/2006-09/msg01046.php"; > > > Massimo > > > On Aug 23, 6:47 pm, Richard <richar...@gmail.com> wrote: > > > > hi Massimo, > > > > you mentioned that the initial query was invalid (http:// > > > code.google.com/p/web2py/issues/detail?id=52). > > > Could you clarify what was invalid and the proper way to do multiple > > > joins? > > > > Richard > > > > On Aug 17, 4:37 am, howesc <how...@umich.edu> wrote: > > > > > Thanks for the tip! A little tweaking to your suggestion makes it > > > > work: > > > > > c=db.concert > > > > cp=db.concert_purchases > > > > a=db.artist > > > > v=db.venue > > > > af=db.audiofile > > > > test = db(cp.auth_user==3)(c.id==cp.concert)(cp.expires>'2009-08-08 > > > > 09:46:23'). > > > > select(c.name, c.id, cp.date, a.name, v.name, c.date, > > > > af.file,left=(c.on((c.venue==v.id) & (v.name != None)), > > > > cp.on((cp.concert==c.id) & (cp.concert != > > > > None)), > > > > a.on((a.id==c.artist) & (a.name != None)), > > > > af.on(af.concert==c.id)), orderby=~cp.date) > > > > > I put in where clause restrictions to make some of the left joins not > > > > really left joins, I probably don't need to do that for this > > > > particular query. > > > > > thanks again for the suggestion to help me re-craft the query! > > > > > christian > > > > > On Aug 14, 12:38 am, mdipierro <mdipie...@cs.depaul.edu> wrote: > > > > > > Are you sure this does not work? > > > > > > c=db.concert > > > > > cp=db.concert_purchases > > > > > a=db.artist > > > > > db(cp.auth_user==3)(c.id==cp.concert)(cp.expires>'2009-08-08 > > > > > 09:46:23').select(c.name, c.id, cp.date, a.name, v.name, c.date, > > > > > af.file,left=(c.on(c.value==v.id),cp.on(cp.concert==c.id),a.on > > > > > (a.id==c.artist)), orderby=~cp.date) > > > > > > Massimo > > > > > > On Aug 12, 9:09 pm, Richard <richar...@gmail.com> wrote: > > > > > > > hi Christian, > > > > > > I also just got this error message when testing a migration from > > > > > > sqlite to postgres. Thanks for the diagnosis of the problem. > > > > > > Have you found a work around yet using the DAL? > > > > > > Richard > > > > > > > On Aug 10, 4:56 pm, howesc <how...@umich.edu> wrote: > > > > > > > > This is in Postgres. > > > > > > > > I did a little digging, and apparently mixing the JOIN syntax is a > > > > > > > problem. So if i re-write thequeryin this fashion it works: > > > > > > > > SELECT c.name, c.id, cp.date, a.name, v.name, c.date, af.file > > > > > > > FROM venue v > > > > > > > JOIN concert c ON c.venue=v.id > > > > > > > JOIN concert_purchases cp ON cp.concert=c.id > > > > > > > JOIN artist a ON a.id=c.artist > > > > > > > LEFT JOIN audiofile af ON af.concert=c.id > > > > > > > WHERE cp.auth_user=3 > > > > > > > AND c.id=cp.concert > > > > > > > AND cp.expires>'2009-08-08 09:46:23' > > > > > > > ORDER BY cp.date DESC; > > > > > > > > some others have suggested that mixing the join syntax has > > > > > > > operator > > > > > > > precedence issues, and that Postgres follows the standard while > > > > > > > other > > > > > > > databases let you get away with the "incorrect" > > > > > > > syntax:http://archives.postgresql.org/pgsql-general/2006-09/msg01046.phphttp...... > > > > > > > > based on their analysis a standard compliant SQL parser will > > > > > > > interpret > > > > > > > the web2py generatedquerylike this: > > > > > > > SELECT concert.name, concert.id, concert_purchases.date, > > > > > > > artist.name, > > > > > > > venue.name, concert.date, audiofile.file FROM venue, concert, > > > > > > > concert_purchases, (artist LEFT JOIN audiofile ON > > > > > > > audiofile.concert=concert.id) WHERE > > > > > > > ((((concert_purchases.auth_user=3 > > > > > > > AND concert.id=concert_purchases.concert) AND > > > > > > > concert_purchases.expires>'2009-08-08 09:46:23') AND > > > > > > > artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY > > > > > > > concert_purchases.date DESC; > > > > > > > > note the added parens around the last item in the from clause and > > > > > > > the > > > > > > > LEFT JOIN item - hence concert is not visible. if i re-write it > > > > > > > knowing the operator precedence like this: > > > > > > > > SELECT concert.name, concert.id, concert_purchases.date, > > > > > > > artist.name, > > > > > > > venue.name, concert.date, audiofile.file > > > > > > > FROM venue, artist, > > > > > > > concert_purchases, concert LEFT JOIN audiofile ON > > > > > > > audiofile.concert=concert.id WHERE > > > > > > > ((((concert_purchases.auth_user=3 > > > > > > > AND concert.id=concert_purchases.concert) AND > > > > > > > concert_purchases.expires>'2009-08-08 09:46:23') AND > > > > > > > artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY > > > > > > > concert_purchases.date DESC; > > > > > > > > it also works...which explains to me why some of my left joins > > > > > > > work > > > > > > > and some don't - if i'm lucky and the lasttablein the from clause > > > > > > > is > > > > > > > what i am left joining on it works. > > > > > > > > that being said, it looks like i'm the only one with that problem > > > > > > > so i > > > > > > > don't expect you to try and fix that right away. > > > > > > > > thanks, > > > > > > > > Christian > > > > > > > > On Aug 8, 4:17 pm, mdipierro <mdipie...@cs.depaul.edu> wrote: > > > > > > > > > Is this sqlite? The generated SQL looks right to me. Do you see > > > > > > > > anything wrong with it? > > > > > > > > > On Aug 8, 11:48 am, howesc <how...@umich.edu> wrote: > > > > > > > > > > Massimo, > > > > > > > > > > I downloaded the latest released version today: Version > > > > > > > > > 1.65.11 > > > > > > > > > (2009-08-04 16:42:46), and it still fails. here is the > > > > > > > > > generated SQL: > > > > > > > > > > SELECT concert.name, concert.id, concert_purchases.date, > > > > > > > > > artist.name, > > > > > > > > > venue.name, concert.date, audiofile.file FROM venue, concert, > > > > > > > > > concert_purchases, artist LEFT JOIN audiofile ON > > > > > > > > > audiofile.concert=concert.id WHERE > > > > > > > > > ((((concert_purchases.auth_user=3 > > > > > > > > > AND concert.id=concert_purchases.concert) AND > > > > > > > > > concert_purchases.expires>'2009-08-08 09:46:23') AND > > > > > > > > > artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY > > > > > > > > > concert_purchases.date DESC; > > > > > > > > > > Thanks, > > > > > > > > > > Christian > > > > > > > > > > On Aug 6, 11:58 pm, mdipierro <mdipie...@cs.depaul.edu> wrote: > > > > > > > > > > > Please try a new version of web2py (I remember addressing a > > > > > > > > > > bug about > > > > > > > > > > this in the past). if you still have a problem, try print > > > > > > > > > > the _select > > > > > > > > > > and let us see the generate sql. This should definitively > > > > > > > > > > be possible. > > > > > > > > > > > Massimo > > > > > > > > > > > On Aug 6, 6:15 pm, howesc <how...@umich.edu> wrote: > > > > > > > > > > > > Hi all, > > > > > > > > > > > > I'm writing several queries with multiple tables, and > > > > > > > > > > > left joins, and > > > > > > > > > > > the generated SQL is not working with postgres: > > > > > > > > > > > > ProgrammingError: invalid reference to > > > > > > > > > > > FROM-clauseentryfortable > > > > > > > > > > > "concert" > > > > > > > > > > > LINE 1: ..., artist LEFT JOIN audiofile ON > > > > > > > > > > > audiofile.concert=concert.id... > > > > > > > > > > > > > > > > > > > > > > ^ > > > > > > > > > > > HINT: Thereis anentryfortable"concert",butitcannotbe > > > > > > > > > > >referencedfrom thispartof thequery. > > > > > > > > > > > > myqueryis: > > > > > > > > > > > query= db((db.concert_purchases.auth_user == > > > > > > > > > > > auth.user.id) & > > > > > > > > > > > (db.concert.id == > > > > > > > > > > > db.concert_purchases.concert) & > > > > > > > > > > > (db.concert_purchases.expires > now) & > > > > > > > > > > > (db.artist.id == db.concert.artist) & > > > > > > > > > > > (db.venue.id == db.concert.venue)) > > > > > > > > > > > rows =query.select(db.concert.name, db.concert.id, > > > > > > > > > > > db.concert_purchases.date, > > > > > > > > > > > db.artist.name, > > > > > > > > > > > db.venue.name, > > > > > > > > > > > db.concert.date, > > > > > > > > > > > db.audiofile.file, > > > > > > > > > > > > > > > > > > > > > > left=db.audiofile.on(db.audiofile.concert == > > > > > > > > > > > db.concert.id), > > > > > > > > > > > > > > > > > > > > > > orderby=~db.concert_purchases.date) > > > > > > > > > > > > I have web2py Version 1.63.5 (2009-06-03 23:35:00) > > > > > > > > > > > > so, istherea way i can write thequeryto make the left > > > > > > > > > > > join work, > > > > > > > > > > > or should i just do multiple queries? > > > > > > > > > > > > thanks for the help, > > > > > > > > > > > > christian --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
