How do I write arbitrary SQL queries in Ruby on Rails?

December 13, 2005 1:49 PM

How do I write arbitrary SQL queries in Ruby on Rails?

I'm sort of learning as I go, but I can't figure this one out. All my current queries are something like MyObject.find(various conditions). That returns an array of MyObjects, and is sufficent for most purposes. But now I want to create a list of the most popular items, with a query like "select name, count(*) from MyObjects group by name order by 2". That's not going to return MyObjects, that's going to return arbitrary data columns. So how do I do that?

posted by smackfu to Computers & Internet (9 answers total)

What you're looking for is find_by_sql instead of find. :)
posted by Kickstart70 at 2:00 PM on December 13, 2005

Best answer: Let's say you're adding it to the MyObject model file (models/my_object.rb).

def self.popular_items
  MyObject.find_by_sql("select m.*, count(*) as count from my_objects ETC ETC ETC")
end

From your controller (or view, etc)..

for item in MyObject.popular_items
 blah blah
end

Simple as that.
posted by wackybrit at 2:00 PM on December 13, 2005

Or, you can do more with find than you are currently doing, for that matter.

Example:
find(:all, :group => 'name', :order => '2')
posted by Kickstart70 at 2:03 PM on December 13, 2005

Best answer: While I suspect you're looking for find_by_sql(), if you're looking to do just select a few rows without getting a model involved, try this:

ActiveRecord::Base.connection.select_one('SELECT COUNT(*) FROM mytable')
or
ActiveRecord::Base.connection.execute('SELECT * FROM mytable')
posted by revgeorge at 2:18 PM on December 13, 2005

Response by poster: Thanks! The part I was missing was that I could add ",count(*) as count" and then just call "object.count" to access that returned value, even though it wasn't a real table column.

(It does seem like it's perverting the object model, but it works, so who cares.)

And thanks revgeorge, that is what I thought I was looking for, but find_by_sql seems like it should work for my purposes.
posted by smackfu at 2:21 PM on December 13, 2005

(It does seem like it's perverting the object model, but it works, so who cares.)

That's what the model is for, all the fiddly, model specific stuff. While raw SQL is often frowned upon, it's unavoidable in many cases or in situations with certain optimizations. (I mean, you could probably do an eager load then do counts in Ruby itself, but that can be incredibly memory hungry and rather sloppy, even if it looks nice in code.)
posted by wackybrit at 2:27 PM on December 13, 2005

MyObject.find(:all, :conditions = ["title LIKE :search AND status = :status", {:search => "%my%", :status = "10"}, :select => "*, substring(title, 1, 10) AS title_sub", :order => "id DESC")
posted by sd at 3:45 PM on December 13, 2005

( add a ] after "10"} )
posted by sd at 3:46 PM on December 13, 2005

Response by poster: At some point, the raw SQL becomes more readable.
posted by smackfu at 12:22 PM on December 14, 2005 [1 favorite]