This is in Postgres. I did a little digging, and apparently mixing the JOIN syntax is a problem. So if i re-write the query in 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.php http://stackoverflow.com/questions/187146/inner-join-outer-join-is-the-order-of-tables-in-from-important based on their analysis a standard compliant SQL parser will interpret the web2py generated query like 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 last table in 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-clause entry for table > > > > "concert" > > > > LINE 1: ..., artist LEFT JOIN audiofile ON > > > > audiofile.concert=concert.id... > > > > ^ > > > > HINT: There is an entry for table "concert", but it cannot be > > > > referenced from this part of the query. > > > > > my query is: > > > > 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, is there a way i can write the query to 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 -~----------~----~----~----~------~----~------~--~---
