This query works as expected in the sqlite command line client:
SELECT first_name, course_id FROM auth_user LEFT JOIN
training_requirements ON auth_user.id = training_requirements.user_id
WHERE course_id=8 OR course_id IS NULL;
It returns one and only one row for each record in auth_user and the
course_id where there is one and a null value where there is not.
Here's how I write the DAL select:
myrows = db((db.training_requirements.course_id==8) or
(db.training_requirements.course_id==None)).select(
db.auth_user.first_name,
db.training_requirements.course_id,
left=db.training_requirements.on(
db.training_requirements.user_id==db.auth_user.id
))
This query only returns those rows where
training_requirements.course_id is equal to 8, whereas the native
sqlite query returned rows that met either of the two criteria.
What am I doing wrong? Or does the DAL not handle a query like this?
