Search code examples
ruby-on-railsrubyactiverecordhas-and-belongs-to-many

Rails, Has and belongs to many, match all conditions

I have two models Article and Category

class Article < ApplicationRecord

  has_and_belongs_to_many :categories

end

I want to get Articles that have category 1 AND category 2 associated.

Article.joins(:categories).where(categories: {id: [1,2]}  )

The code above won't do it because if an Article with category 1 OR category 2 is associated then it will be returned and thats not the goal. Both must match.

Solution

  • The way to do it is to join the same table multiple times. Here is an untested class method on Article:

    def self.must_have_categories(category_ids)
  scope = self

  category_ids.each do |category_id|
    scope = scope.joins("INNER JOIN articles_categories as ac#{category_id} ON articles.id = ac#{category_id}.article_id").
        where("ac#{category_id}.category_id = ?", category_id)
  end

  scope
end