如何查找具有*全部*匹配类别的项目 [英] How to find items with *all* matching categories

问题描述

我有两个模型，分别是Item和Category，由联接表联接。我想查询项目以查找仅与类别列表匹配的项目。我的模型如下：

I have two models, Item and Category, joined by a join table. I would like to query Item to find only items that match a list of categories. My models look like:

class Item < ActiveRecord::Base
  has_and_belongs_to_many :categories
end

class Category < ActiveRecord::Base
  has_and_belongs_to_many :items
end

我可以轻松找到项目匹配类别列表中的任何一个。以下将返回属于类别1、2或3的项目。

I can easily find items that match ANY of the list of categories. The following will return items that belong to category 1, 2 or 3.

Item.includes(:categories).where(categories: {id:[1,2,3]})

我只想查找那些属于所有3类。使用ActiveRecord完成此操作的最佳方法是什么？

I would only like to find items that belong to all 3 categories. What is the best way to accomplish this using ActiveRecord?

我是否需要回过头来自己编写where条件，如果这样，PostgreSQL的正确语法是什么？我尝试了各种形式的 WHERE ALL IN（1,2,3），但仅遇到语法错误。

Do I need to fall back to writing the where condition myself and if so, what is the correct syntax for PostgreSQL? I've tried various flavors of "WHERE ALL IN (1,2,3)", but just get syntax errors.

更新：

基于对查找与所有类别匹配的产品（路轨3.1）我可以说很近了。

Based on the accepted answer to Find Products matching ALL Categories (Rails 3.1) I can get pretty close.

category_ids = [7,10,12,13,52,1162]

Item.joins(:categories).
  where(categories: {id: category_ids}).
  group('items.id').
  having("count(categories_items.category_id) = #{category_ids.size}")

不幸的是，当链接 .count 或 .size 时，我得到的是哈希值而不是记录数：

Unfortunately, when chaining .count or .size I get back a Hash instead of a record count:

{189 => 6, 3067 => 6, 406 => 6}

我可以对生成的哈希中的键进行计数以获得真实记录数，但这是

I can count the keys in the resulting hash to get the real record count, but this is a really inelegant solution.

推荐答案

ActiveRecord

对于ActiveRecord，您可以在您的Item类中放置这样的方法：

ActiveRecord

For ActiveRecord, you could put a method like this in your Item class:

def self.with_all_categories(category_ids)
  select(:id).distinct.
    joins(:categories).
    where('categories.id' => category_ids).
    group(:id).
    having('count(categories.id) = ?', category_ids.length)
end

然后您可以像这样过滤查询：

Then you can filter your queries like so:

category_ids = [1,2,3]
Item.where(id: Item.with_all_categories(category_ids))

您还可以使用范围使其更加友好：

You could also make use of scopes to make it a little more friendly:

class Item
  scope :with_all_categories, ->(category_ids) { where(id: Item.ids_with_all_categories(category_ids)) }

  def self.ids_with_all_categories(category_ids)
    select(:id).distinct.
      joins(:categories).
      where('categories.id' => category_ids).
      group(:id).
      having('count(categories.id) = ?', category_ids.length)
  end
end

Item.with_all_categories([1,2,3])

两者都会生成此SQL

Both will produce this SQL

SELECT "items".*
FROM "items"
WHERE "items"."id" IN
  (SELECT DISTINCT "items"."id"
   FROM "items"
   INNER JOIN "categories_items" ON "categories_items"."item_id" = "items"."id"
   INNER JOIN "categories" ON "categories"."id" = "categories_items"."category_id"
   WHERE "categories"."id" IN (1, 2, 3)
   GROUP BY "items"."id" 
   HAVING count(categories.id) = 3)

从技术上讲，您不需要与众不同作为该子查询的一部分，但是我不确定是否有更好的性能。

You don't technically need the distinct part of that subquery, but I'm not sure whether with or without would be better for performance.

原始SQL有几种方法

SELECT *
FROM items
WHERE items.id IN (
  SELECT item_id
  FROM categories_items
  WHERE category_id IN (1,2,3)
  GROUP BY item_id
  HAVING COUNT(category_id) = 3
)

这将在SQL Server中起作用-语法在Postgres中可能略有不同。或

That will work in SQL Server - the syntax might be slightly different in Postgres. Or

SELECT *
FROM items
WHERE items.id IN (SELECT item_id FROM categories_items WHERE category_id = 1)
  AND items.id IN (SELECT item_id FROM categories_items WHERE category_id = 2)
  AND items.id IN (SELECT item_id FROM categories_items WHERE category_id = 3)

这篇关于如何查找具有*全部*匹配类别的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！