Additional AND criteria

[jarvis]

Hi All,

 

I have the following SQL (which is from Wordpress/WooCommerce):

	SELECT $wpdb->woocommerce_order_items.order_item_id, $wpdb->woocommerce_order_items.order_id, $wpdb->postmeta.post_id, $wpdb->postmeta.meta_value, $wpdb->users.ID, $wpdb->users.user_nicename, $wpdb->woocommerce_order_itemmeta.meta_key, $wpdb->woocommerce_order_itemmeta.meta_value
	FROM $wpdb->postmeta
	INNER JOIN $wpdb->users ON $wpdb->postmeta.meta_value = $wpdb->users.ID
	INNER JOIN $wpdb->woocommerce_order_items ON $wpdb->postmeta.post_id = $wpdb->woocommerce_order_items.order_id
	INNER JOIN $wpdb->woocommerce_order_itemmeta ON $wpdb->woocommerce_order_items.order_item_id = $wpdb->woocommerce_order_itemmeta.order_item_id
	WHERE $wpdb->postmeta.meta_key =  '_customer_user'
	AND $wpdb->woocommerce_order_itemmeta.meta_key =  '_wcs_migrated_subscription_status'
	AND $wpdb->woocommerce_order_itemmeta.meta_value =  'active'

What I need to do is add another 2 lines at the bottom like so:

	AND $wpdb->woocommerce_order_itemmeta.meta_key =  '_product_id'
	AND $wpdb->woocommerce_order_itemmeta.meta_value =  '20'

However, simply adding those 2 lines returns no results, removing those lines returns results yet I know it should have something!

 

Any help is much appreciated

[Jacques1]

How could the meta value possibly be both "active" and "20" at the same time?

 

I think what you actually mean is this:

AND (
    ($wpdb->woocommerce_order_itemmeta.meta_key = '_wcs_migrated_subscription_status' AND $wpdb->woocommerce_order_itemmeta.meta_value = 'active')
    OR ($wpdb->woocommerce_order_itemmeta.meta_key =  '_product_id' AND $wpdb->woocommerce_order_itemmeta.meta_value =  '20')
)

[jarvis]

Hi Jacques1,

 

That's a fair point but I guess it's the way the wordpress tables handle the info as each wordpress post can have multiple meta hence why I need to search on both those criteria

[mac_gyver]

because of the meta key/value pairs, that you want to match two different sets of, you need to join with the woocommerce_order_itemmeta twice, once for each set of values. the following (untested) should work - 

SELECT oi.order_item_id, oi.order_id, pm.post_id, pm.meta_value, u.ID, u.user_nicename,
    oim_t1.meta_key, oim_t1.meta_value, oim_t2.meta_key, oim_t2.meta_value
FROM $wpdb->postmeta pm
INNER JOIN $wpdb->users u ON pm.meta_value = u.ID
INNER JOIN $wpdb->woocommerce_order_items oi ON pm.post_id = oi.order_id

INNER JOIN $wpdb->woocommerce_order_itemmeta oim_t1 ON oi.order_item_id = oim_t1.order_item_id
    AND oim_t1.meta_key =  '_wcs_migrated_subscription_status'
    AND oim_t1.meta_value =  'active'

INNER JOIN $wpdb->woocommerce_order_itemmeta oim_t2 ON oi.order_item_id = oim_t2.order_item_id
    AND oim_t2.meta_key =  '_product_id'
    AND oim_t2.meta_value =  '20'

WHERE pm.meta_key =  '_customer_user'

[jarvis]

Thanks @mac_gyver!

[Jacques1]

There's no need for an additional join. Simply check if the number of matching key/value pairs is 2 (so that both conditions are fulfilled):

SELECT {$wpdb->woocommerce_order_items}.order_item_id, {$wpdb->woocommerce_order_items}.order_id, $wpdb->users.ID AS user_id, $wpdb->users.user_nicename
FROM {$wpdb->postmeta}
INNER JOIN {$wpdb->users} ON {$wpdb->postmeta}.meta_value = {$wpdb->users}.ID
INNER JOIN {$wpdb->woocommerce_order_items} ON $wpdb->postmeta.post_id = {$wpdb->woocommerce_order_items}.order_id
INNER JOIN {$wpdb->woocommerce_order_itemmeta} ON {$wpdb->woocommerce_order_items}.order_item_id = {$wpdb->woocommerce_order_itemmeta}.order_item_id
WHERE
    {$wpdb->postmeta}.meta_key =  '_customer_user'
    AND (
      ({$wpdb->woocommerce_order_itemmeta}.meta_key = '_wcs_migrated_subscription_status' AND {$wpdb->woocommerce_order_itemmeta}.meta_value = 'active')
      OR ({$wpdb->woocommerce_order_itemmeta}.meta_key =  '_product_id' AND {$wpdb->woocommerce_order_itemmeta}.meta_value =  '20')
    )
GROUP BY {$wpdb->woocommerce_order_items}.order_item_id, {$wpdb->woocommerce_order_items}.order_id, $wpdb->users.ID, $wpdb->users.user_nicename
HAVING COUNT(*) = 2