mysql - 在 MySQL 中存储项目关系

Question

背景

我希望能够在 MySQL 数据库中存储产品之间的复杂关系。

我目前有两个表。第一个是产品列表:

ID | sku | division | retired

第二张表是关系表

ID | prod1 | relation | prod2 

想法是 prod2 与 prod 有关系。

示例行

产品表:

1 | lamp | household  | false
2 | lamp-short | household  | false
3 | chair| household | false
4 | desk | household | false
5 | desk-realwood | household | false
6 | desk-extratall | household | false
6 | couch | household | false

关系表:

1 | 1 | child | 2   <- short lamp is child of lamp
2 | 4 | child | 5   <- real wood desk is child of desk
2 | 4 | child | 6   <- extra tall desk is child of desk

然后在lamp 的单个产品页面上，我想要一个“相关产品”框，因此我需要拉入所有相关产品。所以我需要一个 SQL 查询，给定 products 和 product_relations 的表名以及当前产品的 ID，可以提取所有行在与当前产品相关的 products 表中。

IE，如果我正在查看lamp，我需要引入lamp-short。

我想我需要使用 JOIN，但是当我尝试过时，我无法让它工作。

SELECT *
FROM `products`
INNER JOIN `product_relations` on products.id = ( product_relations.prod2 WHERE product_relations.prod1 = 1 )

5 被提供给查询。

问题

如果我知道当前产品的 ID，我将如何编写查询以获取所有相关产品？

Answer 1

如果你想得到相关的产品sku和它与通过它的sku搜索的原始产品的关系，你可以使用:

SELECT rp.sku, r.relation
FROM product p
    INNER JOIN relations r ON p.id = r.prod1
    INNER JOIN product rp on r.prod2 = rp.id
WHERE p.sku = '<original sku>'

这可以缩短，如果您正在按原始产品的 ID 进行搜索，那么您实际上并不需要上面的 p 别名表:

SELECT rp.sku, r.relation
FROM relations r
    INNER JOIN product rp on r.prod2 = rp.id
WHERE r.prod1 = '<original product id>';

并且您可以添加到该 WHERE 子句以按特定 r.relation 类型进行过滤，例如“Child”或您有什么。