sql – LEFT OUTER JOIN in Rails 4

sql – LEFT OUTER JOIN in Rails 4

You can pass a string that is the join-sql too. eg joins(LEFT JOIN StudentEnrollment se ON c.id = se.course_id)

Though Id use rails-standard table naming for clarity:

joins(LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id)

If anyone came here looking for a generic way to do a left outer join in Rails 5, you can use the #left_outer_joins function.

Multi-join example:

Ruby:

Source.
 select(sources.id, count(metrics.id)).
 left_outer_joins(:metrics).
 joins(:port).
 where(ports.auto_delete = ?, true).
 group(sources.id).
 having(count(metrics.id) = 0).
 all

SQL:

SELECT sources.id, count(metrics.id)
  FROM sources
  INNER JOIN ports ON ports.id = sources.port_id
  LEFT OUTER JOIN metrics ON metrics.source_id = sources.id
  WHERE (ports.auto_delete = t)
  GROUP BY sources.id
  HAVING (count(metrics.id) = 0)
  ORDER BY sources.id ASC

sql – LEFT OUTER JOIN in Rails 4

There is actually a Rails Way to do this.

You could use Arel, which is what Rails uses to construct queries for ActiveRecrods

I would wrap it in method so that you can call it nicely and pass in whatever argument you would like, something like:

class Course < ActiveRecord::Base
  ....
  def left_join_student_enrollments(some_user)
    courses = Course.arel_table
    student_entrollments = StudentEnrollment.arel_table

    enrollments = courses.join(student_enrollments, Arel::Nodes::OuterJoin).
                  on(courses[:id].eq(student_enrollments[:course_id])).
                  join_sources

    joins(enrollments).where(
      student_enrollments: {student_id: some_user.id, id: nil},
      active: true
    )
  end
  ....
end

There is also the quick (and slightly dirty) way that many use

Course.eager_load(:students).where(
    student_enrollments: {student_id: some_user.id, id: nil}, 
    active: true
)

eager_load works great, it just has the side effect of loding models in memory that you might not need (like in your case)
Please see Rails ActiveRecord::QueryMethods .eager_load
It does exactly what you are asking in a neat way.

Leave a Reply

Your email address will not be published.