Rails - Dec 2007 - Missing values in LEFT OUTER JOIN using find_by_sql (possible bug?)

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

wmciver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org 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 ...

-- 
We develop, watch us RoR, in numbers too big to ignore.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---