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

I kind of study, but I can't figure it out. All my current queries are similar to MyObject.find(various conditions)

. This returns an array of MyObject

s and is sufficient for most purposes. But now I want to create a list of the most popular items with a type query select name, count(*) from MyObjects group by name order by 2

. This will not return MyObject

s, which will return columns of arbitrary data. So how do I go about doing this?


source to share

4 answers

If you are using 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 return object. For example,

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


This returns objects, each with attributes called somenewname and someothername.

More information about the find_by_sql method can be found in the documentation.



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

results ='name, count(*) as how_many')

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


so you don't need to resort to SQL.

As mentioned, there is also find_by_sql

if you want to use a SQL query to instantiate a set of model objects based on the SQL query.

If you have a SQL query that gathers data from multiple tables, or includes something that AR doesn't fit (subqueries, views ...), you can push ActiveRecord 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


It select_rows

can also be useful if you are encoding a large bunch of data directly into JSON (or similar) and don't need the intricacies of ActiveRecord and don't want to pay the overhead of using ActiveRecord.



You can always:



but there are usually better ways.



You can try orm-way, "count(*) as count").group(...).order(...)




All Articles