How to get all WooCommerce Order metadata?

Hi Guys,

Today I am going to share my experience when pulling WooCommerce order metadata using SQL.

Initially, I use join for each metadata(example1), but when we have thousands of data and join count exceeds by 10, MYSQL is not able to handle the request. Then I search for an alternative solution and found aggregate function can be more optimized in this case. s. See example 2:. Aggregate solution gives me more efficient report then with JOIN

Example1:

SELECT 
ord.ID AS OrderID,
pm1.meta_value AS OrderTotal,
pm2.meta_value AS FirstName,
pm3.meta_value AS LastName,
pm4.meta_value AS CustomerID

FROM dbprefix_posts ord
-- join for order total
INNER JOIN dbprefix_postmeta pm1
ON ord.ID=pm1.post_id


-- join for order first_name
INNER JOIN dbprefix_postmeta pm2
ON ord.ID=pm2.post_id


-- join last name
INNER JOIN dbprefix_postmeta pm3
ON ord.ID=pm3.post_id

-- inner join customeriD
INNER JOIN dbprefix_postmeta pm4
ON ord.ID=pm4.post_id

-- and so on as per your metadata requirement

WHERE ord.post_type="shop_order"
AND pm1.meta_key="_order_total"
AND pm2.meta_key="_billing_first_name"
AND pm3.meta_key="_billing_last_name"
AND pm4.meta_key="_customer_user"

Example2:

SELECT 
ord.ID AS OrderID,
MAX(case when pm1.meta_key = '_order_total' then pm1.meta_value end) AS OrderTotal,
MAX(case when pm1.meta_key = '_billing_first_name' then pm1.meta_value end) AS FirstName,
MAX(case when pm1.meta_key = '_billing_last_name' then pm1.meta_value end) AS LastName,
MAX(case when pm1.meta_key = '_customer_user' then pm1.meta_value end) AS CustomerID

FROM dbprefix_posts ord

-- Join to get meta information
INNER JOIN dbprefix_postmeta pm1
ON pm1.post_id=ord.ID

WHERE ord.post_type="shop_order"
AND pm1.meta_key IN(
"_order_total", "_billing_first_name", "_billing_last_name", "_customer_user"
)
GROUP BY ord.ID

I hope this example will show some light on your struggle.

Thanks

Advertisement