How to write a raw sql in rails scope?

Question

This is scope I have.

scope :abc, -> (start_date, end_date) {
  find_by_sql("select * from xys where created_at >= '#{start_date}' and created_at <= '#{end_date}'")
}

When I am executing the above scope with the following command it returns blank array.

XYZ.abc(start_date, end_date)

Above command generates the proper sql which I can see in the log. And when I execute that sql into mysql it returns data.

I know How to write scope with help of select, where etc...

Question here is, I want to write pure sql inside the scope. How can I write that?

Answer 1

The right way would be to use the following:

scope :abc, ->(start_date, end_date) {
  where("created_at >= ? AND created_at <= ?", start_date, end_date)
}

By calling find_by_sql you're mixing ActiveRecord's scoping system with the find_by_sql method (which I'd only recommend for complicated SQL, which yours is not).

Scoping is actually one of the strengths of the framework (you can, for example, easily combine them together Object.abc(date, date).some_other_scope to create readable queries).

Since you're not scoping, no models are instantiated for you, and the database isn't queried immediately (which allows you to chain scopes); a scope method typically returns something of a ActiveRecord::Relation::ActiveRecord_Relation_[Classname] and not an array.

Update: With today's knowledge I'd write this query as follows (using Range):

scope :abc, ->(start_date, end_date) { where(created_at: (start_date..end_date)) }

With Rails 3 you can even write endless ranges (created_at >= start_date), e.g.

scope :abc, ->(start_date) { where(created_at: (start_date...)) }