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

In my opinion, trying to extract this data in one query (which I suppose you want to do) is a little bit overkill. I'd issue one query for each type with :group => company_name option. Then I'd merge the results using Ruby code so I have some data structure that allows for easy display (probably a hash of hashes, e.g. {'Company 1' => {'Calls' => 5, 'Letter' => 0, 'Postalcards' => 3'}...}.

Answer 2

If you really want to have all the contacts with the count of all these relationships, it might be a good idea to use a counter_cache.

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html - find counter cache on this page

What it does is automatically keep a denormalized tracking of the count on each of contact's relationships, so you don't have to query information from all the tables grouping by the contact, which might incur in performance problems.

What you need to do is add columns 'emails_count', 'letters_count' and 'postcards_count' to your 'contacts' table, update it with the right count initially, and declare your has_many relationships as such:

class Contact < ActiveRecord::Base
  has_many :emails, :class_name => 'ContactEmail', :counter_cache => 'emails_count'
  # so on for other columns
end

With these setup, all you need to do is query your Contacts, and check their counter cache to render your table. It's number will be automatically updated by rails everytime an item is created or destroyed for that contact.

Answer 3

Oh, so I had misunderstood your question at first. I think I finally get what you mean.

So indeed, the only good way to do what you want (which is pull all the data from all those tables) is by issuing an include when querying for contacts:

@contacts = Contact.all :include => [:contact_emails, :contact_letters, :contact_postal_cards]

This will generate 4 queries, selecting all data on these 4 tables an populating your contacts accordingly, so you can navigate through the contact object's relationships any way you like to populate your table.

Finally, you mention your table would be grouped by companies and not contacts, so I assume multiple contacts could have the same 'company_name'. If that's the case, you can group your collection by the 'company_name' like this

@companies = @contacts.group_by &:company_name

This would create a hash like this:

{'Company 1' => [contact_1, contact_3], 'Company 2' => [contact_4]}

With those in place, you can generate your table with something like this:

<% @companies.each do |company_name, contacts| %>
    Company: <%= company_name %>
    <% contacts.each do |contact| %>
       Contact: <%= contact.name %>
          Emails : <%= contact.contact_emails.map(&:email).split(',') %>
          etc..
    <% end %>
<% end %>

Answer 4

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