Ava Barbilla Ava Barbilla - 7 months ago 31
SQL Question

Wordpress SQL query the user's first & last name for product X?

I am creating an SQL query where I want to display the full names and emails of customers who have purchased a downloadable product from a woocommerce shop. I am looking for something like this:

user_id first_name last_name user_email order
---------------------------------------------------------
1 Peter Jones a12@gmail Doc_1


In the table above, Doc_1 stands for the name of the product, in this case the customer with user_id = 1 can download Doc_1. For wordpress users, you know that the table
wp_usermeta
contains all the user's information, and looks like this:

umeta_id user_id meta_key meta_value
---------------------------------------------
1 1 nickname petjon123
2 1 first_name Peter
3 1 last_name Jones
... ... ... ...


This list goes down containing more
meta_value[s]
including email, etc. Everything would have been much more easier if the
meta_key[s]
would be table headers showing
first_name
and
last_name
beside each other instead of on top one another. I have come this far where my query shows the
first_name
, but I am having trouble getting the
last_name
. My SQL query looks like this:

SELECT
wp_usermeta.user_id,
wp_usermeta.meta_value AS 'first_name',
Wp_woocommerce_downloadable_product_permissions_1.user_email AS 'email',
Wp_woocommerce_order_items_1.order_item_name AS 'order'
FROM Wp_woocommerce_order_items
INNER JOIN Wp_woocommerce_downloadable_product_permissions
ON Wp_woocommerce_order_items.order_id = Wp_woocommerce_downloadable_product_permissions.order_id,
(Wp_woocommerce_order_items AS Wp_woocommerce_order_items_1
INNER JOIN Wp_woocommerce_downloadable_product_permissions AS Wp_woocommerce_downloadable_product_permissions_1
ON Wp_woocommerce_order_items_1.order_id = Wp_woocommerce_downloadable_product_permissions_1.order_id)
INNER JOIN wp_usermeta
ON Wp_woocommerce_downloadable_product_permissions_1.user_id = wp_usermeta.user_id
GROUP BY wp_usermeta.user_id,
wp_usermeta.meta_value,
Wp_woocommerce_downloadable_product_permissions_1.user_email,
Wp_woocommerce_order_items_1.order_item_name,
Wp_woocommerce_order_items_1.order_item_type,
wp_usermeta.meta_key
HAVING (((Wp_woocommerce_order_items_1.order_item_type) = 'line_item')
AND ((wp_usermeta.meta_key) = 'first_name'));


Giving me this result:

user_id first_name user_email order
--------------------------------------------
1 Peter a12@gmail Doc_1


I checked these values manually, and they are correct, however, I must say that if I do not GROUP the results, I get 1000 times more results, so GROUPING the data simplifies the results by basically removing the duplicates which obviously is excessive. I am doubting that this query is efficient given that the execution time until the results appear is +/-20368.14 ms and hereby am very open to any suggestions of improving this code. Having this said, I thought that I had to do another query to get the
last_name
making this a NESTED QUERY. If I open the tables in MS Access, create lets say Query1 which is the query above, and join Query1 with the table I get the results that I want. The SQL code for this is as follows:

SELECT
Query1.user_id,
Query1.first_name,
wp_usermeta.meta_value AS 'last_name',
Query1.user_email,
Query1.order_item_name
FROM wp_usermeta
INNER JOIN Query1
ON wp_usermeta.user_id = Query1.user_id
WHERE (((wp_usermeta.meta_value) <> '')
AND ((wp_usermeta.meta_key) = 'last_name'));


I tried substituting Query1 for this second query using parenthesis by enclosing the code from Query1, and this did not work. I get this error:

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 '.user_id, (SELECT wp_usermeta.user_id, wp_usermeta.meta_value AS 'first' at line 22

Does anybody know how to do this? Thanks!!




UPDATE

According to the answer provided by @Hogan I came up with this:

