php - mysql - 产品属性选择

Question

我有三个数据库表:

products
(
  product_id, name etc..
)

specifications
(
  id_specification, name 
)

product_has_specification
(
  id_specification, product_id, specification_value
)

假设我有一件尺寸为 M 的 T 恤 (product_id = 1)，颜色为蓝色，并且具有更多属性。

我尝试连接三个表，并设置 value = m AND value =blue 等。但是它不显示任何结果。

我也尝试过像这样的子查询:

select distinct products.* from products
join product_has_specification on products.id = product_has_specification.product_id
join specifications on product_has_specification.spec_id = pecifications.id_specification
where
(
(specifications.name = 'color' and product_has_specification.value='black')

)
 and products.id in 
 (
select products.id from products
left join product_has_specification on products.id = product_has_specification.product_id
left join specifications on product_has_specification.spec_id = specifications.id_specification
where

(specifications.name='size' and product_has_specification.value='s')

 ) 

如果产品颜色为“黑色”且尺寸为“s”，则此选择有效，但如果我有更多属性，假设 X，则选择将太长。

选择会是什么样子？还有其他解决办法吗？

编辑:

我已经找到解决办法了。它很丑，但它能完成工作。但是，如果一个产品有，比如说 10 个规范，那么查询就会很长。

        SELECT products.*
        FROM products
        left JOIN product_has_specification ON products.id = product_has_specification.product_id
        left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
        WHERE products.id in (

                SELECT products.id
                FROM products
                left JOIN product_has_specification ON products.id = product_has_specification.product_id
                left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
                where   (product_has_specification.value = 'm') and products.id in 

                    (
                        SELECT products.id
                        FROM products
                        left JOIN product_has_specification ON products.id = product_has_specification.product_id
                        left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
                        where   (product_has_specification.value = 'black') and products.id in 
                        (
                                SELECT products.id
                                FROM products
                                left JOIN product_has_specification ON products.id = product_has_specification.product_id
                                left JOIN specifications ON product_has_specification.id_specifiction = specifications.id_specification
                                where   (product_has_specification.value = 'test') 
                        )

                    )

        )
        group by products.id

根据您想要的规范调整此查询。如果有人有更好的解决方案，请发布。

Answer 1

您可以使用 EXISTS 和 AND 运算符来选择所有颜色为黑色且尺寸为 s 的产品:

select distinct products.* from products
WHERE
EXISTS
(select 1 from product_has_specification join specifications on product_has_specification.spec_id = specifications.id_specification
where specifications.name = 'color' and product_has_specification.value='black' and products.products.id = product_has_specification.product_id) 
and
EXISTS
(select 1 from product_has_specification join specifications on product_has_specification.spec_id = specifications.id_specification
where specifications.name='size' and product_has_specification.value='s' and products.products.id = product_has_specification.product_id)