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

Question

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?

Answer 1

If you use MyObject.find_by_sql that should solve your problems. Any additional columns you use in the select statement, can be accessed as usual through the returned object. For example,

    MyModelName.find_by_sql("select coulmn1, column2 as somenewname,
    column3 as someothername from....")

This returns objects which will each have attributes called somenewname and someothername.

You can have a look at the documentation for more on the find_by_sql method.

Answer 2

You can do something simple like that through the standard ActiveRecord query interface:

results = MyObject.select('name, count(*) as how_many')
                  .group(:name)
                  .order(:how_many)

# And later...
results.each do |o|
    puts "#{o.name} has #{o.how_many}"
end

so you don't have to resort to SQL.

As others have mentioned, there's also find_by_sql if you want to use an SQL query to instantiate a set of model objects based on an SQL query.

If you have some SQL query that collects data from several tables or includes things that AR isn't good at (subselects, derived tables, ...), then you can push ActiveRecord out of the way and talk directly to the database:

connection.select_rows('some big pile of sql').each do |r|
    # r will be an array of strings so you have to pull
    # it apart and sort out the types by hand
end

Using select_rows can also be useful if you're encoding a big pile of data straight to JSON (or similar) and have no need for the ActiveRecord niceties and don't want to pay the processing overhead of using ActiveRecord.

Answer 3

You can always:

MyObject.find_by_sql('query....')

but there are usually better ways.

Answer 4

You can try orm-way

MyObject.select(:name, "count(*) as count").group(...).order(...)