MySQL查询以获取产品变体

loading...


2

我正在尝试获取要在产品页面上显示的产品变体属性 . 我想要以下输出:

颜色:红色(尺寸:S,M),绿色(尺寸:S)

但我得到了:

颜色:红色,绿色尺码:S,M

这是我到目前为止的简化版本:

PRODUCT TABLE
|    id     |
-------------
|     1     |
|     1     |
|     1     |
VARIANT TABLE
|    id     |fk_product_id|
---------------------------
|     1     |       1     |
|     2     |       1     |
|     3     |       1     |
ATTRIBUTE TABLE
|fk_variant_id| property |  value    |
--------------------------------------
|     1     |   color   |     red    |
|     1     |    size   |      S     |
|     2     |   color   |     green  |
|     2     |    size   |      S     |
|     3     |   color   |     red   |
|     3     |    size   |      M     |
$stmt=$db->prepare('SELECT
    attribute.property AS property,
    GROUP_CONCAT(DISTINCT attribute.value) AS value
FROM product
INNER JOIN variant
    ON product.id=variant.fk_product_id
LEFT JOIN attribute
    ON variant.id=attribute.fk_variant_id
WHERE product_id=:product_id
GROUP BY attribute.property');

$stmt->bindParam(':product_id',$product_id);
$stmt->execute();
$query=$stmt->fetchAll();
foreach($query as $row){
  echo $row['property'].' '.$row['value'].'<br>';
}

最终的游戏是这样的:

loading...

1回答

  • 1

    此查询应该为您提供所需的结果 . 它有效地创建了每个属性( colorsize )的表,并将它们加入到产品和变体中 .

    SELECT p.id AS product, c.value AS color, GROUP_CONCAT(s.value) AS sizes
    FROM product p
    JOIN variant v ON v.fk_product_id = p.id
    LEFT JOIN attribute c ON c.fk_variant_id = v.id AND c.property = 'color'
    LEFT JOIN attribute s ON s.fk_variant_id = v.id AND s.property = 'size'
    GROUP BY product, color
    

    产量

    product     color   sizes
    1           green   S
    1           red     S,M
    

    Demo on dbfiddle

评论

暂时没有评论!