CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
tybro0103
tybro0103

Reputation: 49723

Active Record: Use hash conditions in on clause

Trying to figure out how to make a query through Active Record and still keep my dignity. The end result in sql should look like:

... left join works on clients.id = works.client_id and works.performed_on >= '2012-01-21' and works.performed_on <= '2012-01-28'...

If the date range were in the WHERE clause I could use hash conditions like so:

where(:performed_on => (date1..date2))

But, I need the range in the ON clause. Any better ideas than painfully writing the full thing out?

In my mind, something like the following would be perfect:

Client.joins('left join works').on('clients.id' => 'works.client_id', 'works.performed_on' => (date1..date2))

Upvotes: 2

Views: 660

Answers (1)

Harish Shetty
Harish Shetty

Reputation: 64363

You could filter the result in the WHERE clause instead of an ON clause.

Client.joins(:works).where(:works => {:performed_on => (date1..date2)})

If you must filter the result in a ON clause then, first construct the JOIN sql:

join_sql = Client.send(:sanitize_sql_for_conditions, [
   "LEFT JOIN works ON clients.id = works.client_id AND (
      works.performed_on >= ? AND works.performed_on <= ? )", 
   date1, date2])

Client.joins(join_sql)

Upvotes: 2

Related Questions