mysql - 如何使用连接和 group_concat 正确检索产品的主要和次要类别？

Question

我正在尝试找出一个查询来检索特定产品所属的类别列表。

我有一个名为 products 的数据库，它包含三个表，分别是 categories、product_to_sec_cat_assoc 和 products。

+--------------------+ +--------------------+ +--------------------------+
| Products           | | categories         | | product_to_sec_cat_assoc |
+--------------------+ +--------------------+ +--------------------------+
| product_id         | | category_id        | | product_id               |
| pri_category_id    | | category_name      | | category_id              |
| name               | | url_link           | +--------------------------+
+--------------------+ +--------------------+ 

由于并非所有产品都有次要类别，因此我直接在产品条目中将其主要类别分配给产品。如果它们也在其他次要类别中，我会使用名为 product_to_sec_cat_assoc 的关联表来关联其他类别。

我在编写查询时遇到问题，我想在其中创建一个页面来显示特定类别中的所有产品。每个产品列表还显示了它们所属的各种二级类别。

我当前的查询是:

select t.*, 
GROUP_CONCAT(c.category_name order by pri_category_id, c.category_id) as category_names, 
GROUP_CONCAT(c.url_link) as category_links from products t
left join product_to_sec_category_assoc a using (id)
left join categories c on t.pri_category_id = c.category_id or c.category_id = a.category_id
where t.pri_category_id = 12 
or (a.category_id = 12 and a.id = t.id)
group by t.title

但出于某种原因，如果我将产品添加到多个二级类别，当我查看其中一个二级类别中的产品时，它只会显示一级类别和我正在查看的二级类别。另一方面，如果我查看主要类别列表，它会显示主要类别及其所有次要类别。

例如，假设我在电子产品主要类别以及平板电脑和移动设备次要类别中有 iPad。如果我访问“电子产品”类别列表，它会在“电子产品”、“平板电脑”、“移动设备”中显示 iPad。如果我进入“移动”类别，它会显示在“电子”、“移动”中。如果我进入平板电脑类别，它会显示在电子产品、平板电脑类别中。

我遇到的另一个问题是查询也没有正确地对 GROUP_CONCAT(category_name) 和 GROUP_CONCAT(category_link) 进行排序，以便 category_names 及其链接处于匹配顺序。

Answer 1

考虑到您的设计(产品与类别有两种不同的关系)，您的问题相当棘手。问题在于，在您的 WHERE 子句中，您实际上是在过滤掉其余的产品类别，并且只返回用户所在的类别和/或其主要类别。

相反，您必须首先选择在次要类别表或其主要类别字段中具有特定类别的产品(在 FROM 子选择中完成)，然后 将那个的结果加入二级类别表 - 这样您就可以只获得特定类别(无论是否是主要类别):

SELECT
    a.*,
    GROUP_CONCAT(DISTINCT CONCAT(c.category_name, '||', c.url_link) ORDER BY c.category_name) AS categories
FROM
    (
        SELECT 
            aa.product_id,
            aa.pri_category_id,
            aa.name
        FROM
            products aa
        LEFT JOIN
            product_to_sec_category_assoc bb ON 
                aa.product_id = bb.product_id
                AND bb.category_id = 12
        WHERE
            aa.pri_category_id = 12
            OR bb.product_id IS NOT NULL
    ) a
LEFT JOIN
    product_to_sec_cat_assoc b ON a.product_id = b.product_id
LEFT JOIN
    categories c ON 
        a.pri_category_id = c.category_id
        OR b.category_id = c.category_id
GROUP BY
    a.product_id
ORDER BY
    a.title

两个 12 代表用户当前所在的类别。

您必须注意的一件事是，第二个 LEFT JOIN 中的 OR 将始终为至少具有一个次级类别的每个产品连接两行 -一排将始终是该产品的主要类别。因此，在连接中，对于为产品列出的每个次级类别，在视觉上，您会将产品的主要类别附加在它之上，从而导致重复。这就是为什么你必须在 GROUP_CONCAT() 函数中引入 DISTINCT。

在 GROUP_CONCAT() 中，我决定将 category_name 和 url_link 字段合并在一起，用 || 分隔。根据您在应用程序中使用的语言，您可以根据该分隔符将两者分开。这比尝试匹配两个不同列中的顺序要好。

---

也许更好的设计是在产品和类别之间建立多对多关系，并在交叉引用表中有一个字段指示该类别是否是主要类别。该架构看起来像:

+---------------+ +-------------------+ +----------------+
| products      | | product_cat_assoc | | categories     |
+---------------+ +-------------------+ +----------------+
| product_id    | | product_id        | | category_id    |
| name          | | category_id       | | category_name  |
+---------------+ | is_primary        | | url_link       |
                  +-------------------+ +----------------+

现在您只需关心基于一种关系而不是两种关系检索类别。 is_primary 字段可以是一个 TINYINT(1)，包含 0 或 1 以指示它是否是主要的。在此设计中，所有产品都应始终在 product_cat_assoc 表中至少有一个对应行代表主要类别。额外的行将是次要类别。

然后要检索特定类别中产品的所有类别列表，查询将非常简单:

SELECT
    a.*,
    GROUP_CONCAT(CONCAT(c.category_name, '||', c.url_link) ORDER BY b.is_primary DESC) AS categories
FROM
    (
        SELECT 
            aa.product_id, 
            aa.name
        FROM 
            products aa
        INNER JOIN 
            product_cat_assoc bb ON aa.product_id = bb.product_id AND bb.category_id = 12
    ) a
INNER JOIN
    product_cat_assoc b ON a.product_id = b.product_id
INNER JOIN
    categories c ON b.category_id = c.category_id
GROUP BY
    a.product_id
ORDER BY
    a.name