CODEWITHSUNDEEP
pythonmysqlsqlleft-joinweb2py
Vincent Audebert
Vincent Audebert

Reputation: 1856

Web2Py DAL, left join and operator precedence

In my DB, I've basically 3 tables:

  1. usergroup(id, name, deleted)
  2. usergroup_presentation(id, groupid, presentationid)
  3. presentation(id, name)

I'm trying to run this DAL query:

left_join = db.usergroup_presentation.on((db.usergroup_presentation.group_id==db.usergroup.id)
&(db.usergroup_presentation.presentation_id==db.presentation.id))
result = db(db.usergroup.deleted==False).select(
       db.usergroup.id, 
       db.usergroup.name, 
       db.usergroup_presentation.id, 
       left=left_join, 
       orderby=db.usergroup.name)

And SQL returns this errors: Unknown column 'presentation.id' in 'on clause'

The generated SQL looks something like that:

SELECT  usergroup.id, usergroup.name, usergroup_presentation.id 
FROM presentation, usergroup
LEFT JOIN usergroup_presentation ON ((usergroup_presentation.group_id = usergroup.id) AND (usergroup_presentation.presentation_id = presentation.id)) 
WHERE (usergroup.deleted = 'F')
ORDER BY usergroup.name;

I did some researches on Google and I got this: http://mysqljoin.com/joins/joins-in-mysql-5-1054-unknown-column-in-on-clause/

Then I tried to run this query directly in my DB:

SELECT  usergroup.id, usergroup.name, usergroup_presentation.id 
FROM (presentation, usergroup)
LEFT JOIN usergroup_presentation ON ((usergroup_presentation.group_id = usergroup.id) AND (usergroup_presentation.presentation_id = presentation.id)) 
WHERE (usergroup.deleted = 'F')
ORDER BY usergroup.name;

And indeed it works when adding the brackets around the FROM tables.

My question is how can I generate a SQL query like this (with brackets) with DAL without executing a basic executesql ?

Even better, I would like to get a cleaner SQL query using INNER JOIN and LEFT JOIN. I don't know if it's possible with my query though.

Upvotes: 1

Views: 1397

Answers (1)

Massimo
Massimo

Reputation: 1653

I believe this has now been fixed in trunk. Please help us check it. P.S. next time open a ticket (https://code.google.com/p/web2py/issues/list) and it will be fixed sooner.

Upvotes: 1

Related Questions