[MySQL] Sortowanie wyników w INNER JOIN |
[MySQL] Sortowanie wyników w INNER JOIN |
18.01.2023, 23:07:26
Post
#1
|
|
Grupa: Zarejestrowani Postów: 56 Pomógł: 0 Dołączył: 16.07.2010 Ostrzeżenie: (0%) |
Witam.
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 | +----+---------------------------------+------------+ INGREDIENT Kod +----+---------+------------+ | 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 | +----+------------+---------------+--------+----------+-------------+------------+ 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` 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}] | +---------------------------------+-------------------------------------------------------------------------+ 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}] | +---------------------------------+-------------------------------------------------------------------------+ ma ktoś jakiś pomysł? |
|
|
Wersja Lo-Fi | Aktualny czas: 27.09.2024 - 08:58 |