How does Ruby on Rails handle "Select count(*) from products group by category"?

Question

Say if there is a table with fields

Products
--------
ID
CategoryID
Name
Price
  ... etc

How can Ruby on Rails give a table that returns

select count(*) from products group by categoryID

which is to show how many products in each category? How will the result be like, as opposed to Products.find(:all) which is an array of Product objects?

As a more advanced operation, how about

select count(*) from products p inner join category c on p.categoryID = c.ID 
  group by categoryID

and

select average(price) from products p inner join category c on p.categoryID = c.ID 
  group by categoryID

?

Answer 1

You might want to check out the ActiveRecord Calculations module (it does most of what you're asking, I believe):

http://ar.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html

Some examples:

Count:

Product.count(:all, :group => :category_id)

Average:

Product.average(:price, :joins => [:category], :group => :category_id)

Those should hopefully get you on the right path. Definitely check out the linked documentation, though, as it's incredibly useful.

Answer 2

Are you asking what is happening behind the scenes or just what will the result look like. If it's the latter, then learn to love the console! You can easily find out for yourself:

$ script/console
Loading development environment (Rails 2.3.8)

>> Product.count
=> 229697

>> Product.count(:group => :category_id)
=> #<OrderedHash {27=>5588, 33=>41, 28=>156, 34=>22, 23=>15209, 1=>115357, 
     29=>109, 24=>68, 2=>14434, 25=>78576, 31=>85, 26=>4, 32=>48}>

As you can see, it gives you an ordered hash mapping category_ids to the counts for those categories.