company_counts.rb

#For http://stackoverflow.com/questions/3818686/this-sql-appears-to-be-giving-weird-results-with-multiple-joins-how-do-i-correct
#If you want to batch this all in one query, I would suggest creating a bunch of subqueries to count the records for each join, then joining to each of those.
#If you have few companies it would probably be better to use @klew's answer, and just loop through each company doing one query for each join type.
 def self.get_list(report_start_date, report_end_date)

   #Create a subquery for each join table which creates a count of all the records per company
   email_sub_query = Company.scoped(:select => 
                                    'companies.id, 
                                    count(contact_emails.id) as email_count',
                                    :joins => 
                                    [:contacts,
                                     "LEFT JOIN contact_emails ON contact_emails.contact_id = contacts.id"], 
                                    :conditions => 
                                    ['contact_emails.date_sent < ? and contact_emails.date_sent > ?',
                                     report_end_date, report_start_date], 
                                    :group => 'companies.id').send(:construct_finder_sql, {})
   call_sub_query = Company.scoped(:select => 
                                   'companies.id, 
                                    count(contact_calls.id) as call_count',
                                   :joins => 
                                   [:contacts,
                                    "LEFT JOIN contact_calls ON contact_calls.contact_id = contacts.id"], 
                                   :conditions => 
                                   ['contact_calls.date_sent < ? and contact_calls.date_sent > ?',
                                    report_end_date, report_start_date], 
                                   :group => 'companies.id').send(:construct_finder_sql, {})
   letter_sub_query = Contact.scoped(:select => 
                                     'contacts.id, 
                                      count(contact_letters.id) as letter_count',
                                     :joins => 
                                     [:contacts,
                                      "LEFT JOIN contact_letters ON contact_letters.contact_id = contacts.id"], 
                                     :group => 'companies.id').send(:construct_finder_sql, {})
   postalcard_sub_query = Company.scoped(:select => 
                                         'companies.id, 
                                          count(contact_postalcards.id) as postalcard_count',
                                         :joins => 
                                         [:contacts,
                                          "LEFT JOIN contact_postalcards ON contact_postalcards.contact_id = contacts.id"], 
                                         :group => 'companies.id').send(:construct_finder_sql, {})
   
   
   self.find( :all,
              :select => "companies.name AS co_name, 
                  companies.id AS comp_id, 
                  email_counts.email_count AS email_count, 
                  call_counts.call_count AS call_count, 
                  letter_counts.letter_count AS letter_count
                  postalcard_counts.postalcard_count AS postalcard_count",
              
              :joins => [
                         "LEFT JOIN (#{email_sub_query}) AS email_counts on email_counts.id =companies.id",
                         "LEFT JOIN (#{call_sub_query}) AS call_counts on call_counts.id =companies.id",
                         "LEFT JOIN (#{letter_sub_query}) AS letter_counts on letter_counts.id =companies.id",
                         "LEFT JOIN (#{postalcard_sub_query}) AS postalcard_counts on postalcard_counts.id =companies.id"
                        ],
              :group => "companies.id")
 end



#with all the redunancy removed
 def self.get_list(report_start_date, report_end_date)

      join_groups = [[:email,  
        {:conditions => 
          ['contact_emails.date_sent < ? and contact_emails.date_sent > ?',
           report_end_date, report_start_date]}
       ], 
       [:call,
        {:conditions => 
          ['contact_calls.date_sent < ? and contact_calls.date_sent > ?',
           report_end_date, report_start_date], 
        }],
       :letter,
       :postalcard
      ]
      select_and_join_sqls = join_groups.collect do |name,conditions|
        #Create a subquery for each join table which creates a count of all the records per company
        contact_join_table = "contact_#{name.pluralize}"
        sub_query_sql = Company.scoped({
                         :select =>
                         "companies.id,
                          count(#{contact_join_table}.id) as #{name}_count",
                         :joins => [
                                    :contacts,
                                    "INNER JOIN #{contact_join_table} ON contacts.id = #{contact_join_table}.contact_id"],
                         :group => 'companies.id'
                       }.merge(conditions)
         ).send(:construct_finder_sql, {}) #makes a sql string from a scope. Like "select blah from companies ..."
        ["#{name}_counts.#{name}_count AS #{name_count}", "LEFT JOIN (#{sub_query_sql}) as #{name}_counts on #{name}_counts.id = companies.id"]
      end
        self.find(:all,
                  :select => "companies.name AS co_name,
                              companies.id AS comp_id,
                              #{select_and_join_sqls.map(&:first).join(",\n")}",
                  :joins => select_and_join_sqls.map(&:last),
                  :group => 'companies.id')

end