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
-~----------~----~----~----~------~----~------~--~---
