Rails join three models and compute the sum of a column

Question

Hi, I need some help over here to understand how the model relationship works on rails. Let me explain the scenario.

I have created 3 models.

1. Properties
2. Units
3. Rents

Here is the how relationship mapped for them.

Model #property.rb

 class Property < ActiveRecord::Base
      has_many :units
      has_many :rents, :through=> :unit
    end

Model #unit.rb

class Unit < ActiveRecord::Base
  belongs_to :property
  has_many :rents
end

Model #rent.rb

class Rent < ActiveRecord::Base
  belongs_to :unit
end

here is the the schema

     create_table "units", :force => true do |t|
        t.integer  "property_id"
        t.string   "number"
        t.decimal  "monthly_rent"
      end

 create_table "rents", :force => true do |t|
        t.integer  "unit_id"
        t.string   "month"
        t.string   "year"
        t.integer  "user_id"
      end

OK, here is my problem. Let's say I have 2 properties

• property A
• property B

and

• property A has unit A01,A02,A03
• property B has unit B01,B02,B03

I need to generate a report which shows the SUM of all the outstanding rents based on the property and month

So here is how it should be looks like. (tabular format)

• Property A - December - RENT SUM GOES HERE
• Property B - December - RENT SUM GOES HERE

So I got all the properties first. But I really can't figure out a way to merge the properties and units (I guess we don't need the rents model for this part) and print them in the view. Can someone help me to do this. Thanks

  def outstanding_by_properties

    @properties = Property.find(:all)
    @units = Unit.find(:all,:select=>"SUM(monthly_rent) as total,property_id",:conditions=>['property_id IN (?)',@properties])
  end

Answer 1

I think something like this will work for you. Hopefully an SQL guru will come along and check my work. I'm assuming your Property model has a "name" field for "Property A," etc.--you should change it to whatever your field is called.

def outstanding_by_properties
  Property.all  :select => "properties.name, rents.month, SUM(units.monthly_rent) AS rent_sum",
                :joins => { :units => :rents },
                :group => "properties.id, rents.month, rents.year"
end

This should return an array of Property objects that have the attributes name, month, and rent_sum.

It basically maps to the following SQL query:

SELECT properties.name, rents.month, SUM(units.monthly_rent) AS rent_sum
FROM properties
  JOIN units ON properties.id = units.property_id
  JOIN rents ON units.id = rents.unit_id
GROUP BY properties.id, rents.month, rents.year

The JOINs connect rows from all three tables and the GROUP BY makes it possible to do a SUM for each unique combination of property and month (we have to include year so that e.g. December 2008 is not grouped together with December 2009).