Merge query and row to column

Question

I have the query below which gets ID's of the rows I require.

SELECT `wp_posts`.`ID`, `wp_posts`.`post_modified_gmt`, `wp_term_relationships`.`term_taxonomy_id`
FROM `wp_posts`
LEFT JOIN `wp_term_relationships` ON
`wp_posts`.`ID` = `wp_term_relationships`.`object_id`
WHERE `wp_posts`.`post_type` = 'shop_order'
AND `wp_term_relationships`.`term_taxonomy_id` = 14
AND `wp_posts`.`post_modified` > '2013-04-25 16:02:00'
ORDER BY `wp_posts`.`post_modified` ASC;

+----+---------------------+------------------+
| ID | post_modified       | term_taxonomy_id |
+----+---------------------+------------------+
| 41 | 2013-04-25 16:02:43 |               14 |
| 43 | 2013-04-25 18:40:37 |               14 |
+----+---------------------+------------------+
2 rows in set (0.00 sec)

I then loop through all ID's and run the query below using the ID to match post_id in the query.

SELECT `wp_postmeta`.`post_id`, `wp_postmeta`.`meta_key`, `wp_postmeta`.`meta_value` 
FROM `wp_postmeta` 
WHERE `post_id` = 43 # ID from last query
AND (`wp_postmeta`.`meta_key` = '_shipping_first_name'  
OR `wp_postmeta`.`meta_key` = '_shipping_last_name'
OR `wp_postmeta`.`meta_key` = '_shipping_address_1'
OR `wp_postmeta`.`meta_key` = '_shipping_address_2'
OR `wp_postmeta`.`meta_key` = '_shipping_city'
OR `wp_postmeta`.`meta_key` = '_shipping_state'
OR `wp_postmeta`.`meta_key` = '_shipping_postcode'
OR `wp_postmeta`.`meta_key` = '_shipping_country'
OR `wp_postmeta`.`meta_key` = '_order_total'
OR `wp_postmeta`.`meta_key` = '_order_shipping'
OR `wp_postmeta`.`meta_key` = '_order_discount'
OR `wp_postmeta`.`meta_key` = '_cart_discount'
OR `wp_postmeta`.`meta_key` = '_order_tax'
OR `wp_postmeta`.`meta_key` = '_order_shipping_tax');

+---------+----------------------+------------------+
| post_id | meta_key             | meta_value       |
+---------+----------------------+------------------+
|      43 | _shipping_country    | GB               |
|      43 | _shipping_first_name | Joe              |
|      43 | _shipping_last_name  | Bloggs           |
|      43 | _shipping_address_1  | 18 Street Name   |
|      43 | _shipping_address_2  |                  |
|      43 | _shipping_city       | Manchester       |
|      43 | _shipping_state      | Lancashire       |
|      43 | _shipping_postcode   | MM1 1MM          |
|      43 | _order_shipping      | 0.00             |
|      43 | _order_discount      | 0.00             |
|      43 | _cart_discount       | 0.00             |
|      43 | _order_tax           | 0.00             |
|      43 | _order_shipping_tax  | 0.00             |
|      43 | _order_total         | 224.00           |
+---------+----------------------+------------------+
14 rows in set (0.00 sec)

I was wondering if there was a way to merge these queries so I am halving the amount of queries to my server.

I'm not sure if you can do this, but I would like to change the meta_key values in to names of columns and use meta_value as the value?

Final result example

+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| ID | post_modified       | term_taxonomy_id | _shipping_country | _shipping_first_name | _shipping_last_name | _shipping_address_1 | _shipping_address_2 | _shipping_city | _shipping_state | _shipping_postcode | _order_shipping | _order_discount | _cart_discount | _order_tax | _order_shipping_tax | _order_total |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| 43 | 2013-04-25 18:40:37 |               14 | GB                | Joe                  | Bloggs              | 18 Street Name      |                     | Manchester     | Lancashire      | MM1 1MM            | 0.00            | 0.00            | 0.00           | 0.00       | 0.00                | 224.00       |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
Next row...
Next row...

Any help would be great.

Thanks

EDIT

With the help of meewoK this is the query that works for me.

