Activerecord，如何在联接表上链接多个条件

Question

我正在设法为我的产品数据库创建一个查询。

我有这些模型

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 

基本上是一个产品数据库，每个产品都有一些特性，值存储在ProductFeature连接表中。这里是结构，presentional_value用于视图，raw_value用于搜索

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

我有一个Vue前端在这个产品数据库，我有多个搜索。为了创建搜索字段，我创建了category_searches表。

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

每天晚上，当我在数据库中导入新产品时，我会创建新的记录或者更新这个表:对于每一个可搜索的功能，我都会存储所有可能的可搜索值。

例如，在这个表中，我有

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"]

在我的Vue前端中，对于每个类别，我都使用这个表中的记录来绘制搜索界面，所以当我选择某个内容时，前端会用以下参数向我的搜索API发送一个请求：

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

基本上，我必须用category_id=5搜索每个产品，其中有search_options。

在这里，我停止了:我不知道如何构建查询。

我知道我必须加入products表和products_features表。我知道怎么去问Activerecord

“找到raw_value ==的产品吗?”或者“在哪里找到产品feature_id= ?”

这是一个简单的链子。但我不知道该怎么问ActiveRecord：

“查找ProductFeature with feature_id=124的raw_value为"Yes”的产品，feature_id=235有"OLED“的raw_value和.”

Answer 1

AND子句不会真正给出您想要的结果。您想要的是使用OR子句和组，并具有：

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

这将导致以下查询：

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  ?

返回联接表中至少有两个匹配项的所有产品。

您可能希望使用JSON/JSONB列而不是字符串列来存储值。这将帮助您减轻EAV模式的最大问题之一，这就是将所有内容都输入字符串列所带来的麻烦。

在Postgres (可能还有MySQL)上，您可以使用WHERE (columns) IN (values)构建一个更简单、更有效的查询：

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

用法：

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

SQL查询：

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

使用左联接怎么样？

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"})

如果可以加入更多的表，只需添加左联接语句和where语句即可。

Answer 3

我认为ActiveRecord不支持这样的查询。您可以使用普通SQL、阿雷尔或洗劫等。

目前无法进行检查，但Arel版本可能与此类似：

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