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.

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.

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
share|improve this question
    
You are right in that you should use a $wpdb query - so what is the question...? –  vancoder Apr 3 '13 at 18:25
    
I don't really know how to create this $wpdb query, especially with DATE and BETWEEN. Is there any example, that can help me to understand the idea of how to do this? –  Kee Ross Apr 3 '13 at 18:33
    
Not a solution but related because of the format you are using to store your data: wordpress.stackexchange.com/questions/75315/… –  s_ha_dum Apr 3 '13 at 20:17
    
Nice you found a solution! But please also post your solution as an answer and accept it, so that other people easily can A) see that the question has been answered and B) find the solution. –  t f Apr 4 '13 at 10:34
add comment

1 Answer

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
share|improve this answer
2  
I suggest looking in codex for the proper use of prepare. This code will give you a notice that you're using it incorrectly if you enable debugging. –  Milo Apr 4 '13 at 15:32
    
Yeah, did some research and fixed this. –  Kee Ross Apr 4 '13 at 20:59
add comment

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.