WordPress / MySQL子查询问题

Question

I am creating a shipping plugin for a client using wordpress and Woocommerce. I have an admin screen that displays all shipments with selectable filters, i.e date range, shipping carrier, service method, etc. I am able to query the database for records base on date range using the below code.

SELECT ID
FROM wp_posts
WHERE post_type LIKE 'shop_order'
AND ID = ANY
   (SELECT post_id
    FROM wp_postmeta
    WHERE meta_value
    BETWEEN DATE_SUB( NOW( ) , INTERVAL 30 DAY AND NOW( ) );
LIMIT 0 , 30

However, when I want to add another filter such as Carrier, ie 'USPS', an empty result is returned. When adding the following:

AND meta_value LIKE 'USPS' )

I am using $wpdb in my code, however, the above is a direct call in phpmyadmin.

Answer 1

I think the reason nothing is being returned is that you're trying to filter on a single meta value being both a date, and a carrier, at the same time:

WHERE meta_value
  BETWEEN DATE_SUB( NOW( ) , INTERVAL 30 DAY AND NOW( )
  AND meta_value LIKE 'USPS'

Instead, you could try something like:

WHERE 
  ID IN 
  (
    SELECT post_id 
    FROM wp_postmeta 
    WHERE meta_key = 'date_key' 
      AND meta_value BETWEEN DATE_SUB( NOW( ) , INTERVAL 30 DAY AND NOW( ) )
  )
AND
  ID IN 
  (
    SELECT post_id
    FROM wp_postmeta 
    WHERE meta_key = 'carrier_key' 
      AND meta_value = 'USPS'
  )

Or joining to the meta table multiple times may be neater:

SELECT ID
FROM wp_posts
INNER JOIN wp_postmeta meta_date ON wp_posts.ID = meta_date.post_id AND meta_date.meta_key = 'date_key'
INNER JOIN wp_postmeta meta_carrier ON wp_posts.ID = meta_carrier.post_id AND meta_carrier.meta_key = 'carrier_key'
WHERE post_type LIKE 'shop_order'
  AND meta_date.meta_value BETWEEN DATE_SUB( NOW( ) , INTERVAL 30 DAY AND NOW( ) )
  AND meta_carrier.meta_value = 'USPS'
LIMIT 0 , 30

As I don't know the meta keys you're using, you'd need to replace 'date_key' and 'carrier_key' with the correct values.