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

Question

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)?

Answer 1

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_scopes 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.

Answer 2

Like molf is saying with :include, .find() has the advantage of eager loading of children. Also, there are several plugins, like pagination, that will wrap the find function. You'll have to use .find() to use the plugins.

If you have a really complex sql query remember that .find() uses your exact parameter string. You can always inject your own sql code:

:conditions => ["id in union (select * from table...

And don't forget there are a lot of optional parameters for .find()

* :conditions - An SQL fragment like "administrator = 1", [ "user_name = ?", username ], or ["user_name = :user_name", { :user_name => user_name }]. See conditions in the intro.
* :order - An SQL fragment like "created_at DESC, name".
* :group - An attribute name by which the result should be grouped. Uses the GROUP BY SQL-clause.
* :having - Combined with +:group+ this can be used to filter the records that a GROUP BY returns. Uses the HAVING SQL-clause.
* :limit - An integer determining the limit on the number of rows that should be returned.
* :offset - An integer determining the offset from where the rows should be fetched. So at 5, it would skip rows 0 through 4.
* :joins - Either an SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id" (rarely needed), named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations. If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table‘s columns. Pass :readonly => false to override.
* :include - Names associations that should be loaded alongside. The symbols named refer to already defined associations. See eager loading under Associations.
* :select - By default, this is "*" as in "SELECT * FROM", but can be changed if you, for example, want to do a join but not include the joined columns. Takes a string with the SELECT SQL fragment (e.g. "id, name").
* :from - By default, this is the table name of the class, but can be changed to an alternate table name (or even the name of a database view).
* :readonly - Mark the returned records read-only so they cannot be saved or updated.
* :lock - An SQL fragment like "FOR UPDATE" or "LOCK IN SHARE MODE". :lock => true gives connection‘s default exclusive lock, usually "FOR UPDATE".

src: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M002553