Wordpress multiple meta query

Question

I am trying to get data from below query. I want result between two dates by first key and other key value if equal 1 but query returns value for me only today's date data and in sql add OR relationship. Please help me to figure out the wrong and to get correction.

My code is

 $curent_date = date('Y-m-d');
 $start_date = date('Y-m-d 00:00:01', strtotime($curent_date."- 4 day"));
 $end_date = date('Y-m-d 23:59:59', strtotime($curent_date."+ 4 day"));
$events_query = new WP_Query( array(
    'post_type' => array('tribe_events'),
    'post_status' => 'publish', 
    'meta_key' => '_EventStartDate',
    'orderby'   => 'meta_value',
    'order'     => 'DESC',
    'meta_query' => array( 
        'relation' => 'AND',
        array(
        'key' => '_EventStartDate',
        'value' => array( $start_date, $end_date ),
        'compare' => 'BETWEEN'
    ),
    array(
        'key' => 'agenda',
        'value' => 1,
        'compare' => '='
    ),
    ),) );
 print_r($events_query->request);

 print_r($events_query->posts);

Return SQL is

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, MIN(tribe_event_end_date.meta_value) as EventEndDate 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 )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = '_EventEndDate' )  WHERE 1=1  AND ( 
  wp_postmeta.meta_key = '_EventStartDate' 
  AND 
  ( 
    mt1.meta_key = '_EventStartDate' 
    AND 
    ( mt2.meta_key = '_EventStartDate' AND mt2.meta_value BETWEEN '2017-05-22 00:00:01' AND '2017-05-30 23:59:59' ) 
    AND 
    ( mt3.meta_key = 'agenda' AND mt3.meta_value = '1' )
  )
) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish')) AND **(wp_postmeta.meta_value >= '2017-05-26 02:58:14' OR (wp_postmeta.meta_value <= '2017-05-26 02:58:14' AND tribe_event_end_date.meta_value >= '2017-05-26 02:58:14' )) GROUP BY wp_posts.ID ORDER BY EventStartDate DESC, wp_postmeta.meta_value DESC LIMIT 0, 10

Thanks in advance.

Dat Nguyen · Answer 1 · 2017-05-26T03:28:34.223

Compare datetime in wp_query you need convert your meta query item of date to DATE OrDATETIME`.

$events_query = new WP_Query( array(
'post_type' => array('tribe_events'),
'post_status' => 'publish', 
'meta_key' => '_EventStartDate',
'orderby'   => 'meta_value',
'order'     => 'DESC',
'meta_query' => array( 
    'relation' => 'AND',
    array(
    'key' => '_EventStartDate',
    'value' => array( $start_date, $end_date ),
    'compare' => 'BETWEEN',
    'type'=>'DATE'
),
array(
    'key' => 'agenda',
    'value' => 1,
    'compare' => '='
),
),) );

and if this not working you can try this code

$events_query = new WP_Query( array(
'post_type' => array('tribe_events'),
'post_status' => 'publish', 
'meta_key' => '_EventStartDate',
'orderby'   => 'meta_value',
'order'     => 'DESC',
'meta_query' => array( 
    'relation' => 'AND',
    array(
           'key' => '_EventStartDate',
           'value' => $start_date,
           'compare' => '<=',
           'type' => 'DATE'
       ),
array(
           'key' => '_EventEndDate',
           'value' => $end_date,
           'compare' => '>=',
           'type' => 'DATE'
       )
array(
    'key' => 'agenda',
    'value' => 1,
    'compare' => '='
),
),) );

No. Both are not working. Please look at my sql and there is the main problem. Bold text are return wrong result. Only todays result. — Destroyer.0211, May 26 '17 at 03:36
Your code in my server http://downpublic.info/wpdemo/RS1991_ElseVillano/ — Dat Nguyen, May 26 '17 at 03:48
but for me add this **OR (wp_postmeta.meta_value <= '2017-05-26 05:30:23' AND tribe_event_end_date.meta_value >= '2017-05-26 05:30:23' )) GROUP BY wp_posts.ID ORDER BY EventStartDate DESC, wp_postmeta.meta_value DESC LIMIT 0, 10** — Destroyer.0211, May 26 '17 at 05:35

Wordpress multiple meta query

1 Answers1