ARel: Add additional conditions to an outer join

Question

I have the following models in my Rails application:

class Shift < ActiveRecord::Base
  has_many :schedules
  scope :active, where(:active => true)
end

class Schedule < ActiveRecord::Base
  belongs_to :shift
end

I wish to generate a collection of all active shifts and eager load any associated schedules that have occurs_on between two given dates. If a shift has no schedules between those dates, it should still be returned in the results.

Essentially, I want to generate SQL equivalent to:

SELECT shifts.*, schedules.*
FROM shifts
  LEFT JOIN schedules ON schedules.shift_id = shifts.id
    AND schedules.occurs_on BETWEEN '01/01/2012' AND '01/31/2012'
WHERE shifts.active = 't';

My first attempt was:

Shift.active.includes(:schedules).where("schedules.occurs_on BETWEEN '01/01/2012' AND '01/31/2012')

The problem is that the occurs_on filtering is done in the where clause, and not in the join. If a shift has no schedules in that period, it is not returned at all.

My second attempt was to use the joins method, but this does an inner join. Again, this will drop all shifts that have no schedules for that period.

I'm frustrated because I know the SQL I want AREL to generate, but I can't figure out how to express it with the API. Anyone?

Answer 1

You can use a SQL fragment as the argument of your joins method call :

Shift.active.joins('LEFT OUTER JOIN schedules ON schedules.occurs_on...')