CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordhas-and-belongs-to-many
l33cher
l33cher

Reputation: 21

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.

Upvotes: 2

Views: 214

Answers (2)

l33cher
l33cher

Reputation: 21

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

Upvotes: 0

Igor Drozdov
Igor Drozdov

Reputation: 15045

You can query only those articles of the first category, which are also the articles of the second category.

It's going to be something like this:

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

Note, that it can be:

Category.find(1).articles.where(id: Category.find(2).articles)

but it makes additional requests and requires additional attention to the cases when category can't be found.

Upvotes: 1

Related Questions