This one is on MySQL by I believe this is also happening in Postgres
based on previous searches in the groups, the problem appears to be in
the parsing of the ORM code
Consider the following controller code:
# Grab all questions
questions = db(
(db.questions.is_visible==True) &\
(db.member_properties.auth_user==db.questions.created_by) &\
(db.member_properties.property_id==db.member_properties_skel.id) &\
(db.member_properties_skel.property_name=='m_display_name') &\
(db.auth_roles.id==db.auth_users.auth_role_id) &\
(db.auth_users.id==db.questions.created_by))._select(\
db.questions.title,
db.questions.created_on,
db.questions.modified_on,
db.questions.views,
db.questions.votes_up,
db.questions.votes_dn,
db.questions.is_answered,
db.questions.is_featured,
db.member_properties.property_value,
db.answers.id.count(),
db.auth_roles.role_name,
db.questions.is_closed,
left=db.answers.on(db.answers.question_id==db.questions.id),
groupby=db.questions.id,
orderby=~db.questions.modified_on,
limitby=(offset, num_recs))
"_select", returns (Formatted for readability):
SELECT
questions.title,
questions.created_on,
questions.modified_on,
questions.views,
questions.votes_up,
questions.votes_dn,
questions.is_answered,
questions.is_featured,
member_properties.property_value,
COUNT(answers.id),
auth_roles.role_name,
questions.is_closed
FROM
questions,
auth_users,
member_properties,
member_properties_skel,
auth_roles LEFT JOIN answers ON answers.question_id=questions.id,
WHERE (((((
questions.is_visible='T'
AND member_properties.auth_user=questions.created_by)
AND member_properties.property_id=member_properties_skel.id)
AND member_properties_skel.property_name='m_display_name')
AND auth_roles.id=auth_users.auth_role_id)
AND auth_users.id=questions.created_by)
GROUP BY
questions.id
ORDER BY
questions.modified_on DESC
LIMIT 30 OFFSET 0;
The problem is in this line:
auth_roles LEFT JOIN answers ON answers.question_id=questions.id,
The ON clause is expecting a relationship between the two referenced
tables, in my particular case, the error reported is "Unknown column
'questions.id' in 'on clause'"
By moving the left join statement to the "questions" as so:
questions LEFT JOIN answers ON answers.question_id=questions.id,
Solves the problem, I do not know, however, how to make this work
using the ORM, if anyone could share some thoughts or solutions it'd
be appreciated..
(All this is on MySQL 5.1 and web2py 1.74.5)
Thanks!
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
