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
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.
source to share
You can do something simple 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 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
end
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.
source to share