How do I extract a "table" from an array of ActiveRecords in Rails?

Question

I have several similar Models (ContactEmail, ContactLetter, ContactPostalcard).

Each instance (record) in, say, ContactEmail, means a specific Email template was sent to a specific Contact.

So, each one (e.g. ContactEmail) belongs_to :contact

So, in ContactEmail model, there is an attribute ContactEmail.contact_id.

Each Contact has a virtual attribute company_name.

So, when I write the following, I will get all emails sent within a specific set a conditions (in this case, time):

@sent_emails = ContactEmail.find(:all, :conditions => "conditions here")

So, @sent_emails.size would tell me the total of all emails sent.

My challenge: how do I extract more granularity, by unique company across the different models?

The output I want would look like the following:

FROM 8/1/10 TO 8/10/10 (where the dates are dynamic)

                Calls       Letter      Postalcards
Company 1         4           2             4
Company 2        10           4             6
Company 3         2           3             4

So Company3 has 2 Calls, which means there were two records of ContactCalls where the sent_date fell between the two dates, and where the associated contact belongs to Company 3.

Company 1-3 is not set ahead of time. It needs to be extracted from the pool of ContactCalls, ContactLetters, and ContactPostalcards.....

The challenge is that I don't know what the companies are. They are attributes of the contacts part of each distinct record. So in some cases, I may have Company2 has 0 letters.

Thanks for any guidance! :)

How I can find a company on a given record for ContactEmail model:

ContactEmail.contact.company.name

This will return the associated company for a specific ContactEmail

Answer 1

This should do it nicely:

    list = Contact.find :all,
      :select => "companies.name company_name, COUNT(contact_emails.id) email_count, COUNT(contact_letters.id) letter_count, COUNT(contact_postalcards.id) postalcard_count",
      :joins => [
        "LEFT JOIN companies ON company.id = contact.company_id",
        "LEFT JOIN contact_emails ON contact_emails.contact_id = contacts.id",
        "LEFT JOIN contact_letters ON contact_letters.contact_id = contacts.id",
        "LEFT JOIN contact_postalcards ON contact_postalcards.contact_id = contacts.id"
      ],
      :group => "companies.id"

      list.each do |item|
        p item.company_name, item.email_count, item.letter_count, item.postalcard_count
      end