Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ActiveRecord left outer join with and clause

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.

like image 479
Daniel Perez Avatar asked Sep 14 '13 13:09

Daniel Perez


1 Answers

(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.

like image 139
Daniel Perez Avatar answered Sep 21 '22 15:09

Daniel Perez