XOR functionality for meta_query

3 Views

I’m trying to resolve the problem with event displaying.

An event has its start and end dates, written in meta. For example, event starts on (dmY) 03.04.2013 and ends up on 08.04.2013. Using WP_query and meta_query I can exclude this event if I am trying to get data by 09.04.2013.

Read More

At the same time this event will show up each day in the interval, that is cool too. But, I can’t really exclude this event if I’m trying to get data by 02.04.2013.
The interval I use is this month, I mean, for example from 01.04.2013 to 30.04.2013.

Here is the code.
In my particular situation I need XOR functionality for meta_relation. The best way is to create a custom $wpdb query, but I’m not too strong in MySQL…. 

//Week events
$query_date = $_GET['date'];
$month_start = date('Ym01',strtotime('this month'));
$month_end = date('Ymt',strtotime('this month'));
//IF query Day is empty
if ( empty($query_date) || ( strlen($query_date) != 8 ) || ( !is_numeric($query_date) ) ) {

  //Custom query
  $args = array(
    'post_type' => 'post',
    'orderby' => 'event_start_date',
    'order' => 'ASC',
    'meta_query' => array(
    'relation' => 'OR',
      array(
        'key' => 'event_start_date',
        'value' => array( $month_start, $month_end),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
      ),
      array(
        'key' => 'event_end_date',
        'value' => array( $month_start, $month_end),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
      )
    )
  );

} else {

  //Custom query
  $args = array(
    'post_type' => 'post',
    'orderby' => 'event_start_date',
    'order' => 'ASC',
    'meta_query' => array(
    'relation' => 'OR',
       array(
        'key' => 'event_start_date',
        'value' => array( $query_date, $month_end),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
       ),
       array(
        'key' => 'event_end_date',
        'value' => array( $query_date, $month_end),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
       )
     )
   );
  }

$wp_query = new WP_Query( $args );
//End of custom query
Post Views: 3

Related posts

Leave a Reply

1 comment

  1. Okay!
    So, having spent all the night in order to find a right solution… finally.. I did it.
    Here is the right method, that works in my situation.

    The $wpbd method is pretty cool and complicated at the same time.
    While using this method do not forget to cache queries properly with php-apc or memcache.

    While doing some testings realized, that XOR logic operation does not work properly. So, I replaced XOR by a set: XOR = ( ( A AND NOT B ) OR ( NOT A AND B ) ). This one works!

    //Week events
$query_date = $_GET['date'];
$month_start = date('Ym01',strtotime('this month'));
$month_end = date('Ymt',strtotime('this month'));
$pm1_meta_key = 'event_start_date';
$pm2_meta_key = 'event_end_date';

//IF query Day is empty
if ( empty($query_date) ) {

  //Custom query
  $postids = $wpdb->get_col( $wpdb->prepare(
    "
    SELECT p.* FROM $wpdb->posts p
    JOIN $wpdb->postmeta pm1 ON (p.ID = pm1.post_id)
    JOIN $wpdb->postmeta pm2 ON (p.ID = pm2.post_id)
    WHERE p.post_type = 'post'
      AND p.post_status = 'publish'
      AND ( ( pm1.meta_key ='%1$s' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN %3$d AND %4$d )
      OR ( pm2.meta_key = '%2$s' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN %3$d AND %4$d ) )
    ",
   $pm1_meta_key,
   $pm2_meta_key,
   $month_start,
   $month_end
  ));

  $args = array(
    'post__in' => $postids,
    'meta_key' => 'event_start_date',
    'orderby' => 'meta_value_num',
    'order' => 'ASC'
  );

} else {

  //Date
  $query_date = date('Ymd', strtotime($query_date));
  $query_date_month = date('m', strtotime($query_date));

  //If future month
  if ( $query_date_month > date('m') ) {
    $month_end = date('Ymt', mktime(0, 0, 0, $query_date_month, 1, date('y')));
  }

  //Custom query
  $postids = $wpdb->get_col( $wpdb->prepare(
    "
    SELECT p.* FROM $wpdb->posts p
    JOIN $wpdb->postmeta pm1 ON (p.ID = pm1.post_id)
    JOIN $wpdb->postmeta pm2 ON (p.ID = pm2.post_id)
    WHERE p.post_type = 'post'
      AND p.post_status = 'publish'
      AND ( ( ( pm1.meta_key = '%1$s' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN %3$d AND %4$d )
      AND ( pm2.meta_key = '%2$s' 
      AND CAST( pm2.meta_value AS DATE ) NOT BETWEEN %3$d AND %4$d ) )
      OR ( ( pm1.meta_key = '%1$s' 
      AND CAST( pm1.meta_value AS DATE) NOT BETWEEN %3$d AND %4$d )
      AND ( pm2.meta_key = '%2$s' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN %3$d AND %4$d ) ) )
      OR ( pm1.meta_key = '%1$s' 
      AND CAST( pm1.meta_value AS DATE) = %3$d )
    ",
   $pm1_meta_key,
   $pm2_meta_key,
   $query_date,
   $month_end
  ) );

  $args = array(
    'post__in' => $postids,
    'meta_key' => 'event_start_date',
    'orderby' => 'meta_value_num',
    'order' => 'ASC'
  );

}

$wp_query = new WP_Query( $args );
//End of custom query