Ruby on Rails: Find all records that match a list-CodePudding

I have 2 models:

class Item < ApplicationRecord
  has_many :tags, dependent: :destroy
end

class Tag < ApplicationRecord
  belongs_to :item
end

My current query will return Items that have ANY of the tags in a list, I need it to only return Items that have ALL of the tags.

tag_list = ['Foo', 'Bar', 'Baz'] # may contain more

Item.distinct
  .joins(:tags)
  .where(tags: { name: tag_list })

What can I do to return only the Items that have ALL the Tag names from my list using ActiveRecord?

Thanks for the help.

Edit: I'm using postgres

CodePudding user response：

This should work:

tag_list = ['Foo', 'Bar', 'Baz']

Item.distinct
  .joins(:tags)
  .where(tags: { name: tag_list })
  .group('tags.item_id')
  .having("COUNT(DISTINCT tags) = #{tag_list.size}")

CodePudding user response：

You can filter all the tags having name 'Bar', 'Baz', 'Foo' (order matters) and group them by their item_id, then you can get the items with those ids;

Item.where(
  id: Tag
    .group(:item_id)
    .having("ARRAY_AGG(DISTINCT name ORDER BY name) = ARRAY[?]::VARCHAR[]", ['Bar', 'Baz', 'Foo'])
    .select(:item_id)
)

Here the ARRAY[?]::VARCHAR[] is because tags.name is of type character varying, so you have to map the value to the right of the = in HAVING to whatever is the output of ARRAY_AGG(DISTINCT name ORDER BY name).