My Rails queries are starting to get complicated, should I switch to raw SQL queries? What do you do?

activerecord, ruby-on-rails, sql

Solution

The general idea is to stick to `ActiveRecord`-generated queries as much as possible, and use SQL fragments only where necessary. SQL fragments are explicitly supported because the creators of `ActiveRecord` realised that SQL cannot be completely abstracted away.

Using the the `find` method without SQL fragments is generally rewarded with better maintainability. Given your example, try:

Book.find(:all,
  :conditions => ["created_at >= ? AND created_at <= ? AND updated_at <= ?", 
                  date1, date2, date3]
  :include => :carts)

The `:inlude => :carts` will do the join if you added `has_many :carts` to your `Book` model. As you can see, there does not have to be much SQL involved. Even the quoting and escaping of input can be left to Rails, while still using SQL literals to handle the `>=` and `<=` operators.

Going a little bit further, you can make it even clearer:

class Book < AciveRecord::Base
  # Somewhere in your Book model:
  named_scope :created_between, lambda { |start_date, end_date|
    { :conditions => { :created_at => start_date..end_date } }
  }
  named_scope :updated_before, lambda { |date|
    { :conditions => ["updated_at <= ?", date] }
  }
  # ...
end

Book.created_between(date1, date2).updated_before(date3).find(:all,
  :include => :carts)

Update: the point of the `named_scope`s is, of course, to reuse the conditions. It's up to you to decide whether or not it makes sense to put a set of conditions in a named scope or not.

Problem

My Rails app is starting to need complicated queries. Should I just start using raw SQL queries? What is the trend in the Rails community? Update: I do not have written queries right now, I wanted to ask this question before I start. But here is an example of what I want to do: I have books which have categories. I want to say- ``` Give me all books that were: -created_at (added to store) between date1 and date2 -updated_at before date3 -joined with books that exist in shopping carts right now ``` I haven't written the query yet but I think the rails version will be something like this: ``` books_to_consider = Book.find(:all, :conditions => "created_at <= '#{date2}' AND created_at >= '#{date1}' AND updated_at <= '#{date3}'", :joins => "as b inner join carts as c on c.book_id = b.id") ``` I am not saying ActiveRecord can't handle this query, but is it more accepted to go with raw SQL for readability (or maybe there are other limitations I don't know of yet)?

Original source