CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecord
Enrai
Enrai

Reputation: 593

How to filter model by the sum of an attribute of its has_many relation

I have a model named Promoter and a model named PromoterProject. Each promoter has many promoter projects:

class Promoter < ActiveRecord::Base
  has_many :promoter_projects
end

class PromoterProject < ActiveRecord::Base
  belongs_to :promoter
end

The PromoterProject model has a column work_days of type integer. It represents the amount of days the promoter has worked on the project.

I need to implement a filter that returns the promoters with total work days (across all of their projects) between the specified values. I am having difficulties building the query using the active record query methods. So far I have the following:

Promoter.includes(:promoter_projects).group('promoters.id').select('promoters.*, sum(promoter_projects.work_days) as total_days').where('total_days between ? and ?', min_value, max_value)

I get an error saying that the total_days column cannot be found.

Most of the line (the group and the select statements) I have stitched from things I found in the interned, so I am not fully understanding what exactly is happening. If you can provide me with additional explanations I will really appreciate it!

Additional Info:

Rails version 4.1.4 PostgreSQL

Upvotes: 2

Views: 852

Answers (1)

Drenmi
Drenmi

Reputation: 8777

As Albin said, use joins. However, when using aggregate functions, it is simplest to use having:

Promoter.joins(:promoter_projects)
.having('SUM("promoter_projects"."work_days") BETWEEN ? AND ?', min_value, max_value)
.group("promoters.id")

Upvotes: 2

Related Questions