SELECT
first.user_id,
first.meta_value AS 'first_name',
last.meta_value AS 'last_name',
Wp_woocommerce_downloadable_product_permissions_1.user_email,
Wp_woocommerce_order_items_1.order_item_name
FROM Wp_woocommerce_order_items
INNER JOIN Wp_woocommerce_downloadable_product_permissions
ON Wp_woocommerce_order_items.order_id = Wp_woocommerce_downloadable_product_permissions.order_id,
wp_usermeta AS last
INNER JOIN ((Wp_woocommerce_order_items AS Wp_woocommerce_order_items_1
INNER JOIN Wp_woocommerce_downloadable_product_permissions AS Wp_woocommerce_downloadable_product_permissions_1
ON Wp_woocommerce_order_items_1.order_id = Wp_woocommerce_downloadable_product_permissions_1.order_id)
INNER JOIN wp_usermeta AS first
ON Wp_woocommerce_downloadable_product_permissions_1.user_id = first.user_id)
ON last.user_id = Wp_woocommerce_downloadable_product_permissions_1.user_id
GROUP BY first.user_id,
first.meta_value,
last.meta_value,
Wp_woocommerce_downloadable_product_permissions_1.user_email,
Wp_woocommerce_order_items_1.order_item_name,
Wp_woocommerce_order_items_1.order_item_type,
first.meta_key,
last.meta_key
HAVING (((Wp_woocommerce_order_items_1.order_item_type) = 'line_item')
AND ((first.meta_key) = 'first_name')
AND ((last.meta_key) = 'last_name'));


Which works perfectly fine in MS Access but in SQL it does not. I am getting this error:

Unknown column 'first.meta_key' in 'having clause'

Why?

I also have to mention, that when I add the
meta_key[s]
to the
SELECT
like this:

SELECT
first.user_id,
first.meta_value AS 'first_name',
last.meta_value AS 'last_name',
Wp_woocommerce_downloadable_product_permissions_1.user_email,
Wp_woocommerce_order_items_1.order_item_name,
first.meta_key,
last.meta_key


I at least get no error, the query is running but it goes on and on increasing my CPU usage drastically given that I am working on a localhost.




SOLUTION

SELECT DISTINCT
did the trick. No GROUPING. The query results appear in 127.76 ms. This is my query:

SELECT DISTINCT
first.user_id AS 'id',
first.meta_value AS 'first_name',
last.meta_value AS 'last_name',
Wp_woocommerce_downloadable_product_permissions_1.user_email AS 'email',
Wp_woocommerce_order_items_1.order_item_name AS 'order'
FROM Wp_woocommerce_order_items
INNER JOIN Wp_woocommerce_downloadable_product_permissions
ON Wp_woocommerce_order_items.order_id = Wp_woocommerce_downloadable_product_permissions.order_id,
((Wp_woocommerce_order_items AS Wp_woocommerce_order_items_1
INNER JOIN Wp_woocommerce_downloadable_product_permissions AS Wp_woocommerce_downloadable_product_permissions_1
ON Wp_woocommerce_order_items_1.order_id = Wp_woocommerce_downloadable_product_permissions_1.order_id)
INNER JOIN wp_usermeta AS first
ON Wp_woocommerce_downloadable_product_permissions_1.user_id = first.user_id)
INNER JOIN wp_usermeta AS last
ON Wp_woocommerce_downloadable_product_permissions_1.user_id = last.user_id
WHERE (((first.meta_key) = 'first_name')
AND ((last.meta_key) = 'last_name')
AND ((Wp_woocommerce_order_items_1.order_item_type) = 'line_item'));


As @Hogan stated, simply add the table
wp_usermeta
again with another alias, and make an
INNER JOIN
.

Answer

add this join

INNER JOIN wp_usermeta AS last 
    ON Wp_woocommerce_downloadable_product_permissions_1.user_id = last.user_id AND last.meta_key = 'last_name'

and the column

last.meta_value AS 'last_name',

You probably don't need to go so crazy with the () -- order does not matter unless you are doing a sub query (which you aren't) or are mixing left and right joins (which you aren't).