Как сделать такой запрос на activerecord?

Question

[Splite]

Допустим, есть такие таблицы

elements
id name
1 Тест1
2 Тест2
3 Тест3

options
id name
1 Опция1
2 Опция2
3 Опция3

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

В RoR для этого используются связь has_and_belongs_to_many. Так вот, как мне запросить все элементы, которые, например, имеют опции с ID 1 и 2 одновременно? (в случае с моим примером это запрос должен вернуть элементы с id 1 и 2)

Answer 1

[Viktor Vsk]

Я в свое время так и не нашел ответ на этот вопрос.
Задача была похожая: интернет-магазин, товары, фильтры товаров. При фильтрации товаров нужно выбирать товар у которого, помимо прочего, есть 2 фильтра И с id=1 И с id=2, например (Когда нужно выбрать, скажем, ноутбук у которого И 8ГБ памяти И 14 дюймов диагональ)

Был вариант с фасетным поиском с помощью полнотекстовых движков (sphinx, solr, elasticsearch и т.д.), но они бы были оверхедом в той ситуации.

Сейчас уже долгое время работает вот такой метод:

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

Так, думаю, все понятно. Разве что метод #to_group - он группирует фильтры (например, 8ГБ оперативки и 4ГБ оперативки нужно сравнивать по ИЛИ, а 8ГБ оперативки и 14 дюймов экран - по И)

Решение выглядит костыльно, но более элегантного не нашел.

Comments

[Splite]

Спасибо огромное!
Вот придумал еще одно решение. Не могу сообразить, подойдет ли оно вам
Element.joins(:options).select('*, count(*)').group(:id).having('count(*) = 2').where('options.id': [1, 2])
Очень костыльно, но работает. Сount должен быть равен кол-ву опций
Буду думать, как поменять архитектуру приложения так, чтобы подобные запрос нужен не был

Answer 2

[Павел Кононенко]

Никогда не используйте для many-to-many связей has_and_belongs_to_many! Используйте только нотацию has_many through. Однажды вам придется добавить в вашу связующую таблицу какое-то поле. Со связью организованной через has_and_belongs_to_many вы не сможете к нему обратиться.

Answer 3

[ТыжСисАдмин]

ModelName.where(element_id: [1,2])

Внимательно изучаем guides.rubyonrails.org/active_record_querying.html

Comments

[Splite]

Если напишу что-то вроде
Element.includes(:options).where('options.id': [1, 2])
то получу все элементы, где есть опция 1 ИЛИ опция 2
А нужны те элементы, где есть обе опции одновременно
Вариант Element.includes(:options).where('options.id': 1).where('options.id': 2) не работает

[Viktor Vsk]

Splite: все правильно. Только суть не в active record. Вы не можете написать запрос, где будете одновременно проверять на разные значения в одном поле

[thepry]

Виктор Ablebeam, Splite: вам поможет having('Count...')

Answer 4

[thepry]

UPDATE: а, вы уже и сами придумали :)

Вы можете использовать LEFT JOIN вместе с оператором COUNT и условием в HAVING. Товар содержит все опции из массива, если отобрать по этим опциям и количество отобранных равно длине массива.
Примерно так:

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

[vsuhachev]

Нужно использовать подзапрос, вот примерный код:

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