Activerecord, how to chain multiple condition on a join table

Question

I'm trying to find a way to create a query for my product database.

I have these models

class Category < DatabaseProducts::Base
 has_many :category_searches
 has_many :products
end 

class Product < DatabaseProducts::Base
 belongs_to :category
 has_many :products_features
end 

class Feature < DatabaseProducts::Base
 has_many :products, through: :product_features
 has_many :product_features
end 

class ProductFeature < DatabaseProducts::Base
 belongs_to :feature
 belongs_to :product
end 

class CategorySearch < DatabaseProducts::Base
 belongs_to :category
end 

Basically is a product database, and every product has some features, and values are stored in the ProductFeature join table. Here is the structure, presentional_value is for the view, raw_value is for the search

create_table "product_features", force: :cascade do |t|
    t.string "raw_value"
    t.string "presentional_value"
    t.integer "product_id"
    t.integer "feature_id"
    t.boolean "searchable", default: false
    t.index ["feature_id"], name: "index_product_features_on_feature_id"
    t.index ["product_id"], name: "index_product_features_on_product_id"
  end

I have a Vue frontend in this product database, and I have multiple searches. To create the search field I create the category_searches table.

create_table "category_searches", force: :cascade do |t|
    t.integer "category_id"
    t.integer "feature_id"
    t.string "name"
    t.string "search_type"
    t.string "search_options", default: [], array: true
    t.index ["category_id"], name: "index_category_searches_on_category_id"
    t.index ["feature_id"], name: "index_category_searches_on_feature_id"
  end

Every night, when I import the new products in my database, I create new records or I update this table: for every searchable feature I store every possible searchable value.

For the TV Category, for example, in this table I have

category_id: 5
feature_id: 124
search_type: boolean
values: ["Yes","No"]

category_id: 5
feature_id: 235
search_type: options
values: ["OLED","LCD","QLED"]

In my Vue Frontend, for every category, I use the records in this table to draw the search interface, so when I select something the frontend send a request to my search API with these parameters:

category_id: 5
search_options: {"124" => "Yes", "235" => "OLED" ...}

Basically I have to search every product with category_id=5 where search_options.

Here I stop: I don't know how build the query.

I know that I have to join the products table and the products_features table. And I know how to ask to Activerecord

"Find Products where raw_value == ?" or "Find Products where feature_id= ?"

It's a simple chained where. But I don't know how to ask ActiveRecord:

"Find Products where ProductFeature with feature_id=124 has the raw_value of "Yes" and where the feature_id=235 has the raw_value of "OLED" and... "

Answer 1

An AND clause won't really give you the result you want. What you want is an to use an OR clause and GROUP and HAVING:

f_id = ProductFeature.arel_table[:feature_id]
raw = ProductFeature.arel_table[:raw_value]
Product.joins(:product_features)
       .where(
          f_id.eq(124).and(raw.eq("Yes")).or(
            f_id.eq(12345).and(raw.eq("No"))
          )
        )
       .group("products.id")
       .having(Arel.star.count.eq(2))

This results in the following query:

SELECT "products".*
FROM   "products"
       INNER JOIN "product_features"
               ON "product_features"."product_id" = "products"."id"
WHERE  ( "product_features"."feature_id" = 123
         AND "product_features"."raw_value" = 'Yes'
          OR "product_features"."feature_id" = 12345
             AND "product_features"."raw_value" = 'No' )
GROUP  BY "products"."id"
HAVING ( count(*) = 2 )
LIMIT  ?

Which returns all products that have at least two matches in the join table.

You might want to use a JSON/JSONB column instead of a string column for the value storage. This will help you mitigate one of the biggest problems with the EAV pattern which is the headaches of typecasting everything into a string column.

On Postgres (and probably MySQL) you can use WHERE (columns) IN (values) to compose a simpler and more effective query:

class Product < ApplicationRecord
  has_many :product_features
  has_many :features, through: :product_features

  def self.search_by_features(*pairs)
    t =  ProductFeature.arel_table
    conditions = Arel::Nodes::In.new(
        Arel::Nodes::Grouping.new( [t[:feature_id], t[:raw_value]] ),
        pairs.map { |pair| Arel::Nodes::Grouping.new(
            pair.map { |value| Arel::Nodes.build_quoted(value) }
        )}
    )
    Product.joins(:product_features)
      .where(
        conditions
      ).group(:id)
      .having(Arel.star.count.eq(pairs.length))
  end
end

Usage:

Product.search_by_features([1, "Yes"], [2, "No"], [3, "Maybe"])

SQL query:

SELECT     "products".*
FROM       "products"
INNER JOIN "product_features"
ON         "product_features"."product_id" = "products"."id"
WHERE      
  ("product_features"."feature_id", "product_features"."raw_value")
  IN 
  ((1, 'Yes'),(2, 'No'),(3, 'Maybe'))
GROUP BY   "products"."id"
HAVING     ( COUNT(*) = 3) ) 
LIMIT $1 

Answer 2

I don't think ActiveRecord supports such queries. You can use plain SQL, or Arel, or ransack, etc.

Cannot check at the moment, but Arel version may look similar to this:

products = Product.arel_table
result = params[:search_options].reduce(Product.where(category_id: 5)) do |scope, (feature_id, feature_value)|
  product_feature_table = ProductFeature.arel_table.alias("product_feature_#{feature_id}")
  join_clause = product_feature_table[:product_id].eq(products[:id]).
    merge(product_feature_table[:raw_value].eq(feature_value))
  scope.joins(product_feature_table.on(join_clause)
end

Answer 3

How about using left joins?

Product
.left_joins(:product_features)
.left_joins(:features)
.where(product_features: {feature_id: feature_id, raw_values: "YES"})
.where(features: {feature_id: feature_id, raw_values: "OLED"})

if there is more table that can be joined, just add left joins statement and where statement again