Can't get a complicated mysql query to work

问题:

I'm trying to write a mysql query and I'm having some issues with it. I'm trying to query WooCommerce data out of my Wordpress database. Basic invoice data is stored in the wp_posts table and the rest of the data is stored in the wp_postmeta table. Now 1 inovice in the wp_posts table points to multiple items in the wp_postmeta table. Here is an example.

WP_POSTS

----------------------------------------------------
ID           STATUS                 Date
----------------------------------------------------
0001         OPEN                   01/01/2000
0002         OPEN                   01/01/2000
0003         CLOSED                 01/02/2000

WP_POSTMETA

--------------------------------------------------------------------------
ID        POST_ID               META_KEY                META_VALUE
--------------------------------------------------------------------------
0001      0001                  CustomerLN              Test
0002      0001                  CustomerFN              Tester
0003      0001                  Payment_Type            PayPal
0004      0001                  Invoice_Total           $200
0005      0002                  CustomerLN              Doe
0006      0002                  CustomerFN              John
0007      0002                  Payment_Type            CC-Mastercard
0008      0002                  Invoice_Total           $1000

I've got a basic query that pulls the data in from the wp_posts table but I can't figure out how to pull data from the second table based on the META_KEY value.

Any help would be great. Thanks in advance.

回答1:

Welcome to the Entity-Attribute-Value world.

To have a normal representation of the resultset you might need not only JOIN these two tables but also PIVOT the resultset. You can do that with a query like

SELECT p.id, p.status,
       MAX(CASE WHEN m.meta_key = 'CustomerLN'    
                THEN m.meta_value END) customer_last_name,
       MAX(CASE WHEN m.meta_key = 'CustomerFN'    
                THEN m.meta_value END) customer_firt_name,
       MAX(CASE WHEN m.meta_key = 'Payment_Type'  
                THEN m.meta_value END) payment_type,
       MAX(CASE WHEN m.meta_key = 'Invoice_Total' 
                THEN m.meta_value END) invoice_total
 FROM wp_posts p LEFT JOIN wp_postmeta m
   ON p.id = m.post_id
 GROUP BY p.id, p.status

Sample output:

+------+--------+--------------------+--------------------+---------------+---------------+
| id   | status | customer_last_name | customer_firt_name | payment_type  | invoice_total |
+------+--------+--------------------+--------------------+---------------+---------------+
|    1 | OPEN   | Test               | Tester             | PayPal        | $200          |
|    2 | OPEN   | Doe                | John               | CC-Mastercard | $1000         |
|    3 | CLOSED | NULL               | NULL               | NULL          | NULL          |
+------+--------+--------------------+--------------------+---------------+---------------+

Here is SQLFiddle demo

---

Now to be able to filter your records based on meta keys and meta values you'll have to use HAVING clause

For example if you want to get invoices made by customer Jhon Doe

SELECT p.id, p.status,
       MAX(CASE WHEN m.meta_key = 'CustomerLN'    
                THEN m.meta_value END) customer_last_name,
       MAX(CASE WHEN m.meta_key = 'CustomerFN'    
                THEN m.meta_value END) customer_first_name,
       MAX(CASE WHEN m.meta_key = 'Payment_Type'  
                THEN m.meta_value END) payment_type,
       MAX(CASE WHEN m.meta_key = 'Invoice_Total' 
                THEN m.meta_value END) invoice_total
 FROM wp_posts p LEFT JOIN wp_postmeta m
   ON p.id = m.post_id
 GROUP BY p.id, p.status
HAVING customer_last_name = 'Doe'
   AND customer_first_name = 'John'

Output:

+------+--------+--------------------+---------------------+---------------+---------------+
| id   | status | customer_last_name | customer_first_name | payment_type  | invoice_total |
+------+--------+--------------------+---------------------+---------------+---------------+
|    2 | OPEN   | Doe                | John                | CC-Mastercard | $1000         |
+------+--------+--------------------+---------------------+---------------+---------------+

Here is SQLFiddle demo

回答2:

To start, I would do

select a.*, b.* from WP_POSTS a, wp_postmeta b
where b.POST_ID = a.id

What have you done?

回答3:

For this circumstance you want to use a "JOIN". Check out http://www.w3schools.com/sql/sql_join.asp, which is almost exactly what you want to do.

It'll end something like:

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta
ON wp_posts.ID=wp_postmeta.PostId;

Also note that your table name is wrong for the second table.

回答4:

First, you should not attempt to JOIN both tables, because their table structures are dissimilar. Let us create a view that consumes WP_POSTMETA's data, consolidating it.

Joining WP_POSTMETA with itself X-1 times for every X fields you are interested in produces 0 to X lines for every Post_ID. Use the WHERE clause to get 1 column from each copy of the table and reduce the total amount of lines per Post_ID to 1 (or none).

Filter out the rubbish and voilà!

NOTE: [table_name2] can be called temp0, [table_name3] called temp1, [table_name4] called temp2, or whatever you wish.

SELECT WP_POSTMETA.Post_ID AS Post_ID, WP_POSTMETA.CustomerLN AS CustomerLN, [table_name2].CustomerFN AS  CustomerFN, [table_name3].Payment_Type AS Payment_Type, [table_name4].Meta_Value AS  Invoice_Total 
FROM WP_POSTMETA 
JOIN WP_POSTMETA AS [table_name2] ON WP_POSTMETA.Post_ID = [table_name2].Post_ID 
JOIN WP_POSTMETA AS [table_name3] ON WP_POSTMETA.Post_ID = [table_name3].Post_ID 
JOIN WP_POSTMETA AS [table_name4] ON WP_POSTMETA.Post_ID = [table_name4].Post_ID 
WHERE WP_POSTMETA.Meta_Value =  "CustomerLN"
AND [table_name2].Meta_Value =  "CustomerFN"
AND [table_name3].Meta_Value =  "Payment_Type"
AND [table_name4].Meta_Value =  "Invoice_Total"

This will generate an output that can be made into a view by prepending

CREATE VIEW WP_TRANSACTIONS AS

and this view JOINed to WP_POST

SELECT * FROM WP_POST 
LEFT JOIN WP_TRANSACTIONS ON WP_POST.Post_ID = WP_TRANSACTIONS.Post_ID