Active record query for multiple conditions on child association

Question

I need help with a correlated subquery.

An item has many facets. The attributes of a facet are identifier and content. The facet identifier and content are used to provide options to filter what items are displayed - for example, the identifier could be 'color' and the content 'red' or identifier is 'size' and content is '10`

I want a query that will return the item where

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

The rails query I've tried

rails query

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

which translates into the following sql query

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

This returns an empty array because I think there is a conflict between

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

and

`facets`.`identifier` = 'color'

How do I write a query that will return an item on the basis of more than one of its facets. And is it possible to write this as an active record as opposed to raw sql?

Answer 1

I've identified an approach that works though it's not necessarily optimum:

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