How to make such a request on activerecord?

Question

Ruby on Rails

Let's say there are such tables

elements
id name
1 Test1
2 Test2
3 Test3

options
id name
1 Option1
2 Option2
3 Option3

elements_options
element_id option_id
1 1
1 2
3 2
2 1
2 2

RoR uses the has_and_belongs_to_many relationship for this. So, how do I request all elements that, for example, have options with ID 1 and 2 at the same time? (in the case of my example, this query should return elements with id 1 and 2)

Answer 1

At the time, I never found an answer to this question.
The task was similar: an online store, products, product filters. When filtering products, you need to select a product that, among other things, has 2 filters AND with id=1 AND with id=2, for example (When you need to select, say, a laptop with AND 8GB of memory AND 14 inches diagonal)
There was a variant with faceted search using full-text engines (sphinx, solr, elasticsearch, etc.), but they would be an overhead in that situation.
This method has been working for a long time now:

def filter(fvalues_ids)
  grouped = Catalog::FilterValue.where(id: fvalues_ids.map(&:to_i)).to_group
  query = []
  grouped.each_value do |fvalues|
    ids = fvalues.map(&:id).join(', ')
    query << %(
      SELECT catalog_products.id
      FROM catalog_products
      INNER JOIN
        catalog_product_filter_values ON catalog_product_filter_values.catalog_product_id = catalog_products.id
      WHERE
        catalog_product_filter_values.catalog_filter_value_id IN (#{ids})
    )
  end

  query = query.join("\nINTERSECT\n")
  ids = ActiveRecord::Base.connection.execute(query).map{ |row| row['id'] }
  where(id: ids)
end

So, I think everything is clear. Unless the #to_group method - it groups filters (for example, 8GB of RAM and 4GB of RAM should be compared by OR, and 8GB of RAM and 14 inches screen - by AND)
The solution looks crutch, but I did not find a more elegant one.

Answer 2

Never use has_and_belongs_to_many for many-to-many relationships! Use only has_many through notation. One day you will have to add some field to your link table. With a has_and_belongs_to_many link, you won't be able to access it.

Answer 3

ModelName.where(element_id: [1,2])
Carefully studying guides.rubyonrails.org/active_record_querying.html

Answer 4

UPDATE: oh, you already figured it out yourself :)
You can use LEFT JOIN along with the COUNT operator and the HAVING condition. The product contains all options from the array, if selected by these options and the number of selected items is equal to the length of the array.
More or less like this:

options_ids = [1, 2]
Element.includes(:options).where('options.id': options_ids).group('elements.id, options.id').having('COUNT(options.id) = ?', options_ids.size)

Answer 5

You need to use a subquery, here is an example code:

Element.where(id: Option.joins(:elements).where(id: [1,2]).uniq.pluck('elements.id'))