Rails SQL Left Join

Question

My models:

class Student
  has_many :grades
  has_many :courses, :through => :grades
end

class Grades
  belongs_to :student
  belongs_to :course
end

class Course
  has_many :grades
  has_many :students, :through => :grades
end

On my view page, for a student, I need to show all the courses and associated grades (if any)

Student: Joe Blow
Course            Grade
Sociology         A
Biology           B
Chemistry 
...

I am using a left join to handle this query

@courses = Course.find(:all,
  :joins => "as c left join grades g on c.id = g.grade_id and g.user_id = 1",
  :select => "c.id, c.name, g.grade")

When I run this query, if there is a matching grade, all is fine but if there isn't a matching grade for a course, even though it returns the course record, it returns a nil for course id, so I'm puzzled why this is so. If I run a straight SQL query, it returns the course id for all courses regardless of grade matches. I'm using the inspect method to see the results

logger.debug @courses.first.inspect + ", " + @courses.first.grade.inspect

I also noticed an oddity in referencing grade, should I be using the singular or plural of grade?

Answer 1

I believe the problem has to do with your join statement.

"and g.user_id = 1" is more of a WHERE condition than a join term. However, as a condition the select won't grab rows where there's no grade for this user, so you need to a NULL term.

What you want is more like this:

@courses = Course.find(:all,
  :joins => "as c left join grades g on c.id = g.grade_id",
  :conditions => "g.user_id = 1 OR g.user_id IS NULL)",
  :select => "c.id, c.name, g.grade")