Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Having trouble with WP_Query returning no posts when filtering on two custom fields, although testing for each custom field individually works perfectly. After reviewing documentation for custom field parameters I'm obviously still missing something. Thank you in advance for any assistance or ideas.

Query that produces no posts:

$query = new WP_Query(array(
'post_type'=>'games',
'orderby'=>'menu_order',
'order'=>'asc',
'posts_per_page' => 1,
'meta_query'=> array(
    'relation'=>'AND',
    array(
        'key'=>'game_date_time',
        'value'=>$nowtoday,
        'compare' =>'>'
    ),
    array(
        'key'=>'home_team',
        'value'=>'Allen',
        'compare'=>'LIKE'  
    )
)
));    

SQL produced:

 `SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1 AND wp_posts.post_type = 'games' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( (wp_postmeta.meta_key = 'game_date_time' AND CAST(wp_postmeta.meta_value AS CHAR) > '3/5/2014 23:12') AND (mt1.meta_key = 'home_team' AND CAST(mt1.meta_value AS CHAR) LIKE '%Allen%') ) GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order asc LIMIT 0, 1`

The var $nowtoday is a formatted timestamp for comparing against the custom field game_date_time. Querying only on game_date_time yields the expected result. Here is that query and it returns the next chronological game (home or away) as intended.

$query = new WP_Query(array(
'post_type'=>'games',
'orderby'=>'menu_order',
'order'=>'asc',
'posts_per_page' => 1,
'meta_query'=> array(
    'relation'=>'AND',
    array(
        'key'=>'game_date_time',
        'value'=>$nowtoday,
        'compare' =>'>'
    ),
    array(
       /* 'key'=>'home_team',
        'value'=>'Allen',
        'compare'=>'LIKE'  */
    )
)
));

SQL produced:

`SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'games' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( (wp_postmeta.meta_key = 'game_date_time' AND CAST(wp_postmeta.meta_value AS CHAR) > '3/5/2014 23:11') ) GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order asc LIMIT 0, 1`

Similarly, the modified query to return only home games, that is to say, where home_game is LIKE 'Allen', returns the expected result.

$query = new WP_Query(array(
'post_type'=>'games',
'orderby'=>'menu_order',
'order'=>'asc',
'posts_per_page' => 1,
'meta_query'=> array(
    'relation'=>'AND',
    array(
       /*  'key'=>'game_date_time',
        'value'=>$nowtoday,
        'compare' =>'>'  */
    ),
    array(
       'key'=>'home_team',
        'value'=>'Allen',
        'compare'=>'LIKE'  
    )
)
));

SQL produced:

`SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'games' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( (wp_postmeta.meta_key = 'home_team' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%Allen%') ) GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order asc LIMIT 0, 1`

The puzzle is why the two filters together produce no result, when there are numerous posts that meet both conditions, game_date_time > $nowtoday AND home_team LIKE 'Allen'.

Thank you for your time and any suggestions toward a solution!

share|improve this question
1  
Look at the SQL generated for each query, echo $query->request after each and compare them. –  Milo Mar 6 at 3:31
    
Thank you, Milo. That is a great suggestion. I actually tried that but I was not successful in decoding the raw SQL statements. –  jdm2112 Mar 6 at 4:38
    
You can edit your question and add that output so we can have a look at it. –  Milo Mar 6 at 5:01
    
Very generous offer sir. I will do that now. –  jdm2112 Mar 6 at 5:05
    
Generated SQL for each query added. –  jdm2112 Mar 7 at 0:17

1 Answer 1

up vote 0 down vote accepted

The first query gives the expected results when the date format was corrected.

$query = new WP_Query(array( 'post_type'=>'games', 'orderby'=>'menu_order', 'order'=>'asc', 'posts_per_page' => 1, 'meta_query'=> array( 'relation'=>'AND', array( 'key'=>'game_date_time', 'value'=>$nowtoday, 'compare' =>'>' ), array( 'key'=>'home_team', 'value'=>'Allen', 'compare'=>'LIKE'
) ) ));

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.