CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3ruby-on-rails-3.2
Ajedi32
Ajedi32

Reputation: 48438

Using Named Scopes as Subqueries in Rails

In my Rails app, I want to join one table with a named scope of another table. Is there a way to do this without having to rewrite the named scope in pure SQL for my join statement?

Basically, is there a way to do something like this?

class Foo < ActiveRecord::Base
  scope :updated_today, where('updated_at > ?', DateTime.now.prev_day)
end

Bar.joins(Foo.updated_today)

Where Bar.joins generates the following SQL:

SELECT * FROM bars
INNER JOIN
  (SELECT * FROM foos WHERE updated_at > 2012-8-9) AS t0
ON bar_id = bars.id

Upvotes: 3

Views: 2790

Answers (2)

Ajedi32
Ajedi32

Reputation: 48438

I don't believe there's any method specifically designed for doing this. You can, however, use the to_sql method of ActiveRecord::Relation to get the full SQL Query for the scope as a string, which you can then use as a subquery in a join statement, like so:

Bar.joins("INNER JOIN (#{Foo.updated_today.to_sql}) as t0 ON bar_id = bars.id")

Upvotes: 3

BaronVonBraun
BaronVonBraun

Reputation: 4293

You can use the merge method to join scopes to another model's query:

Bar.joins(:foos).merge(Foo.updated_today)

I haven't seen a ton of documentation on this (the Rails API doesn't even have any documentation on the method itself), but here is a pretty decent blog post giving a reasonably detailed example.

Also, just noticed that this is mentioned in a RailsCast on Advanced Queries in Rails 3.

Upvotes: 2

Related Questions