MySQL:获取列出了所有属性的产品 [英] MySQL: Get products having all attributes listed

问题描述

我遇到了一些问题，无法使查询正常工作.

I'm having some problems to get this query works as I expect.

我有三个表:products，product_attributes和attributes.
关系很明显(一个产品可以具有多个属性)

I have three tables: products, product_attributes and attributes.
The relation is obvious (A product can have multiple attributes)

products
---------
id

product_attributes
------------------
product_id
attribute_id

attributes
----------
id
name

我想要实现的是获得具有给定属性列表的那些产品，但是忽略那些仅具有所需属性的部分列表的产品.
例如，具有以下产品和属性:

What I want to achieve is to get those products that has a given list of attributes, BUT omit those products that only has a partial list of the desired attributes.
For example, having these products and attributes:

• 鞋子1 [蓝色，男孩]
• 鞋子2 [蓝色，女孩]
• 鞋子3 [红色，男孩]
• 鞋子4 [红色，女孩]

查询带有[blue，boy]的那些产品的查询将仅检索Shoe 1.
询问带有[blue]的产品的查询将不会返回任何内容.

A query asking for those products with [blue,boy] would retrieve only Shoe 1.
A query asking for those products with [blue] would not return anything.

从现在开始我一直在使用此查询:

Since now I was working with this query:

SELECT p.*, pa.attribute_id
FROM products AS p 
LEFT JOIN product_attributes AS pa ON(pa.product_id=p.id)
WHERE 
pa.attribute_id IN(' . implode(',', $attr_ids) . ')
GROUP BY p.id
HAVING count(pa.attribute_id)=' . count($attr_ids)

仅给出属性时，此操作将失败，因为它将返回具有该属性的所有产品.

This fails when just an attribute is given because it will return any product having that attribute.

推荐答案

-- PHP (or any other languaje) parts are hardcoded here!!!!

SELECT p.*, hma.howmuchattr
-- howmuchattr is needed by HAVING clause, 
-- you can omit elsewhere (by surrounding SELECT or by programming languaje)

FROM products AS p 
LEFT JOIN product_attributes AS pa ON pa.product_id = p.id 
LEFT JOIN (
    SELECT product_id, count(*) as howmuchattr
    FROM product_attributes 
    GROUP BY product_id
) as hma on p.id = hma.product_id

WHERE 
pa.attribute_id IN 
(1,3)                    -- this cames from PHP (or any other languaje). Can be (1) for the other case
GROUP BY p.id
HAVING count(*) = howmuchattr;

请参见 sqlfiddle 此处
另请参见此答案

see sqlfiddle here
see also this answer

这篇关于MySQL:获取列出了所有属性的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！