ROR Associations group by query

Question

I have the following 3 models and relationship between them:

class Calendar < ActiveRecord::Base 
   has_many:fiscalcalendars
  has_many:voucherdatas ,:through => :fiscalcalendars
end

class Fiscalcalendar < ActiveRecord::Base
  belongs_to :calendar
  has_many :voucherdatas
end

class Voucherdata < ActiveRecord::Base
   has_many   :fiscalcalendars
  has_many   :calendars, :through => :fiscalcalendars
end

• fields in calendar : id,monthname
• fields in fiscalcalendar :id, calendar_id,fiscalyearweek
• fields in voucherdata :id, vhour, fiscalyear week

I want the sum of the vhour for each month

I can get it to group by fiscal week by doing

Voucherdata.sum(:vhour,:group=>:fiscalyearweek)

is there a simpler way to get it by month?

Answer 1

This should do what you're asking for, assuming I understand your database relationship.

Voucherdata.sum(:vhour, :joins => :calendars, :group=> 'calendars.monthname)

However this statement won't work without a little modification. You're not telling Rails how to link Voucherdata and Fiscalcalendar. With two :has_many relationships Rails doesn't know where to find the foreign key to link to the other one.

You need to make a join model and either make it a :has_many, :through relationship or use a :has_and_belongs_to_many relationship. Once you've set that up the above statement will work without modification.

Corrected model relationship and migration required. Using a :has_and_belongs_to_many relationship (cleaner syntax):

class Calendar < ActiveRecord::Base 
  has_many:fiscalcalendars
  has_many:voucherdatas ,:through => :fiscalcalendars
end

class Fiscalcalendar < ActiveRecord::Base
  belongs_to :calendar
  has_and_belongs_to_many :voucherdatas
end

class Voucherdata < ActiveRecord::Base
  has_many   :calendars, :through => :fiscalcalendars
  has_and_belongs_to_many :fiscalcalendars
end

class CreateFiscalcalendarsVoucherdatasJoinTable ActiveRecord::Migration
  def self.up
    create_table :fiscalcalendars_voucherdatas :id => false do |t|
      t.integer :fiscalcalendar_id
      t.integer :voucherdata_id
    end
  end

  def self.down
     drop_table :fiscalcalendars_voucherdatas
  end
end