I need to list all users with a specific role (custom role ‘forhandler’) and for each show their totals (spent) on published orders that have status ‘completed’, ‘processing’ and ‘on-hold’, along with a count of the number of orders per user (also published and status as above). The result must be sorted with the highest totals first.
I managed to write a SQL query that works (and that’s the limit of my SQL knowledge), but it takes 20 seconds+ when running on production data (1200 orders and increasing). Can this query be rewritten to something more efficient (or simply better)?
SELECT users.ID, users.display_name,
(SELECT COUNT(posts.ID)
FROM $wpdb->posts AS posts
LEFT JOIN $wpdb->postmeta ON $wpdb->postmeta.post_id = posts.ID
LEFT JOIN $wpdb->term_relationships AS rel ON posts.ID=rel.object_ID
LEFT JOIN $wpdb->term_taxonomy AS tax USING( term_taxonomy_id )
LEFT JOIN $wpdb->terms AS term USING( term_id )
WHERE $wpdb->postmeta.meta_key = '_customer_user'
AND $wpdb->postmeta.meta_value = users.ID
AND posts.post_type = 'shop_order'
AND posts.post_status = 'publish'
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('completed', 'processing', 'on-hold')
) AS numOrders,
(
SELECT SUM(postmeta.meta_value)
FROM $wpdb->postmeta AS postmeta
LEFT JOIN $wpdb->posts AS pp ON pp.ID = postmeta.post_id
LEFT JOIN $wpdb->postmeta AS pm2 ON pm2.post_id = postmeta.post_id
LEFT JOIN $wpdb->term_relationships AS rel ON pp.ID=rel.object_ID
LEFT JOIN $wpdb->term_taxonomy AS tax USING( term_taxonomy_id )
LEFT JOIN $wpdb->terms AS term USING( term_id )
WHERE postmeta.meta_key = '_order_total'
AND pm2.meta_key = '_customer_user'
AND pm2.meta_value = users.ID
AND tax.taxonomy = 'shop_order_status'
AND term.slug IN ('completed', 'processing', 'on-hold')
AND pp.post_type = 'shop_order'
AND pp.post_status = 'publish'
) AS totalsOfOrders
FROM wp_users AS users
INNER JOIN wp_usermeta ON users.ID = wp_usermeta.user_id
WHERE wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value LIKE '%forhandler%'
ORDER BY totalsOfOrders DESC
