Missing values in LEFT OUTER JOIN using find_by_sql (possible bug?)

rubyonrails-talk
1

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:

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:

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);

2

wmciver@gmail.com wrote:

   @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?

The responses id is overwriting the questions id. Instead use

SELECT afnu_mca_questions.id, afnu_mca_responses.id as response_id ...