0

i have a DB schema as shown in the picture bellow : enter image description here

i want to view each category and subcategory as a heading and list the products under it

note: the products must be of the same supplier

like this:

category1

  • subcategory1

    ++product1

    ++product2

  • subcategory2

    ++product1

    ++product2

category2

  • subcategory

    ++product1

    ++product2

and is it possible to send this from controller to view in laravel ?

2 Answers 2

1

GROUP BY modifier WITH ROLLUP can help :

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

Gives :

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 |

You can control what rows generated by WITH ROLLUP to retrieve or flag them with GROUPING() function : https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_grouping

Laravel question is out of scope.

5
  • i wanted an object (associative array ) as a result of the query that has a category name as a key and all the products belongs to it as values
    – rh2o
    Jul 3, 2022 at 23:54
  • @rh2o i wanted an object (associative array ) as a result of the query If you want this (JSON?) array to be an output of the query then you must provide precise output value which you want to receive. If you want to build this array on the PHP side than this is separate question - you may only ask for an output which can be easily converted to the form which you need in on the PHP side (and again, show this output, not a scheme of its interpretation).
    – Akina
    Jul 4, 2022 at 4:38
  • ORDER BY 1 ASC, 2 ASC, 3 ASC -- NULL will be first This is not safe - nothing prevents the value to be NULL. Using GROUPING() function instead of values in ORDER BY is correct.
    – Akina
    Jul 4, 2022 at 4:39
  • @Akina "nothing prevents the value to be NULL". Correctly valued data (product, subcat and cat) prevent unwaited NULL values. GROUPING() manages only NULL generated by WITH ROLLUP.
    – JCH77
    Jul 4, 2022 at 19:51
  • @rh2o "i wanted an object (associative array ) as a result of the query that has a category name as a key and all the products belongs to it as values". That should be say first. Use SQL JSON_ARRAYAGG() or handle SQL result with PHP.
    – JCH77
    Jul 4, 2022 at 19:52
0

It's messy to build a tree in SQL. It is more straightforward to do that in application code.

GROUP_CONCAT() is another approach:

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)
1
  • what about other details of city table . in my case i want to have access to all columns of the product table
    – rh2o
    Jul 3, 2022 at 23:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.