CODEWITHSUNDEEP
mysql
elimariaaa
elimariaaa

Reputation: 866

CONCAT() inside GROUP_CONCAT()

I have the following data result:

---------------------------------------------------------------------
|  id  |     date     |              order_items           |   qty   |
---------------------------------------------------------------------
| 1460 |  2017-03-24  |  Alfonso XO                        |    2    |
| 1460 |  2017-03-24  |  Godiva Dark 85% Cacao             |    3    |
| 1460 |  2017-03-24  |  Godiva Gold Rigid Ballotin 14pcs  |    5    | 
---------------------------------------------------------------------

through this query:

SELECT 
   p.ID, 
   date(p.post_date) AS date, 
   i.order_item_name AS order_items
   max( CASE WHEN o.meta_key = '_qty' AND i.order_item_id = o.order_item_id THEN o.meta_value END ) AS qty
FROM wp_posts AS p 
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.ID = 1460 AND i.order_item_type = 'line_item' 
GROUP BY i.order_item_name

Then this:

--------------------------------------------------------------
|  id  |     date     |               order_items            |
--------------------------------------------------------------
| 1460 |  2017-03-24  |  2 Alfonso XO                        |
| 1460 |  2017-03-24  |  3 Godiva Dark 85% Cacao             |
| 1460 |  2017-03-24  |  5 Godiva Gold Rigid Ballotin 14pcs  | 
--------------------------------------------------------------

through this:

SELECT 
   p.ID, 
   date(p.post_date) AS date, 
   concat(max( CASE WHEN imeta.meta_key = '_qty' and items.order_item_id = imeta.order_item_id THEN imeta.meta_value END ), ' ', items.order_item_name) as order_items
FROM wp_posts AS p 
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.ID = 1460 AND i.order_item_type = 'line_item' 
GROUP BY i.order_item_name

My problem is, how can I achieve my data to become one column:

--------------------------------------------------------------
|  id  |     date     |               order_items            |
--------------------------------------------------------------
|      |              |  2 Alfonso XO                        |
| 1460 |  2017-03-24  |  3 Godiva Dark 85% Cacao             |
|      |              |  5 Godiva Gold Rigid Ballotin 14pcs  |
--------------------------------------------------------------

I tried enclosing my concat() with group_concat() but I receive an error: Invalid group function.

UPDATE

Big props to Sir Giorgos Betsos for the right answer.

This is now my query:

SELECT ID, post_date, GROUP_CONCAT(order_items separator '\n') AS order_items, post_status, shipping_flight_number, letter_code, terminal, order_total, full_name
FROM (
   SELECT 
  p.ID, 
  date(p.post_date) AS post_date,
  p.post_status AS post_status,
  concat(max( CASE 
                 WHEN o.meta_key = '_qty' and 
                      i.order_item_id = o.order_item_id 
                    THEN o.meta_value 
              END ), ' ', i.order_item_name) as order_items,
  max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_flight_number,
  substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1) as letter_code,
  ( select terminal_id from wp_shipping_airlines where letter_code = substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1)) as terminal,
  max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
  concat(max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ), ' ', max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END )) as full_name
FROM wp_posts AS p
LEFT JOIN wp_postmeta pm on p.ID = pm.post_id
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.post_type = 'shop_order' AND i.order_item_type = 'line_item' AND p.post_status != 'trash'
GROUP BY i.order_item_name, p.ID) AS t
GROUP BY ID, post_date, shipping_flight_number, letter_code, terminal, order_total, full_name

However, upon tweaking my query a bit more, I another issue:

1. I tried concatenating another data on the newly generated one from the new query:

-----------------------------------------------------------------------
|  id  |     date     |                   order_items                 |
-----------------------------------------------------------------------
|      |              |  2 Alfonso XO (2300551)                       |
| 1460 |  2017-03-24  |  3 Godiva Dark 85% Cacao (2657010)            |
|      |              |  5 Godiva Gold Rigid Ballotin 14pcs (2421181) |
-----------------------------------------------------------------------

The ones in the parentheses are called SKU's and they can be found in wp_postmeta table but they are connected via p.ID and p.post_id BUT with p.post_type = 'product'.

I tried replacing concat() with concat_ws() but with no luck.

SELECT ID, post_date, GROUP_CONCAT(order_items separator '\n') AS order_items, post_status, shipping_flight_number, letter_code, terminal, order_total, full_name
FROM (
   SELECT 
  p.ID, 
  date(p.post_date) AS post_date,
  p.post_status AS post_status,
  concat_ws(' ', max( CASE 
                 WHEN o.meta_key = '_qty' and 
                      i.order_item_id = o.order_item_id 
                    THEN o.meta_value 
              END ), i.order_item_name, ' -- ', max( CASE WHEN pm.meta_key = '_sku' and p.ID = pm.post_id and p.post_type = 'product' THEN pm.meta_value END )) as order_items,
  max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END ) as shipping_flight_number,
  substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1) as letter_code,
  ( select terminal_id from wp_shipping_airlines where letter_code = substring_index(max( CASE WHEN pm.meta_key = 'shipping_flight_number' and p.ID = pm.post_id THEN pm.meta_value END )," ",1)) as terminal,
  max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
  concat(max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ), ' ', max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END )) as full_name
FROM wp_posts AS p
LEFT JOIN wp_postmeta pm on p.ID = pm.post_id
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.post_type = 'shop_order' AND i.order_item_type = 'line_item' AND p.post_status != 'trash'
GROUP BY i.order_item_name, p.ID) AS t
GROUP BY ID, post_date, shipping_flight_number, letter_code, terminal, order_total, full_name

Your help is greatly appreciated.

Thank you very much.

-Eli

Upvotes: 1

Views: 1833

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

This should work:

SELECT ID, date, GROUP_CONCAT(order_items) AS order_items
FROM (
   SELECT 
      p.ID, 
      date(p.post_date) AS date, 
      concat(max( CASE 
                     WHEN imeta.meta_key = '_qty' and 
                          items.order_item_id = imeta.order_item_id 
                        THEN imeta.meta_value 
                  END ), ' ', items.order_item_name) as order_items
FROM wp_posts AS p 
LEFT JOIN wp_woocommerce_order_items AS i ON p.ID = i.order_id 
LEFT JOIN wp_woocommerce_order_itemmeta AS o ON i.order_item_id = o.order_item_id 
WHERE p.ID = 1460 AND i.order_item_type = 'line_item' 
GROUP BY i.order_item_name) AS t
GROUP BY ID, date

Note: You may add any separator you like inside GROUP_CONCAT, like: GROUP_CONCAT(order_items SEPARATOR '\n').

Upvotes: 1

Related Questions