ActiveRecord left outer join with and clause

Question

I am using Rails 3 with ActiveRecord and I cannot get it to generate the query I want without inserting almost plain SQL in it.

I simply want to execute this SQL query (actually the query is a little more complex but it's really this part that I cannot get right).

SELECT DISTINCT users.*, possible_dates.*
FROM users 
LEFT OUTER JOIN possible_dates 
ON possible_dates.user_id = users.id 
AND possible_dates.event_id = MY_EVENT_ID;

which I managed to using

User.includes(:possible_dates)
    .joins("LEFT OUTER JOIN possible_dates
            ON possible_dates.user_id = users.id
            AND possible_dates.event_id = #{ActiveRecord::Base.sanitize(self.id)}"
    )
    .uniq

but I feel that I am missing something to simply add the AND condition of the left join using ActiveRecord query methods. I also tried to do something like this

User.includes(:possible_dates)
    .where('possible_dates.event_id' => self.id)
    .uniq

but this yields (as expected), a query with a WHERE clause at the end and not the AND clause I want on the join.

By the way, self in the two snippets above is an instance of my Event class.

Thank you for your help.

Answer 1

(1 year later...) After looking around, I found out that the best was probably to use arel tables. For the above example, the following code would work.

ut = User.arel_table
pt = PossibleDate.arel_table
et = Event.arel_table

User.joins(
  ut.join(pt, Arel::Nodes::OuterJoin)
   .on(pt[:user_id].eq(u[:id])
      .and(pt[:event_id].eq(1))
   ).join_sql
).includes(:possible_dates).uniq

the 1 in eq(1) should be replaced by the correct event id.