SELECT (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_first_name' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_first_name',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_last_name' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_last_name',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_1' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_address_1',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_2' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_address_2',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_city' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_city',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_state' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_state',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_postcode' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_postcode',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_country' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_shipping_country',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_total' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_total',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_shipping' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_shipping',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_discount' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_discount',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_cart_discount' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_cart_discount',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_tax' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_tax',
    (CASE WHEN `wp_postmeta`.`meta_key` =  '_order_shipping_tax' THEN `wp_postmeta`.`meta_value` ELSE NULL END) AS '_order_shipping_tax',
    `wp_posts`.`ID`,
    `wp_posts`.`post_modified_gmt`,
    `wp_term_relationships`.`term_taxonomy_id`
FROM `wp_postmeta`, `wp_posts`
LEFT JOIN `wp_term_relationships` ON `wp_posts`.`ID` = `wp_term_relationships`.`object_id`
WHERE `wp_posts`.`post_type` = 'shop_order'
    AND `wp_term_relationships`.`term_taxonomy_id` = 14
    AND `wp_posts`.`post_modified` > '2013-04-25 16:02:00'
    AND `post_id` = `wp_posts`.`ID`
    AND (`wp_postmeta`.`meta_key` = '_shipping_first_name'
        OR `wp_postmeta`.`meta_key` = '_shipping_last_name'
        OR `wp_postmeta`.`meta_key` = '_shipping_address_1'
        OR `wp_postmeta`.`meta_key` = '_shipping_address_2'
        OR `wp_postmeta`.`meta_key` = '_shipping_city'
        OR `wp_postmeta`.`meta_key` = '_shipping_state'
        OR `wp_postmeta`.`meta_key` = '_shipping_postcode'
        OR `wp_postmeta`.`meta_key` = '_shipping_country'
        OR `wp_postmeta`.`meta_key` = '_order_total'
        OR `wp_postmeta`.`meta_key` = '_order_shipping'
        OR `wp_postmeta`.`meta_key` = '_order_discount'
        OR `wp_postmeta`.`meta_key` = '_cart_discount'
        OR `wp_postmeta`.`meta_key` = '_order_tax'
        OR `wp_postmeta`.`meta_key` = '_order_shipping_tax')
GROUP BY `wp_posts`.`ID`
ORDER BY `wp_posts`.`post_modified` ASC;

Final result example

+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| ID | post_modified       | term_taxonomy_id | _shipping_country | _shipping_first_name | _shipping_last_name | _shipping_address_1 | _shipping_address_2 | _shipping_city | _shipping_state | _shipping_postcode | _order_shipping | _order_discount | _cart_discount | _order_tax | _order_shipping_tax | _order_total |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
| 43 | 2013-04-25 18:40:37 |               14 | GB                | Joe                  | Bloggs              | 18 Street Name      |                     | Manchester     | Lancashire      | MM1 1MM            | 0.00            | 0.00            | 0.00           | 0.00       | 0.00                | 224.00       |
+----+---------------------+------------------+-------------------+----------------------+---------------------+---------------------+---------------------+----------------+-----------------+--------------------+-----------------+-----------------+----------------+------------+---------------------+--------------+
Next row...
Next row...

Checking into transposing..found something at: http://stackoverflow.com/questions/3392956/sql-how-to-transpose but it looks messy... — Menelaos, Apr 26 '13 at 14:21
As mysql doesn't support transposing / pivoting natively the solutions are more complicated than what you want. BBL: http://stackoverflow.com/questions/13581482/transpose-mysql-query-need-rows-into-columns — Menelaos, Apr 26 '13 at 14:24
I updated my answer to do the pivot with case. Give it a try and tell me :) — Menelaos, Apr 26 '13 at 17:03

DarkAjax · Answer 1 · 2013-04-26T14:04:53.070

You can try this:

SELECT `wp_postmeta`.`post_id`, `wp_postmeta`.`meta_key`, 
    `wp_postmeta`.`meta_value` 
FROM `wp_postmeta` 
WHERE `post_id` in (
    SELECT `wp_posts`.`ID`
    FROM `wp_posts`
    LEFT JOIN `wp_term_relationships` ON
        `wp_posts`.`ID` = `wp_term_relationships`.`object_id`
    WHERE `wp_posts`.`post_type` = 'shop_order'
    AND `wp_term_relationships`.`term_taxonomy_id` = 14
    AND `wp_posts`.`post_modified` > '2013-04-25 16:02:00'
    ORDER BY `wp_posts`.`post_modified` ASC)
AND `wp_postmeta`.`meta_key` in ('_shipping_first_name', '_shipping_last_name'
    '_shipping_address_1', '_shipping_address_2', '_shipping_city'
    '_shipping_state', '_shipping_postcode', '_shipping_country'
    '_order_total', '_order_shipping', '_order_discount'
    '_cart_discount', '_order_tax', '_order_shipping_tax');

