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.

Edit #3 - meta_keys as an array

This seems to compare more than just the "opening_time" I can set the opening time for the wrong date or even leave it blank and it will still grab a post if the "panel_time" is in range.

Am I missing something?

$keys = array('opening_time', 'closing_time', 'artist_talk_time', 'special_event_time', 'lecture_time', 'panel_time', 'workshop_time');

$args = array(
 'post_type' => 'event',
 'orderby'     => 'meta_value',
 'order'       => 'asc',
 'meta_query' => array(
 'relation' => 'OR',
    array(
        'key' => $keys,
        'value' => array($today,$future),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
    ),

)
);

Edit #2 to show sorting of met_keys after the posts query

This is the code that sorts all the posts by their respective meta keys. It moves this into days and orders by start date whether it is a panel or a workshop.
I just need a way to get posts to this stage.

It would be stupid to runt all "events" custom posts types through this since after a few years the list would be very long for the computer to chunk through right?

Any suggestions?

      <?php 
  $event_query = new WP_Query( $args ); 
  if ($event_query->have_posts()) : while ($event_query->have_posts()) :  $event_query->the_post();  

  $keys = array('opening_time', 'closing_time', 'artist_talk_time', 'special_event_time', 'lecture_time', 'panel_time', 'workshop_time');
  $custom_field_keys = get_post_custom_keys();

  foreach ($custom_field_keys as $custom_field_key) {
     if (in_array($custom_field_key, $keys)) {    
          $custom_field_value = get_post_meta($post->ID, $custom_field_key, true);
          if ($custom_field_value >= $thedate && $custom_field_value <= $future) {
             $counttest++;
             $times[] = array($custom_field_value, $post->ID, $custom_field_key);
          }
      }
  }     
  endwhile; 

  if ($counttest >0) {

  echo $counttest;

  if ($counttest>=2) {
          echo ' Events ';
      } 
      if ($counttest<2) { 
          echo ' Event ';
      }
  }
  endif;
  $counttest=0;

  asort($times); 

  foreach ($times as $event) { $time_value = $event[0]; $post_id = $event[1]; $time_key = $event[2];  // changed the foreach here

      if ($time_key == 'opening_time') {/* do something */  }

      else if ($time_key == 'artist_talk_time') { /* do something */ }
      else if ($time_key == 'closing_time') { /* do something */ }
      else if ($time_key == 'special_event_time') { /* do something */ }

      rewind_posts();
   }
   ?>

Edit to add suggestion about another possible solution to simplify things

Can I put the keys as a series like the code below? I put it into my test site and it seems to work because I have a function below that sorts the posts by the key values. Or does this have problems of its own?

$args = array(
 'post_type' => 'event',
 'orderby'     => 'meta_value',
 'order'       => 'asc',
 'meta_query' => array(
 'relation' => 'OR',
    array(
        'key' => 'opening_time','closing_time','artist_talk_time','special_event_time',
        'value' => array($today,$future),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
    ),

)
);

Have been trying to figure out where the bottle neck in my code is. I commented different parts to try to isolate to offending portion. If I comment out this section of code it runs at normal speed, but with this query it can take an very long time to load + 10 seconds.

Is there an inherent inefficiancy with this query? Is there an easier way to do it. All of the key values are in the format Y-m-d 00:00.

Thank you in advance.

<?php   
  $today = date("Y-m-d 00:00");  //set today's date for upcomming events sidebar
  for ($i=0; $i<10; $i++) {
  $thedate = strtotime ( '+'.$i.' day' , strtotime ( $today ) ) ;
  $thedate = date ( 'Y-m-d H:i' , $thedate );

  $thedaytext = strtotime ( '+'.$i.' day' , strtotime ( $todaytext ) ) ;
  $thedaytext = date ( 'l' , $thedaytext );

  $thedatetext = strtotime ( '+'.$i.' day' , strtotime ( $todaydatetext ) ) ;
  $thedatetext = date ( 'F j' , $thedatetext );

  $future = strtotime ( '+24 hours' , strtotime ( $thedate ) ) ;
  $future = date ( 'Y-m-d H:i' , $future ); 
  $times = array();  

  $args = array(
       'post_type' => 'event',
       'orderby'     => 'meta_value',
       'order'       => 'asc',
       'meta_query' => array(
       'relation' => 'OR',
          array(
              'key' => 'opening_time',
              'value' => array($today,$future),
              'compare' => 'BETWEEN',
              'type' => 'DATE'
          ),
          array(
              'key' => 'artist_talk_time',
              'value' => array($today,$future),
              'compare' => 'BETWEEN',
              'type' => 'DATE'
          ),
          array(
              'key' => 'closing_time',
              'value' => array($today,$future),
              'compare' => 'BETWEEN',
              'type' => 'DATE'
          ),
          array(
              'key' => 'special_event_time',
              'value' => array($today,$future),
              'compare' => 'BETWEEN',
              'type' => 'DATE'  
          )
      )
  );

  $event_query = new WP_Query( $args ); 

  if ($event_query->have_posts()) : while ($event_query->have_posts()) :  $event_query->the_post();

  endwhile;
  endif;
  }
