针对子关联的多个条件的活动记录查询

Question

我需要相关子查询的帮助。

一个项目有很多方面。分面的属性是identifier和content。分面identifier和content用于提供筛选显示的项目的选项 - 例如，identifier可以是"颜色"，content可以是"红色"，identifier可以是"大小"，content是"10">

我想要一个查询，它将返回其中的项目

(label contains a search term) 
OR 
( has a facet with an identifier equal to one of 2 given values AND the content includes the search term) 
AND 
(it ALSO has ANOTHER facet whose identifier is equal to one of a number of some different values AND content equal to one of a number of yet more different values).

我尝试过的轨道查询

轨道查询

items.joins(:facets)
  .where('items.label LIKE ? OR facets.identifier LIKE ?', "%#{search_term}%", "%#{search_term}%")
  .where('facets.identifier' => filter).where('facets.content' => value).uniq

转换为以下 SQL 查询

SELECT DISTINCT (`items`.`id`) FROM `items` INNER JOIN `facets` ON 
`facets`.`item_id` = `items`.`id` AND `facets`.`identifier` IN ('summary', 'description') AND 
(items.label LIKE '%outing%' OR facets.content LIKE '%outing%') AND 
`facets`.`identifier` = 'color' AND `facets`.`content` IN 
('red')

这将返回一个空数组，因为我认为两者之间存在冲突

`facets`.`identifier` IN ('summary', 'description')

和

`facets`.`identifier` = 'color'

如何编写一个查询，该查询将基于其多个方面返回项目。是否可以将其编写为活动记录而不是原始 sql？

Answer 1

我已经确定了一种有效的方法，尽管它不一定是最佳的：

class ItemProvider
  attr_reader :keyword, :filters
  FACET_CONTENT_TO_SEARCH = %w[summary description]
  def initialize(keyword, filters)
    @keyword = keyword
    @filters = filters
  end
  def retrieve
    @items = Item.all
    filter_by_keyword
    filter_items
  end
  private
  def filter_by_keyword
    return @items if keyword.blank?
    @items = @items
      .joins(:facets)
      .where('facets.identifier' => FACET_CONTENT_TO_SEARCH)
      .where('items.label LIKE ? OR facets.content LIKE ?', "%#{keyword}%", "%#{keyword}%").uniq
  end
  def filter_items
    return @items if filters.blank?
    filters_to_hash.each do |filter, value|
      @items = Item
      .unscoped
      .select("items.*")
      .joins("INNER JOIN facets ON facets.facetable_id = items.id AND facets.facetable_type = 'Item'")
      .where('facets.identifier' => filter)
      .where('facets.content' => value)
    end
  end
  def filters_to_hash
    filters.reduce({}) do |acc, filter|
      if acc[filter[:identifier]]
        acc[filter[:identifier]] << filter[:value]
      else
        acc[filter[:identifier]] = [filter[:value]]
      end
      acc
    end
  end
end