How to make group by with saving some columns?

Question

MySQL

Hello!
There are two tables: Orders and OrderProducts.

I make a request to get the user's orders and the products in this order.

SELECT orders.num, orders.createdon, orders.cost, orders.status, products.name, products.count, products.cost
FROM `modx_ms2_orders` AS orders
JOIN `modx_ms2_order_products` AS products ON orders.id = products.order_id
WHERE `user_id` = 106

Result:


Num is the order number. Is it possible to somehow merge the rows on the num column without removing the duplicate last columns ProductName, ProductCount, ProductCost.

Those. as a result, I want to get one line for each order, in which the ProductName, ProductCount, ProductCost fields can be repeated for each product.

Thanks in advance for the tips!

Answer 1

The simplest option is to use GROUP_CONCAT

SELECT
    orders.num, orders.createdon, orders.cost, orders.status, 
   GROUP_CONCAT(products.name) prod_names, 
   GROUP_CONCAT(products.count) prod_counts, 
   GROUP_CONCAT(products.cost) prod_costs
FROM `modx_ms2_orders` AS orders
JOIN `modx_ms2_order_products` AS products ON orders.id = products.order_id
WHERE `user_id` = 106
GROUP BY orders.num, orders.createdon, orders.cost, orders.status

Answer 2

No. You will not be able to make a selection with an arbitrary number of fields.
The maximum that can be done here is to collapse these fields into JSON.

SELECT `o`.`num`, `o`.`createdon`, `o`.`cost`, `o`.`status`, `p`.`products`
  FROM `modx_ms2_orders` AS `o`
  LEFT JOIN (
    SELECT `order_id`,
           JSON_ARRAYAGG(
             JSON_OBJECT('name', `name`, 'price', `price`, 'count', `count`)
           ) AS `products`
      FROM `modx_ms2_order_products` 
      GROUP BY `order_id`
  ) AS `p` ON `p`.`order_id` = `o`.`id`
  WHERE `o`.`user_id` = 106