Question 115151 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Ruby on Rails: Find all records that match a list

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

Tags:

3

Answers


  1. 0

    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).

    Login or Signup to reply.
  2. 0

    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}")
    Login or Signup to reply.
  3. 0

    Can we use where("tags.name in (?)", tag_list) ?

    (If anyone has any suggestions, I would appreciate it)

    Item.joins(:tags)
  .where("tags.name in (?)", tag_list).uniq
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search