Does it make sense to convert DB-ish queries into Rails ActiveRecord Model lingo?

Question

mysql> desc categories;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(80) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

 mysql> desc expenses;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| created_at  | datetime      | NO   |     | NULL    |                |
| description | varchar(100)  | NO   |     | NULL    |                |
| amount      | decimal(10,2) | NO   |     | NULL    |                |
| category_id | int(11)       | NO   | MUL | 1       |                |
+-------------+---------------+------+-----+---------+----------------+

Now I need the top N categories like this...

Expense.find_by_sql("SELECT categories.name, sum(amount) as total_amount 
   from expenses 
   join categories on category_id = categories.id 
   group by category_id 
   order by total_amount desc")

But this is nagging at my Rails conscience.. it seems that it may be possible to achieve the same thing via Expense.find and supplying options like :group, :joins..

• Can someone translate this query into ActiveRecord Model speak ?
• Is it worth it... Personally i find the SQL more readable and gets my job done faster.. maybe coz I'm still learning Rails. Any advantages with not embedding SQL in source code (apart from not being able to change DB vendors..sql flavor, etc.)?
• Seems like find_by_sql doesn't have the bind variable provision like find. What is the workaround? e.g. if i want to limit the number of records to a user-specified limit.

Answer 1

i see you have to exact tables pasted. can you fix that? i think i can help you if i see complete structure of these two tables

Answer 2

Expense.find(:all,
  :select => "categories.name name, sum(amount) total_amount",
  :joins => "categories on category_id = categories.id",
  :group => "category_id",
  :order => "total_amount desc")

Hope that helps!

Answer 3

Seems like find_by_sql doesn't have the bind variable provision like find.

It sure does. (from the Rails docs)

# You can use the same string replacement techniques as you can with ActiveRecord#find
  Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]

Answer 4

Well this is the code that finally worked for me.. (Francois.. the resulting sql stmt was missing the join keyword)

def Expense.get_top_n_categories options={}
    #sQuery = "SELECT categories.name, sum(amount) as total_amount 
    # from expenses 
    # join categories on category_id = categories.id 
    # group by category_id 
    # order by total_amount desc";
    #sQuery += " limit #{options[:limit].to_i}" if !options[:limit].nil?
    #Expense.find_by_sql(sQuery)
    query_options = {:select => "categories.name name, sum(amount) total_amount",
     :joins => "inner join categories on category_id = categories.id",
     :group => "category_id",
     :order => "total_amount desc"}
    query_options[:limit] = options[:limit].to_i if !options[:limit].nil?
    Expense.find(:all, query_options)
  end

find_by_sql does have rails bind variable... I don't know how I overlooked that. Finally is the above use of user-specified a potential entry point for sql-injection or does the to_i method call prevent that?

Thanks for all the help. I'm grateful.