wp_query with multiple custom fields returning 0 results

In wordpess 4.1 I have the following WP_Query:

$products = new WP_Query( array(
        'post_type'      => 'ys_product',
        'orderby'        => array( 'date' ),
        'order'          => 'DESC',
        'posts_per_page' => 8,
        'meta_query'     => array(
            'relation' => 'AND',
            array(
                'key'   => 'ys_product_status',
                'value' => 'ok'
            ),
            array (
                'key'   => 'ys_product_start',
                'value' => date('Ymd'),
                'compare' => '>='
            ),
            array (
                'key'   => 'ys_product_end',
                'value' => date('Ymd'),
                'compare' => '<='
            )
        )
    ) );

And on my DB I have two items with post_type ‘ys_product’, and with post_meta like this:

Read More

Item 1

  • meta_key ys_product_status = ‘ok’
  • meta_key ys_product_start = ‘20141101’
  • meta_key ys_product_end = ‘20141230’

Item 2

  • meta_key ys_product_status = ‘ok’
  • meta_key ys_product_start = ‘20141101’
  • meta_key ys_product_end = ‘20150131’

The result of date(‘Ymd’) today is ‘20141226’, which seems clearly between those boundaries.

But if I search only filtering by ‘ys_product_status’ == ‘ok’; I get my two items. But when I add my two other meta, it wont return any results at all.

The contents of $products->request are:

SELECT SQL_CALC_FOUND_ROWS  ysls_posts.ID
 FROM ysls_posts 
 INNER JOIN ysls_postmeta ON ( ysls_posts.ID = ysls_postmeta.post_id )
 INNER JOIN ysls_postmeta AS mt1 ON ( ysls_posts.ID = mt1.post_id )
 INNER JOIN ysls_postmeta AS mt2 ON ( ysls_posts.ID = mt2.post_id )
WHERE 1=1
 AND ysls_posts.post_type = 'ys_product'
 AND (ysls_posts.post_status = 'publish' OR ysls_posts.post_status = 'private')
 AND ( 
  ( ysls_postmeta.meta_key = 'ys_product_status' AND CAST(ysls_postmeta.meta_value AS CHAR) = 'ok'  ) 
   AND 
  ( mt1.meta_key = 'ys_product_start' AND CAST(mt1.meta_value AS CHAR) >= '20141226' ) 
   AND 
  ( mt2.meta_key = 'ys_product_end' AND CAST(mt2.meta_value AS CHAR) <= '20141226' )
 ) GROUP BY ysls_posts.ID  LIMIT 0, 8

The post_meta contents for the two posts I’m targeting:
item 1 item 2

What am I doing wrong?

Related posts

Leave a Reply

1 comment

  1. array (
    'key'   => 'ys_product_start',
    'value' => date('Ymd'),
    'compare' => '>='
    ),
    array (
    'key'   => 'ys_product_end',
    'value' => date('Ymd'),
    'compare' => '<='
    )
    

    here: ys_product_start >= date(‘Ymd’) AND ys_product_end <= date(‘Ymd’) its mean a row must met the both conditions but no one can

    Row 1 NOT  20141101 >= 20141226 AND 20141230 <= 20141226 // false AND  false = false
    meta_key ys_product_start = '20141101'
    meta_key ys_product_end = '20141230'
    
    Row 2 NOT 20141101 >= 20141226 AND 20150131 <= 20141226 // false AND false = false
    
    meta_key ys_product_start = '20141101'
    meta_key ys_product_end = '20150131'