mam mały problem z zapytaniem, nie potrafię napisać poprawnie sortowania. Mam trzy tabele:
PRODUCT
Kod
+----+---------------------------------+------------+
| id | name | created_at |
+----+---------------------------------+------------+
| 1 | Rosół | 1673625572 |
| 2 | Skyr - Jogurt Islandzki | 1673982452 |
| 3 | Hamburger | 1674060883 |
+----+---------------------------------+------------+
| id | name | created_at |
+----+---------------------------------+------------+
| 1 | Rosół | 1673625572 |
| 2 | Skyr - Jogurt Islandzki | 1673982452 |
| 3 | Hamburger | 1674060883 |
+----+---------------------------------+------------+
INGREDIENT
Kod
+----+---------+------------+
| id | name | created_at |
+----+---------+------------+
| 1 | kcal | 1673982085 |
| 2 | Białko | 1673982085 |
+----+---------+------------+
| id | name | created_at |
+----+---------+------------+
| 1 | kcal | 1673982085 |
| 2 | Białko | 1673982085 |
+----+---------+------------+
Tabela z relacjami:
_related_product_ingredient
Kod
+----+------------+---------------+--------+----------+-------------+------------+
| id | product_id | ingredient_id | value | priority | modified_at | created_at |
+----+------------+---------------+--------+----------+-------------+------------+
| 1 | 2 | 1 | 389.00 | 1 | 1673983108 | 1673983108 |
| 2 | 2 | 2 | 71.00 | 1 | 1673983183 | 1673983183 |
| 3 | 1 | 2 | 59.00 | 1 | 1674059830 | 1674059830 |
| 4 | 1 | 1 | 394.00 | 1 | 1674059875 | 1674059875 |
| 5 | 3 | 1 | 366.00 | 1 | 1674060944 | 1674060944 |
| 6 | 3 | 2 | 76.00 | 1 | 1674060944 | 1674060944 |
+----+------------+---------------+--------+----------+-------------+------------+
| id | product_id | ingredient_id | value | priority | modified_at | created_at |
+----+------------+---------------+--------+----------+-------------+------------+
| 1 | 2 | 1 | 389.00 | 1 | 1673983108 | 1673983108 |
| 2 | 2 | 2 | 71.00 | 1 | 1673983183 | 1673983183 |
| 3 | 1 | 2 | 59.00 | 1 | 1674059830 | 1674059830 |
| 4 | 1 | 1 | 394.00 | 1 | 1674059875 | 1674059875 |
| 5 | 3 | 1 | 366.00 | 1 | 1674060944 | 1674060944 |
| 6 | 3 | 2 | 76.00 | 1 | 1674060944 | 1674060944 |
+----+------------+---------------+--------+----------+-------------+------------+
Moje query:
Kod
SELECT
`product`.`name` AS `name`,
JSON_ARRAYAGG(JSON_OBJECT('name', `ingredient`.`permalink`, 'value', `_related_product_ingredient`.`value` )) AS `ingredients`
FROM
`_related_product_ingredient`
INNER JOIN
`product` ON `product`.`id` = `_related_product_ingredient`.`product_id`
INNER JOIN
`ingredient` ON `ingredient`.`id` = `_related_product_ingredient`.`ingredient_id`
GROUP BY `_related_product_ingredient`.`product_id`
`product`.`name` AS `name`,
JSON_ARRAYAGG(JSON_OBJECT('name', `ingredient`.`permalink`, 'value', `_related_product_ingredient`.`value` )) AS `ingredients`
FROM
`_related_product_ingredient`
INNER JOIN
`product` ON `product`.`id` = `_related_product_ingredient`.`product_id`
INNER JOIN
`ingredient` ON `ingredient`.`id` = `_related_product_ingredient`.`ingredient_id`
GROUP BY `_related_product_ingredient`.`product_id`
Wynik:
Kod
+---------------------------------+-------------------------------------------------------------------------+
| name | ingredients |
+---------------------------------+-------------------------------------------------------------------------+
| Rosół | [{"name": "bialko", "value": 59.00}, {"name": "kcal", "value": 394.00}] |
| Skyr - Jogurt Islandzki | [{"name": "bialko", "value": 71.00}, {"name": "kcal", "value": 389.00}] |
| Hamburger | [{"name": "bialko", "value": 76.00}, {"name": "kcal", "value": 366.00}] |
+---------------------------------+-------------------------------------------------------------------------+
| name | ingredients |
+---------------------------------+-------------------------------------------------------------------------+
| Rosół | [{"name": "bialko", "value": 59.00}, {"name": "kcal", "value": 394.00}] |
| Skyr - Jogurt Islandzki | [{"name": "bialko", "value": 71.00}, {"name": "kcal", "value": 389.00}] |
| Hamburger | [{"name": "bialko", "value": 76.00}, {"name": "kcal", "value": 366.00}] |
+---------------------------------+-------------------------------------------------------------------------+
chciałbym aby wynik był sortowany od najwyższej zawartości białka do najniższej. Coś takiego:
Kod
+---------------------------------+-------------------------------------------------------------------------+
| name | ingredients |
+---------------------------------+-------------------------------------------------------------------------+
| Hamburger | [{"name": "bialko", "value": 76.00}, {"name": "kcal", "value": 366.00}] |
| Skyr - Jogurt Islandzki | [{"name": "bialko", "value": 71.00}, {"name": "kcal", "value": 389.00}] |
| Rosół | [{"name": "bialko", "value": 59.00}, {"name": "kcal", "value": 394.00}] |
+---------------------------------+-------------------------------------------------------------------------+
| name | ingredients |
+---------------------------------+-------------------------------------------------------------------------+
| Hamburger | [{"name": "bialko", "value": 76.00}, {"name": "kcal", "value": 366.00}] |
| Skyr - Jogurt Islandzki | [{"name": "bialko", "value": 71.00}, {"name": "kcal", "value": 389.00}] |
| Rosół | [{"name": "bialko", "value": 59.00}, {"name": "kcal", "value": 394.00}] |
+---------------------------------+-------------------------------------------------------------------------+
ma ktoś jakiś pomysł?