share|improve this question
3  
Well that meta relation triggers 4 joins, so it's expected to be slow. You should make your own table and avoid custom post types if you need to make that kind of queries. Or at least move the meta fields in their own table (keys as columns) –  onetrickpony Dec 16 '11 at 2:40
    
@OneTrickPony Thank you for the comment. Is there a way to just get the posts that have any key value that is between those dates rather than using OR. I just want the posts that have meta_key dates between $today and $future What do you mean by "move the meta fields in their own table (keys as columns)? The eventual query will need to check 8-9 date keys so if 4 is too slow then I need to find a better solution. –  John Bentwin Dec 16 '11 at 3:26

1 Answer 1

up vote 2 down vote accepted

...or try this:

// ... put your $today and $future variables here...

global $wpdb;

$events = $wpdb->get_results(
" SELECT *

  FROM {$wpdb->posts}

  LEFT JOIN(
   SELECT DISTINCT post_id,
    (SELECT CAST(meta_value AS DATE) FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = meta.post_id AND meta_key ='opening_time') AS opening_time,
    (SELECT CAST(meta_value AS DATE) FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = meta.post_id AND meta_key ='artist_talk_time') AS artist_talk_time,
    (SELECT CAST(meta_value AS DATE) FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = meta.post_id AND meta_key ='closing_time') AS closing_time,
    (SELECT CAST(meta_value AS DATE) FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = meta.post_id AND meta_key ='special_event_time') AS special_event_time,
    FROM {$wpdb->postmeta} meta )
   AS mymeta ON {$wpdb->posts}.ID = mymeta.post_id

  WHERE
        post_status = 'publish' 
    AND post_type = 'event' 
    AND ( 
          mymeta.opening_time BETWEEN {$today} AND {$future}
       OR mymeta.artist_talk_time BETWEEN {$today} AND {$future}
       OR mymeta.closing_time BETWEEN {$today} AND {$future}
       OR mymeta.special_event_time BETWEEN {$today} AND {$future}
      )

  GROUP BY ID
  ORDER BY opening_time
  LIMIT 0,10
");

 var_dump($events); // you should get a list of 10 events here, ordered like you wanted

Theoretically it should be faster than what meta_query generates...

share|improve this answer
    
I will try that. I have never done a DB select like that. Does it replace $event_query = new WP_Query( $args ); if ($event_query->have_posts()) : while ($event_query->have_posts()) : $event_query->the_post(); ? Not sure where to put it in the post template. I tested in phpmyadmin and recieved this error message #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&quot;SELECT * FROM {$wpdb-&gt;posts} LEFT JOIN( SELECT DISTINCT post_id, ' at line 1 –  John Bentwin Dec 16 '11 at 3:31
    
I modified the query in the original question. It seems to speed things buck up to normal on my test server. Does it look ok? Thank you again for the help. –  John Bentwin Dec 16 '11 at 3:45
1  
ah you need to remove quotes and replace variables with table names and sample dates :) I updated the code so you can insert it in your php template. In your second edit you are only comparing "opening_time" –  onetrickpony Dec 16 '11 at 3:46
    
You are right, it is only comparing the "opening_time" But if the opening time is in the right range and other meta_keys are set then the sorting I have after the wp_query catches everything. I posted the sorting code above in the question. Is there a simpler way to get the posts with meta_keys in this range to the sorter? I do not need the query to sort the posts. –  John Bentwin Dec 16 '11 at 4:17
    
Thank you for the help. I accept your answer. For now the code I posted above seems to work. –  John Bentwin Dec 16 '11 at 20:02

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.