是否可以在單個 sql 查詢中將每個類別列為標題及其自己的產品

我有一個數據庫架構，如下圖所示：

我想將每個類別和子類別作為標題查看並列出其下的產品

注意：產品必須是同一供應商

像這樣：

類別1

• 子類 1

++產品1

++產品2

• 子類別2

++產品1

++產品2

類別2

• 子類別

++產品1

++產品2

是否可以將其從控制器發送到 laravel 中查看？

GROUP BY修飾符WITH ROLLUP可以幫助：

SELECT ALL c.name, s.name, p.name
FROM product AS p
INNER JOIN subcategory AS s ON p.subcategory_id = s.id
INNER JOIN category AS c ON s.cat_id = c.id
WHERE p.supplier_id = ?
GROUP BY 1, 2, 3 WITH ROLLUP
ORDER BY 1 ASC, 2 ASC, 3 ASC -- NULL will be first

給出：

category1 | NULL         | NULL     |
category1 | subcategory1 | NULL     |
category1 | subcategory1 | product1 |
category1 | subcategory1 | product2 |
category1 | subcategory2 | NULL     |
category1 | subcategory2 | product3 |
category1 | subcategory2 | product4 |
category2 | NULL         | NULL     |
category2 | subcategory3 | NULL     |
category2 | subcategory3 | product5 |
category2 | subcategory3 | product6 |

您可以控制生成的行以使用函式WITH ROLLUP檢索或標記它們： https ://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_groupingGROUPING()

Laravel 問題超出了範圍。

在 SQL 中建構樹很麻煩。在應用程式碼中這樣做更直接。

GROUP_CONCAT()是另一種方法：

mysql> SET group_concat_max_len = 50;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT province, GROUP_CONCAT(ascii_city SEPARATOR ', ')
   ->     FROM canada GROUP BY province;
+---------------------------+----------------------------------------------------+
| province                  | GROUP_CONCAT(ascii_city SEPARATOR ', ')            |
+---------------------------+----------------------------------------------------+
| Alberta                   | twin butte, carway, whiskey gap, whisky gap, coutt |
| British Columbia          | lameque, rocky point, saseenos, saseenos station,  |
| Manitoba                  | gretna, emerson, south junction, sprague, middlebr |
| New Brunswick             | seal cove, north head, campo bello, welchpool, wel |
| Newfoundland and Labrador | saint shotts, cape pine, portugal cove south, cape |
| Northwest Territories     | cunningham landing, bell rock, fort smith, salt ri |
| Nova Scotia               | lower wood harbour, lower woods harbour, woods har |
| Nunavut                   | charlton depot, belcher islands, sanikiluaq, burwe |
| Ontario                   | scudder, kingsville, point pelee, mcgregor, cottam |
| Prince Edward Island      | white sands, flat river, ocean view, murray harbou |
| Quebec                    | muncey, deloro, sainte-agnes-de-dundee, sainte-agn |
| Saskatchewan              | north portal, northgate, arena, bracken, masefield |
| Yukon                     | kaslo, dalton post, watson, carcross, caribou, car |
+---------------------------+----------------------------------------------------+
13 rows in set, 13 warnings (0.01 sec)

mysql> SHOW WARNINGS LIMIT 1;
+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1260 | Row 5 was cut by GROUP_CONCAT() |
+---------+------+---------------------------------+
1 row in set (0.00 sec)

引用自：https://dba.stackexchange.com/questions/314039