This is related to an earlier post " left outer join change in 1.2.1?"
by Benjamin Ritcey.
I need to use a LEFT OUTER JOIN for its classic purpose:
joining two two tables such that tuples are returned containing
attribute values
of the left hand table even if there is no join on the right hand
table.
From the MySQL console, the following works as desired:
mysql> SELECT afnu_mca_questions., afnu_mca_responses.
-> FROM afnu_mca_questions LEFT OUTER JOIN afnu_mca_responses
-> ON afnu_mca_questions.id =
afnu_mca_responses.afnu_mca_question_id
-> WHERE afnu_mca_questions.afnu_assessment_id = 1 AND
-> (afnu_mca_responses.exam_id = 1 OR afnu_mca_responses.id
IS NULL);
Attribute values for afnu_mca_questions.* (left hand) are returned
even if
afnu_mca_responses.* is NULL.
Reducing the attribute values that are projected to save space, I get
the following in MySQL:
mysql> SELECT afnu_mca_questions.id, afnu_mca_responses.id
-> FROM afnu_mca_questions LEFT OUTER JOIN afnu_mca_responses
-> ON afnu_mca_questions.id =
afnu_mca_responses.afnu_mca_question_id
-> WHERE afnu_mca_questions.afnu_assessment_id = 1 AND
-> (afnu_mca_responses.exam_id = 1 OR afnu_mca_responses.id IS
NULL);
±—±-----+
| id | id |
±—±-----+
| 1 | 1 |
| 4 | NULL |
±—±-----+
2 rows in set (0.00 sec)
Given the above query converted for use in find_by_sql, I have:
@all_question_responses =
AfnuMcaQuestion.find_by_sql(["SELECT afnu_mca_questions.id, " +
" afnu_mca_responses.id " +
"FROM afnu_mca_questions LEFT OUTER
JOIN afnu_mca_responses " +
"ON afnu_mca_questions.id =
afnu_mca_responses.afnu_mca_question_id " +
"WHERE
afnu_mca_questions.afnu_assessment_id = ? AND " +
“(afnu_mca_responses.exam_id = ? OR
afnu_mca_responses.id IS NULL)”,
@afnu_assessment_id, @exam_id]
)
The results of this query are the following:
Object:
- !ruby/object:AfnuMcaQuestion
attributes:
id: “1” - !ruby/object:AfnuMcaQuestion
attributes:
id:
The attribute value for the second “id” should be “4”.
Why is this happening? Is this a bug in find_by_sql?
Thanks,
WJM