查找产品匹配所有类别（Rails的3.1） [英] Find Products matching ALL Categories (Rails 3.1)

问题描述

我挣扎与Rails的3.1.1一个ActiveRecord查询。

I am struggling with an ActiveRecord query in Rails 3.1.1.

我有2个型号，产品和类别，从产品的一个has_and_belongs_to_many类别（一个产品可以有多个类别）。

I have 2 models, Product and Category, with a has_and_belongs_to_many from Product to Category (a Product can have many categories).

我想写的搜索查询会发现，所有的指定类别的产品（考虑搜索的用户界面，用户可以选择X，分类搜索的）。

I am trying to write a search query that will find the Products that have ALL the specified Categories (think of a search UI where the user can choose X categories to search on).

我可以做到这一点：

results = Product.joins(:category_products).where('category_products.category_id' => [1,5,8])

但返回具有任何这些类别的产品（即产生一个SQLIN（1,5,8）条款）。

but that returns Products that have ANY of those Categories (i.e. produces a SQL "IN(1,5,8)" clause).

我怎样才能创建一个做配套的所有样式的查询？例如。发现有所有这些category_ids的产品...

How can I create a query that will do an ALL style of matching? E.g. find the products that have ALL of those category_ids...

推荐答案

您可以用having子句做到这一点：

You can do this with a having clause:

@ids = [1,5,8]
query = Product.select('products.id,products.name').joins(:categories) \
               .where(:categories => {:id => @ids}) \
               .group('products.id, products.name') \
               .having("count(category_products.category_id) = #{@ids.length}")

这篇关于查找产品匹配所有类别（Rails的3.1）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！