CODEWITHSUNDEEP
ruby-on-rails
Manishh
Manishh

Reputation: 1484

Get objects that have multiple associated contents

I have 4 models: Person, School, Home, Office.

They all have the attributes id and name

My main model is person.rb:

class Person < ActiveRecord::Base
  has_and_belongs_to_many :school, join_table: :school_persons
  has_and_belongs_to_many :home, join_table: :home_persons
  has_and_belongs_to_many :office, join_table: :office_persons
end

I want to find all persons that have at least 10 associated contents.

I tried joining it with one model, but I want the content that has at least 10 associated content from all the models.

person.find_by_sql("
  SELECT person.* 
    FROM persons 
    INNER JOIN office_persons ON persons.id = office_persons.person_id     
    GROUP BY persons.id
    HAVING COUNT(office_persons.art_id) = 10
").count

How should I approach this?

Upvotes: 0

Views: 78

Answers (1)

Vasfed
Vasfed

Reputation: 18464

There're several ways:

  • You can employ a counter_cache-like column for count (but you'll have to add callbacks to all the related models to update it on create/delete as original counter_cache does)

  • Just use separate counter caches for each relation (as of rails 4 counter caches should work on HABTM relations) and select by sum of these

  • Heavy sql subqueries (usually not recomended, unless it is a one-off task or you have little data and thus no performance problems)

Query like:

 SELECT persons.* FROM persons
 WHERE ((select count(*) from office_persons where person_id=persons.id) + 
        (select count(*) from home_persons where person_id=persons.id)+
        (select count(*) from school_persons where person_id=persons.id)) >= 10
  • Refactor to has_many through:... with single join table and flag for home/office/etc thus have single counter cache and ability to just

Upvotes: 1

Related Questions