Revisions to XOR functionality for meta_query

added 339 characters in body

Source Link

edited Apr 4, 2013 at 20:58

81
3

//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 = 'event_start_date'='%1\$s' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN '$month_start'%3\$d AND '$month_end'%4\$d )
      OR ( pm2.meta_key = 'event_end_date''%2\$s' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN '$month_start'%3\$d AND '$month_end'%4\$d ) )
    ",
  ) );$pm1_meta_key,
  
  $pm2_meta_key,
 //Insert Posts ID$month_start,
 array and get$month_end
 their data));

  $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 = 'event_start_date''%1\$s' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN '$query_date'%3\$d AND '$month_end'%4\$d )
      AND ( pm2.meta_key = 'event_end_date''%2\$s' 
      AND CAST( pm2.meta_value AS DATE ) NOT BETWEEN '$query_date'%3\$d AND '$month_end'%4\$d ) )
      OR ( ( pm1.meta_key = 'event_start_date' '%1\$s'  
  
     AND CAST( pm1.meta_value AS DATE) NOT BETWEEN '$query_date'%3\$d AND '$month_end'%4\$d )
      AND ( pm2.meta_key = 'event_end_date''%2\$s' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN '$query_date'%3\$d AND '$month_end'%4\$d ) ) )
    "  OR ( pm1.meta_key = '%1\$s' 
      AND CAST( pm1.meta_value AS DATE) = %3\$d );
    ",
  //Insert Posts$pm1_meta_key,
 ID array and$pm2_meta_key,
 get their data$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

//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) ) {

  //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 = 'event_start_date' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN '$month_start' AND '$month_end' )
      OR ( pm2.meta_key = 'event_end_date' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN '$month_start' AND '$month_end' ) )
    "
  ) );
  
   //Insert Posts ID array and get their data
  $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));

  //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 = 'event_start_date' 
    AND CAST( pm1.meta_value AS DATE) BETWEEN '$query_date' AND '$month_end' )
    AND ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) NOT BETWEEN '$query_date' AND '$month_end' ) )
    OR ( ( pm1.meta_key = 'event_start_date'    
     AND CAST( pm1.meta_value AS DATE) NOT BETWEEN '$query_date' AND '$month_end' )
    AND ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) BETWEEN '$query_date' AND '$month_end' ) ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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

//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

added 283 characters in body

Source Link

edited Apr 4, 2013 at 13:21

Kee Ross

81
3

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'));
//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 = 'event_start_date' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN '$month_start' AND '$month_end' )
      OR ( pm2.meta_key = 'event_end_date' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN '$month_start' AND '$month_end' ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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));

  //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 = 'event_start_date' 
    AND CAST( pm1.meta_value AS DATE) BETWEEN '$query_date' AND '$month_end' )
    XORAND ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) NOT BETWEEN '$query_date' AND '$month_end' ) )
    OR ( ( pm1.meta_key = 'event_start_date'    
    AND CAST( pm1.meta_value AS DATE) NOT BETWEEN '$query_date' AND '$month_end' )
    AND ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) BETWEEN '$query_date' AND '$month_end' ) ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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

//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) ) {

  //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 = 'event_start_date' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN '$month_start' AND '$month_end' )
      OR ( pm2.meta_key = 'event_end_date' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN '$month_start' AND '$month_end' ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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));

  //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 = 'event_start_date'
    AND CAST( pm1.meta_value AS DATE) BETWEEN '$query_date' AND '$month_end' )
    XOR ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) BETWEEN '$query_date' AND '$month_end' ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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

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'));
//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 = 'event_start_date' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN '$month_start' AND '$month_end' )
      OR ( pm2.meta_key = 'event_end_date' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN '$month_start' AND '$month_end' ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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));

  //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 = 'event_start_date' 
    AND CAST( pm1.meta_value AS DATE) BETWEEN '$query_date' AND '$month_end' )
    AND ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) NOT BETWEEN '$query_date' AND '$month_end' ) )
    OR ( ( pm1.meta_key = 'event_start_date'    
    AND CAST( pm1.meta_value AS DATE) NOT BETWEEN '$query_date' AND '$month_end' )
    AND ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) BETWEEN '$query_date' AND '$month_end' ) ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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

Source Link

created Apr 4, 2013 at 10:42

Kee Ross

81
3

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.

//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) ) {

  //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 = 'event_start_date' 
      AND CAST( pm1.meta_value AS DATE) BETWEEN '$month_start' AND '$month_end' )
      OR ( pm2.meta_key = 'event_end_date' 
      AND CAST( pm2.meta_value AS DATE ) BETWEEN '$month_start' AND '$month_end' ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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));

  //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 = 'event_start_date'
    AND CAST( pm1.meta_value AS DATE) BETWEEN '$query_date' AND '$month_end' )
    XOR ( pm2.meta_key = 'event_end_date' 
    AND CAST( pm2.meta_value AS DATE ) BETWEEN '$query_date' AND '$month_end' ) )
    "
  ) );
  
  //Insert Posts ID array and get their data
  $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

Stack Exchange Network

Return to Answer