Rails 3 : Group by date AND another column

I have the following 2 models:

Requisition
  created_date
  associate_id
  value
Associate
  id
  name

I'm trying to come up with a way to display one table per month/year, as such:

07/2011
Associate name | Number of requisitions | Total value
associate 1    | 2                      | 300
...

08/2011
Associate name | Number of requisitions | Total value
associate 1    | 3                开发者_如何学JAVA      | 450
associate 2    | 1                      | 100
...

and so on.

This is my current controller code:

@search = Requisicao.search(params[:search])
temp = @search.all(:conditions => {:status_index => Requisicao::STATUSES.index('aprovada')})
@requisicaos = {}
temp.group_by { |t| t.data_efectiva.beginning_of_month }.each do |data, reqs|
  @requisicaos[data] = {}
  reqs.each do |req|
    @requisicaos[data][req.socio] = [] unless not @requisicaos[data][req.socio].nil?
    @requisicaos[data][req.socio] << req
  end
end

Keep in mind i'm using metasearch and another (inconsequential) condition in my query. What i accomplish here is an hash of hashes, like this: { date1 => { associate1 => [ requisition1, requisition2 ] } }

Sorry about the long post, but my question is: Is there a better way to do this, either by exploiting the group_by a little better or using some ruby magic for the creation of the hashes/lists, or both?

Thanks in advance, Pedro

You might be able to get away with using SQL directly to get the results you want, then format them into a nice table. Sometimes using the models just gets in the way:

SELECT associates.name, COUNT(requisitions.id) AS requisition_count, SUM(requisitions.value) AS requisition_value, requisition.created_date AS requisition_date
  FROM associates
  LEFT JOIN requisitions ON requisitions.associate_id=associates.id
  GROUP BY requisition.created_date

You can wrap this into a class method to keep it organized:

class Associate < ActiveRecord::Base
  def self.requisitions_by_date
    # Insert the query described above as the argument here
    self.connections.select_all("...").group_by do |row|
      row['requisition_date']
    end
  end
end