Custom WordPress SQL WC Query for Plugin

Question

I have a question regarding some SQL queries in WordPress. The object is getting the following information from multiple tables and their columns.

I've scoped out the following as a visual aid for myself: Text

wp_postmeta contains:

Department as = billing_field_267
Email as = billing_email
First Name = _billing_first_name
Last Name = _billing_last_name

wp_wc_order_stats contains:

Date and Time = date_created
Ordered Time = billing_field_739
Amount = ??
Total Orders = ??

The billing fields are meta_keys so I just need their values.

I've tried the following SQL Statement to get what I need:

SELECT 
  user.meta_key, user.meta_value
FROM
  wp_postmeta AS user, wp_wc_order_stats as order
WHERE
  user.meta_key='billing_field_267', user.meta_key='billing_email', user.meta_key='_billing_first_name', user.meta_key='_billing_last_name', user.meta_key='billing_field_739'
AND
  order.date_created

It doesn't seem to be working as needed some help in the right direct would be apprecaited.

Every condition in the where clause should be separed by a logic operator instead of coma. `WHERE user.meta_key='billing_field_267' AND user.meta_key='billing_email'...` — Junior Ngangeli, May 30 '22 at 11:29
Are you sure `wp_postmeta` is the right table, The way I see it it should be `wp_usermeta` — Ammar, Jun 04 '22 at 13:18

score 0 · Answer 1 · edited Jun 05 '22 at 19:28

You have two steps of query you have to work with.

Query one which is the first sub-query is fairly straight forward. The second subquery which build customers' info requires the conversion of ROWs into COLUMNs in the table which is described in greater detail in here

Thus, assuming Total Orders is number of order(s) in question and Amount to be total value of the order(s), the following query should work.


SELECT ordInfo.OrdTime as 'order Time',
       ordInfo.DateNTime as 'Date and Time',
       custInfo.Name,
       custInfo.Email,
       custInfo.Department,
       ordInfo.OrdCnt as 'Total orders',
       ordInfo.Amount
FROM
(
    SELECT ord.customer_id as 'customer_id', 
            ord.date_created as 'DateNTime', 
            orderMeta.meta_value as 'OrdTime',
            COUNT(ord.order_id) as 'OrdCnt', 
            SUM(total_sales + tax_total + shipping_total) as  'Amount'
    FROM wp_wc_order_stats ord 
        LEFT JOIN wp_postmeta orderMeta ON ord.order_id = orderMeta.post_id AND orderMeta.meta_key = 'billing_field_739'
    GROUP BY customer_id, date_created, meta_value
)ordInfo LEFT JOIN 
(
    SELECT user_id, 
        COALESCE(MAX(CASE meta_key WHEN 'billing_field_267' THEN meta_value END), ' ') as 'Department',
        COALESCE(MAX(CASE meta_key WHEN 'billing_email' THEN meta_value END), ' ') as 'Email',
        CONCAT(
            COALESCE(MAX(CASE meta_key WHEN '_billing_first_name' THEN meta_value END), ' '), ' ' ,
            COALESCE(MAX(CASE meta_key WHEN '_billing_last_name' THEN meta_value END), ' ')
        ) as 'NAME'
    FROM wp_usermeta
    WHERE meta_key IN ('billing_field_267', 'billing_email', '_billing_first_name', '_billing_last_name')
    GROUP BY user_id
)custInfo
ON ordInfo.customer_id = custInfo.user_id

Custom WordPress SQL WC Query for Plugin

1 Answers1