php - 使用 MySQL 进行高级网上商店过滤

Question

我正在使用 MySQL 和 PHP 制作一个网上商店，并进行数据库设计 similar to this (见图)
我对不同规范的多价商品没有问题，就像上面链接的问题一样。

这将是一个计算机网上商店，因此我无需为每种产品制定规范。我可以对它们进行分类，例如。智能手机、笔记本电脑...并为每个类别添加规范。
智能手机可能有:品牌、处理器类型、屏幕尺寸、颜色、电池、价格等
笔记本电脑:处理器、显卡、品牌、价格、尺寸……

我还认为不需要 [combinations] 表，因为在我看来，不必要地存储太多内容。

我的问题是过滤产品。我可以轻松选择所有这些，或者仅选择一个，或者例如。选择所有白色手机或所有白色或金色手机，但我无法选择所有具有 8MP 前置摄像头的白色手机。

表结构:
[产品]:id、名称、category_id、描述

[类别]:id、名称

[规范]:id、category_id、名称

[spec_values]:id、product_id、spec_id、值

以下 SQL 返回所有金色或具有 8mp 前置摄像头的不同产品。

SELECT products.id, products.name, spec_values.value AS Filter
FROM products, categories, specifications, `spec_values` 
WHERE products.category_id = categories.id 
    AND spec_values.product_id = products.id 
    AND ((specifications.name = 'Color' AND spec_values.value = 'Gold') 
        OR (specifications.name = 'Front Camera' AND spec_values.value = '8MP')) 
GROUP BY products.id;

我想要在它们之间使用逻辑 AND 而不是 OR，但简单地更改它不会返回任何内容，因为它无法在单行内实现，因为我的规范名称和值存储在多行中。

一种解决方案是为每个过滤器使用 SQL CREATE TEMPORARY TABLE，但我认为这需要大量不必要的服务器端工作。

另一种方法是获取所有非不同值并使用 PHP 迭代它们并检查它们是否全部存在；如果是，则将它们显示在页面上，但仍然可能很困惑。

是否有更好的解决方案，因为我对 SQL 缺乏经验而没有注意到？预先感谢您的任何帮助。祝你有美好的一天!

Answer 1

您可以为此使用聚合。包含一个 HAVING 子句来检查计数是否与过滤器的数量匹配。除非您两次(或更多次)插入准确的规范值，否则您将知道满足该条件的产品必须过滤所有规范值。如果没有，则缺少一行(或多行)，即一个(或多个)规范值。

SELECT products.id,
       products.name
       FROM products
            INNER JOIN spec_values
                       ON spec_values.product_id = products.id 
            INNER JOIN specifications
                       ON specifications.id = spec_values.spec_id
       WHERE specifications.name = 'Color' AND spec_values.value = 'Gold'
              OR specifications.name = 'Front Camera' AND spec_values.value = '8MP'
       GROUP BY products.id,
                products.name
       HAVING count(*) = 2;

顺便说一句，建议在 FROM 子句中的逗号分隔列表上使用显式连接语法，以获得更好的可读性。