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.