Find_by_sql as a Rails scope

Question

r937 from Sitepoint was kind enough to help me figure out the query I need to return correct results from my database.

What I need is to be able to use this query as a scope and to be able to chain other scopes onto this one.

The query is:

SELECT coasters.*
FROM (
    SELECT order_ridden,
           MAX(version) AS max_version
    FROM coasters
    GROUP BY order_ridden
) AS m
INNER JOIN coasters
ON coasters.order_ridden = m.order_ridden
AND COALESCE(coasters.version,0) = COALESCE(m.max_version,0)

I tried making a scope like so:

  scope :uniques, lambda {
    find_by_sql('SELECT coasters.*
                 FROM (
                   SELECT order_ridden,
                          MAX(version) AS max_version
                   FROM coasters
                   GROUP BY order_ridden
                 ) AS m
                 INNER JOIN coasters
                 ON coasters.order_ridden = m.order_ridden
                 AND COALESCE(coasters.version,0) = COALESCE(m.max_version,0)')
  }

But when I tried chaining another one of my scopes onto it, it failed. Is there a way I can run this query like a normal scope?

score 17 · Accepted Answer · answered Aug 22 '14 at 23:20

17

find_by_sql returns an Array. But you need an ActiveRecord::Relation to chain additional scopes.

One way to rewrite your query using ActiveRecord methods that will return an ActiveRecord::Relation would be to rearrange it a little bit so that the nesting happens in the INNER JOIN portion.

You may want to try something like:

scope :uniques, lambda {
  max_rows = select("order_ridden, MAX(version) AS max_version").group(:order_ridden)
  joins("INNER JOIN (#{max_rows.to_sql}) AS m
    ON coasters.order_ridden = m.order_ridden
   AND COALESCE(coasters.version,0) = COALESCE(m.max_version,0)")
}

answered Aug 22 '14 at 23:20

cschroed

6,304
6
42
56

Hey @cschroed That works perfectly! Thanks. I'm still a bit confused as to how this is actually working. I realise I need an ActiveRecord::Relation but how does that rearrange that you did output an AR::Relation? – rctneil Aug 23 '14 at 07:59
Glad it worked! We wrote this in a way that we could use the `ActiveRecord` query method called `joins` instead of `find_by_sql`. See the other methods [listed here](http://guides.rubyonrails.org/active_record_querying.html#retrieving-objects-from-the-database). They all return an `ActiveRecord::Relation` and can be chained. You can check the class of the return value in the Rails console if you use `Coaster.uniques.class`. – cschroed Aug 23 '14 at 13:24

Find_by_sql as a Rails scope

1 Answers1

Linked