0

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!

  • 1
    Look at the SQL generated for each query, echo $query->request after each and compare them. – Milo Mar 6 '14 at 3:31
  • You can edit your question and add that output so we can have a look at it. – Milo Mar 6 '14 at 5:01
  • Generated SQL for each query added. – jdm2112 Mar 7 '14 at 0:17
  • I don't think any of those queries are technically giving you correct results, and it has to do with your date format. 3/5/2014 23:11 is not a format MySQL understands. if you get correct results in some cases, it's accidental. you need to store it in a format like 2014/03/05 23:11. in your current format, April 5 2013 would be greater than March 5 2014, because from a numerical perspective 452013 > 352014. – Milo Mar 7 '14 at 0:30
  • Milo you were absolutely right. Simply reformatting the date in the DB, adjusting the format of my date test to match and the double meta query produces the expected results. If you are interested in posting your comment as an answer I will gladly give you the credit you deserve. – jdm2112 Mar 9 '14 at 16:19
0

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'
) ) ));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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