Inefficient Query Confusion

时间：2011-12-16 作者：John Bentwin

Edit #3 - meta_keys as an array

这似乎不仅仅是“opening\\u time”的比较，我可以为错误的日期设置打开时间，甚至可以将其留空，如果“panel\\u time”在范围内，它仍然会占据一个帖子。

我错过什么了吗？

$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

这是按各自的元键对所有帖子进行排序的代码。无论是面板还是车间，它都会按开始日期将其移动到天数和订单中<我只是需要一种方法来把帖子放到这个阶段。

通过这个来运行所有“事件”自定义帖子类型是愚蠢的，因为几年后，这个列表对于计算机来说会很长，对吧？

有什么建议吗？

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

我可以像下面的代码一样把这些键放成一系列吗？我把它放进了我的测试站点，它似乎起到了作用，因为我下面有一个函数，可以根据键值对帖子进行排序。还是这本身就有问题？

$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\'
    ),

)
);
我一直在努力找出代码中的瓶颈所在。我对不同的部分进行了评论，试图将冒犯部分隔离开来。如果我注释掉这段代码，它将以正常速度运行，但使用此查询可能需要很长时间才能加载+10秒。

此查询是否存在固有的低效性？有没有更简单的方法。所有键值的格式均为Y-m-d 00:00。

提前谢谢你。

<?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;
  }

1 个回复
最合适的回答,由SO网友:onetrickpony 整理而成

。。。或者试试这个：

// ... 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
理论上，它应该比meta\\u查询生成的更快。。。

结束
标签： custom-post-types   wp-query   metabox   array   小码农  

相关推荐

删除评论Metabox，但仍允许评论

我已经知道如何从自定义帖子类型编辑页面中删除元数据库。然而，我想删除评论元框，但仍然允许对帖子发表评论。因为我注意到当我删除它时，它会禁用注释。我可以使用什么功能？