Elegant Summing/Grouping/Etc in Rails

Question

I have a number of objects which are associated together, and I'd like to layout some dashboards to show them off. For the sake of argument:

• Publishing House - has many books
• Book - has one author and is from one, and goes through many states
• Publishing House Author - Wrote many books

I'd like to get a dashboard that said:

• How many books a publishing house put out this month?
• How many books an author wrote this month?
• What state (in progress, published) each of the books are in?

To start with, I'm thinking some very simple code:

@all_books = Books.find(:all, :joins => [:author, :publishing_house], :select => "books.*, authors.name, publishing_houses.name", :conditions => ["books.created_at > ?", @date])

Then I proceed to go through each of the sub elements I want and total them up into new arrays - like:

@ph_stats = {}
@all_books.map {|book| @ph_stats[book.publishing_house_id] = (@ph_stats[book.publishing_house_id] || 0) + 1 }

This doesn't feel very rails like - thoughts?

Answer 1

I think your best bet is to chain named scopes together so you can do things like:

@books = Books.published.this_month

http://api.rubyonrails.org/classes/ActiveRecord/NamedScope/ClassMethods.html#M001683 http://m.onkey.org/2010/1/22/active-record-query-interface

Answer 2

You should really be thinking of the SQL required to write such a query, as such, the following queries should work in all databases:

Number of books by publishing house

PublishingHouse.all(:joins => :book, :select => "books.publishing_house_id, publishing_houses.name, count(*) as total", :group => "1,2")

Number of books an author wrote this month

If you are going to move this into a scope - you WILL need to put this in a lambda

Author.all(:joins => :books, :select => "books.author_id, author.name, count(*) as total", :group => "1,2", :conditions => ["books.pub_date between ? and ?", Date.today.beginning_of_month, Date.today.end_of_month])

this is due to the use of Date.today, alternatively - you could use now()::date (postgres specific) and construct dates based on that.

Books of a particular state

Not quite sure this is right wrt your datamodel

Book.all(:joins => :state, :select => "states.name, count(*) as total", :group => "1")

All done through the magic of SQL.