php - 在 SQL 或 PHP 中查询的结果操作？

Question

我写了这个查询:

SELECT p.category, r.id, r.code, u.hash_name, r.created_at, r.updated_at FROM uploads AS u
JOIN refrigerators AS r ON u.id=r.image
JOIN products_refrigerators AS pr ON pr.refrigerator_id=r.id
JOIN products p ON pr.product_id = p.id

这是结果:

Array
(
    [0] => stdClass Object
        (
            [category] => lamps
            [id] => 1
            [code] => canopy
            [path] => public/uploads/AAmAnnC2Yoij9QblcybTJefE5vTQrNZsfDCWqOPt.jpeg
            [created_at] => 2018-02-21 11:15:06
            [updated_at] => 2018-02-21 11:15:06
        )

    [1] => stdClass Object
        (
            [category] => panels
            [id] => 1
            [code] => canopy
            [path] => public/uploads/AAmAnnC2Yoij9QblcybTJefE5vTQrNZsfDCWqOPt.jpeg
            [created_at] => 2018-02-21 11:15:06
            [updated_at] => 2018-02-21 11:15:06
        )

    [2] => stdClass Object
        (
            [category] => lamps
            [id] => 2
            [code] => canopy2
            [path] => public/uploads/nqDT4gEXbF0opPmEpa1cPo5iPSulRoesYcgHiOA3.jpeg
            [created_at] => 2018-02-21 11:17:44
            [updated_at] => 2018-02-21 11:17:44
        )

    [3] => stdClass Object
        (
            [category] => lamps
            [id] => 3
            [code] => QWE
            [path] => public/uploads/0TSrz6j3JJH17XgEs2161mOh8aYgDikujdYwrCWR.jpeg
            [created_at] => 2018-02-21 11:18:13
            [updated_at] => 2018-02-21 11:18:13
        )

    [4] => stdClass Object
        (
            [category] => panels
            [id] => 3
            [code] => QWE
            [path] => public/uploads/0TSrz6j3JJH17XgEs2161mOh8aYgDikujdYwrCWR.jpeg
            [created_at] => 2018-02-21 11:18:13
            [updated_at] => 2018-02-21 11:18:13
        )

)

我的问题是:有没有办法得到这样的结果(= 将 category 属性转换为一个数组，该数组将所有相关类别分组，如果存在多个)

Array
(
    [0] => stdClass Object
        (
            [category] => Array 
                          (
                              [0] => lamps
                              [1] => panels
                          )
            [id] => 1
            [code] => canopy
            [path] => public/uploads/AAmAnnC2Yoij9QblcybTJefE5vTQrNZsfDCWqOPt.jpeg
            [created_at] => 2018-02-21 11:15:06
            [updated_at] => 2018-02-21 11:15:06
        )

    [1] => stdClass Object
        (
            [category] => Array 
                          (
                              [0] => lamps
                          )
            [id] => 2
            [code] => canopy2
            [path] => public/uploads/nqDT4gEXbF0opPmEpa1cPo5iPSulRoesYcgHiOA3.jpeg
            [created_at] => 2018-02-21 11:17:44
            [updated_at] => 2018-02-21 11:17:44
        )

    [2] => stdClass Object
        (
            [category] => Array 
                          (
                              [0] => lamps
                              [1] => panels
                          )
            [id] => 3
            [code] => QWE
            [path] => public/uploads/0TSrz6j3JJH17XgEs2161mOh8aYgDikujdYwrCWR.jpeg
            [created_at] => 2018-02-21 11:18:13
            [updated_at] => 2018-02-21 11:18:13
        )

)

直接使用 MySQL 还是唯一的方法是操纵结果(在我的例子中是在 PHP 中)？

可能答案是“你必须使用 PHP”，但我不是 MySQL 专家，这就是问题的原因。

Answer 1

您应该连接您的 category 列，然后按 id 将其分组，就像 Patrick 在评论中提到的那样。

SELECT GROUP_CONCAT(p.category), r.id ... GROUP BY r.id

您也可以使用 JSON_ARRAY或 JSON_OBJECT功能来实现类似的结果，但不要忘记将它们分组