I have table with following information

id | order_id | batch_id | bucket_id | menu_id | product_id | type_id | size
1  | 1        | 1        | 1         | 1       | 1          | 1       | small
2  | 1        | 1        | 1         | 1       | 5          | 1       | small
3  | 1        | 1        | 1         | 1       | 5          | 1       | medium

I want to achieve following

order_id | batch_id | product1 | product5
1        | 1        | 1 x small| 1 x small, 1 medium

Is this possible to write a query to achieve this?

CodePudding user response：

The following query would return the desired data you would need

SELECT
    order_id,
    batch_id,
    product_id,
    concat(
        count(product_id),
        ' x ',
        size
    ) as size_cnt
FROM
    t1
GROUP BY
    order_id,
    batch_id,
    product_id,
    size;

View working demo on DB Fiddle

However, in order to get it in the desired format, you would need to pivot the data. You could achieve this with the assistance of group_concat as shown in the sql example below:

SELECT
    order_id,
    batch_id,
    -- we can generate from here
    REPLACE(
        GROUP_CONCAT(
            ',',
            CASE
                WHEN product_id=1 THEN size_cnt
            END
        ),
        ',,',
        ','
    ) as product1,
    REPLACE(
        GROUP_CONCAT(
            ',',
            CASE
                WHEN product_id=5 THEN size_cnt
            END
        ),
        ',,',
        ','
    ) as product5
    -- to here. See explanation below
FROM (
    SELECT
        order_id,
        batch_id,
        product_id,
        concat(
            count(product_id),
            ' x ',
            size
        ) as size_cnt
    FROM
        t1
    GROUP BY
        order_id,
        batch_id,
        product_id,
        size
) t2
GROUP BY
   order_id,
   batch_id

View working demo on DB Fiddle

However, as you can see, you would have to know the product_ids before hand for the desired columns.

If you are uncertain about the product ids that you will have, writing a dynamic query would be helpful here. You could start by getting all the product_ids.

I'm using the DB facade from Laravel here, however, you may use the Eloquent ORM or other methods to achieve the following:

//I have a collection of product ids i.e. `collect([1,5])` based on your example
$productIds = DB::select("select distinct product_id from t1")
                 ->pluck('product_id');

Then generating a dynamic sql query to run on your table

$productExpression = DB::select("select distinct product_id from t1")
                 ->pluck('product_id')
                 //for each product id let us return an sql expression
                 ->map(function($productId){
                     return "
                     REPLACE(
                         GROUP_CONCAT(
                             ',',
                             CASE
                                 WHEN product_id=$productId THEN size_cnt
                             END
                         ),
                         ',,',
                         ','
                     ) as product$productId
                     ";
                 })
                 
                 //combine the expressions into one string
                 ->join(",");

We can now create a combined query as

$combinedQuery="
SELECT
    order_id,
    batch_id,
    $productExpression
FROM (
    SELECT
        order_id,
        batch_id,
        product_id,
        concat(
            count(product_id),
            ' x ',
            size
        ) as size_cnt
    FROM
        t1
    GROUP BY
        order_id,
        batch_id,
        product_id,
        size
) t2
GROUP BY
   order_id,
   batch_id;
";

//running the query to retrieve the results
$results = DB::select($combinedQuery);

Let me know if this works for you.

CodePudding user response：

It's possible in MySQL using this kind of query:

SELECT order_id, batch_id, 
       GROUP_CONCAT(CASE WHEN product_id=1 THEN CONCAT(type_id,' x ', size) END) AS product1, 
       GROUP_CONCAT(CASE WHEN product_id=5 THEN CONCAT(type_id,' x ', size) END) AS product5
FROM table1
GROUP BY order_id, batch_id

The problem with this is that it's not dynamic so if you have hundreds, thousands of products, the query will be very hard to maintain. One possible solution in MySQL is using prepared statement. Here is an updated example after @ggordon spotted that my previous attempt show duplicates:

SET @columns := (SELECT GROUP_CONCAT(CONCAT("GROUP_CONCAT(CASE WHEN product_id=",product_id," 
                     THEN CONCAT(cnt,' x ', size) END)  
                      AS product",product_id,"
                    ")) 
               FROM (SELECT DISTINCT product_id FROM table1) t1);
SET @query := CONCAT('SELECT order_id, batch_id, ',@columns,' 
                    FROM (SELECT product_id, order_id, batch_id, size, COUNT(*) cnt 
                            FROM table1 GROUP BY product_id, order_id, batch_id, size) t1 
                    GROUP BY order_id, batch_id');
PREPARE stmt FROM @query ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

There are 2 variables being used in there and I named each variable to represent what is it (hopefully).

Demo fiddle

CodePudding user response：

If you are using php with either PDO or mysqli you can get PHP to concat the fields.

   $result = $db->query("SELECT * FROM TABLE");
   while($row = $result->fetch_assoc()) {
    //do stuff with data
    $product .= $row["product_id"] . " x " . $row["size"].", ";
   }