CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordruby-on-rails-3.1
prasvin
prasvin

Reputation: 3019

active record query depending on count of associated objects

I have two models Company and Role related by has_many and belongs_to association respectively. I need to fetch a company that has exactly 'n' number of roles.

I came up with 

Company.joins(:roles).having("'count(roles.id) = ?', 3")

but this does not work. Is there an active record solution to this ?

Thanks.

Upvotes: 4

Views: 4128

Answers (4)

bricker
bricker

Reputation: 8941

I know this is not the answer you're looking for, but the best (in terms of performance) solution is to add a simple roles_count column to the companies table, and then add :counter_cache => true onto the belongs_to association declaration. Rails takes care of updating that column for you.

Then,

Company.where("roles_count = ?", 3)

More information: http://guides.rubyonrails.org/association_basics.html#belongs_to-counter_cache

Upvotes: 6

Sandip Ransing
Sandip Ransing

Reputation: 7733

Associations -

role belongs_to :company

company has_many :roles

So, selection for criteria would be - 

Company.joins(:roles).group(:company_id).having('count(company_id)= ?', 3)

OR

Company.joins(:roles).group(:company_id).having('count(*)= ?', 3)

Upvotes: 10

m_x
m_x

Reputation: 12564

don't know if this will work with mysql, but you could try something like: 

Company.joins( <<-SQL_SCRIPT, 3 )
  INNER JOIN ( 
               SELECT    "company_id", COUNT("*")
               FROM      "company_roles"
               GROUP BY  "company_id"
             )
          AS  "filter"
          ON  "filter"."company_id" = "companies"."id"
          AND "filter"."count" = ?
SQL_SCRIPT

. where company_roles would be the joining table for the HABTM association.

Upvotes: 0

Bjoernsen
Bjoernsen

Reputation: 2418

In PostgreSQL, you can use something like select company_fk from roles GROUP BY company_fk HAVING COUNT(company_fk) = 3;.

Upvotes: 0

Related Questions