Hi guys,
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.
Thanks,
Vincent.
--
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/groups/opt_out.