Not working get error, and I want it so I dont have the ID (`post_id` = 43) from the first query in it. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from ( SELECT `wp_posts`.`ID`, `wp_posts`.`post_modified_gmt`, `wp' at line 4 — arbme, Apr 26 '13 at 13:54

Menelaos · Accepted Answer · 2013-04-26T17:02:55.390

0

This requires a join to get it working in one query. Something like this:

  SELECT `wp_posts`.`ID`,
       `wp_posts`.`post_modified_gmt`,
       `wp_term_relationships`.`term_taxonomy_id`,
       `wp_postmeta`.`post_id`,
       `wp_postmeta`.`meta_key`,
       `wp_postmeta`.`meta_value`
FROM `wp_postmeta`, `wp_posts`
LEFT JOIN `wp_term_relationships` ON `wp_posts`.`ID` = `wp_term_relationships`.`object_id`
WHERE `wp_posts`.`post_type` = 'shop_order'
  AND `wp_term_relationships`.`term_taxonomy_id` = 14
  AND `wp_posts`.`post_modified` > '2013-04-25 16:02:00'
  AND `post_id` = `wp_posts`.`ID`
  AND (`wp_postmeta`.`meta_key` = '_shipping_first_name'
       OR `wp_postmeta`.`meta_key` = '_shipping_last_name'
       OR `wp_postmeta`.`meta_key` = '_shipping_address_1'
       OR `wp_postmeta`.`meta_key` = '_shipping_address_2'
       OR `wp_postmeta`.`meta_key` = '_shipping_city'
       OR `wp_postmeta`.`meta_key` = '_shipping_state'
       OR `wp_postmeta`.`meta_key` = '_shipping_postcode'
       OR `wp_postmeta`.`meta_key` = '_shipping_country'
       OR `wp_postmeta`.`meta_key` = '_order_total'
       OR `wp_postmeta`.`meta_key` = '_order_shipping'
       OR `wp_postmeta`.`meta_key` = '_order_discount'
       OR `wp_postmeta`.`meta_key` = '_cart_discount'
       OR `wp_postmeta`.`meta_key` = '_order_tax'
       OR `wp_postmeta`.`meta_key` = '_order_shipping_tax')
ORDER BY `wp_posts`.`post_modified` ASC;

EDIT:

In relation to the requirement for pivoting I would use the following. You Will have to hard code a case for every possible meta_key....

select (CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_first_name' THEN `wp_postmeta`.`meta_key` ELSE NULL END) AS '_shipping_first_name',
(CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_1' THEN `wp_postmeta`.`meta_key` ELSE NULL END) AS '_shipping_address_1',
(CASE WHEN `wp_postmeta`.`meta_key` =  '_shipping_address_2' THEN `wp_postmeta`.`meta_key` ELSE NULL END) AS '_shipping_address_2',
...

and so on.

edited Apr 26 '13 at 17:02

answered Apr 26 '13 at 13:49

Menelaos

23,508
18
90
155

Get the error "#1054 - Unknown column 'wp_posts.ID' in 'on clause'". Also I removed comma before left join as it was throwing error. Any ideas whats gone wrong? – arbme Apr 26 '13 at 13:58
Yeh, fixing in the answer. LEFT JOIN needs to be right after `wp_posts`. As the join is on `wp_posts`.`ID` = , with the error before it was trying to left join wp_postmeta with term_relationships and there is no ID in wp_postmeta. I've fixed it now. – Menelaos Apr 26 '13 at 14:00
I have edited my post to show the output I get, is there no way to convert the meta_key values as column names? Thanks – arbme Apr 26 '13 at 14:16
xmmm..seems like some sort of pivot or transpose would do the trick ;) , Looking into it... – Menelaos Apr 26 '13 at 14:17
1

Have a look at http://stackoverflow.com/questions/3392956/sql-how-to-transpose ... though you could need quite a few cases... – Menelaos Apr 26 '13 at 14:21
Had a read little confusing, please can you look at my edited post. – arbme Apr 26 '13 at 14:36
I have updated my post, the problem is it returns multiple rows for each meta_key/meta_value then they should be converted into 1 row per ID. Thanks for the help thus far :) – arbme Apr 26 '13 at 22:36
1

Got it working it needed GROUP BY adding to flatten then result. Thanks for your help your a hero ;) – arbme Apr 27 '13 at 02:22

Merge query and row to column

2 Answers2