CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-5
dev4life
dev4life

Reputation: 1170

has_and_belongs_to_many exact matches by id

I am using a very standard has_and_belongs_to_many association, like the example in the Rails associations guide: http://guides.rubyonrails.org/association_basics.html#the-has-and-belongs-to-many-association

The problem is that I would like to query to find the Assembly with the exact match of parts. For instance, Assembly with 2 Parts associated to it, and the Parts having ids of 1, 2.

Here are my approaches that fail:

Assembly.joins(:parts).where(parts: {id: [1,2]})

Assembly.joins(:parts).
  where('parts.id IN (?)', [1,2]).
  group('assemblies.id').
  having('COUNT(assemblies.id) >= ?', [1,2].length)

(This one was suggested by this SO answer: Rails scope - where in exact matches)

These approaches return Assembly objects which just have Parts with ids 1 and 2 included in their associated Parts. But again, what I want is only Assemblies with exactly those 2 Parts (ids 1 and 2).

Please help!

Upvotes: 2

Views: 483

Answers (1)

Ilya Lavrov
Ilya Lavrov

Reputation: 2860

Update: Order of elements in PostgreSQL arrays are import. It's possible to use ORDER BY inside ARRAY_AGG function.

Assembly.joins(:parts).
  group('assemblies.id').
  having('ARRAY[?] = ARRAY_AGG(parts.id ORDER BY parts.id)', [1,2].sort)

First version:

If you are using PostgreSQL, you can do it with ARRAY_AGG group function:

Assembly.joins(:parts).
  group('assemblies.id').
  having('ARRAY[?] = ARRAY_AGG(parts.id)', [1,2])

Upvotes: 2

Related Questions