如何处理META_QUERY中COMPARE子句中的可选结束日期

时间：2017-02-16 作者：Dashrath

我有一个自定义的帖子类型event 在WordPress中，我需要查询即将到来的event posts 比较$current_date.

查询条件为：

start_date 是有效的date 始终end_date 可以是有效的date 或null 或empty 字符串end_date 是有效的date 在db记录中，然后比较end_date >= $current_dateend_date 是null 或empty 然后比较start_date >=$current_date.

如果end_date 不是可选的，我可以使用下面的代码来获得所需的结果。

$args= array();
$args[\'post_type\'] = "event";
$args[\'meta_query\'] = array(
      array(
           \'key\'        => \'end_date\',
           \'compare\'    => \'>=\',
           \'value\'      => date("Ymd",$current_date),
       )
);
$post_query = new WP_Query();
$posts_list = $post_query->query($args);

我的问题是，如何处理可选end_date 在上述代码中。

提前谢谢。

编辑：重新格式化上面的代码和文本以使其更清晰

5 个回复

最合适的回答,由SO网友:J.D. 整理而成

为了实现这一点，无需手工创建自定义SQL查询。自版本4.1以来，WordPress的查询类supported complex/nested meta queries. 因此，您可以创建如下查询：

    $args[\'meta_query\'] = array(
        // Use an OR relationship between the query in this array and the one in
        // the next array. (AND is the default.)
        \'relation\' => \'OR\',
        // If an end_date exists, check that it is upcoming.
        array(
            \'key\'        => \'end_date\',
            \'compare\'    => \'>=\',
            \'value\'      => date( \'Ymd\', $current_date ),
        ),
        // OR!
        array(
            // A nested set of conditions for when the above condition is false.
            array(
                // We use another, nested set of conditions, for if the end_date
                // value is empty, OR if it is null/not set at all. 
                \'relation\' => \'OR\',
                array(
                    \'key\'        => \'end_date\',
                    \'compare\'    => \'=\',
                    \'value\'      => \'\',
                ),
                array(
                    \'key\'        => \'end_date\',
                    \'compare\'    => \'NOT EXISTS\',
                ),
            ),
            // AND, if the start date is upcoming.
            array(
                \'key\'        => \'start_date\',
                \'compare\'    => \'>=\',
                \'value\'      => date( \'Ymd\', $current_date ),
            ),
        ),
    );

我已经测试过了，它工作得很好。我的PHPUnit测试用例：

/**
 * Tests something.
 */
class My_Plugin_Test extends WP_UnitTestCase {

    public function test_wpse() {

        $current_time = current_time( \'timestamp\' );
        $current_date = date( \'Ymd\', $current_time );
        $yesterday_date = date( \'Ymd\', strtotime( \'yesterday\' ) );

        $post_ids = $this->factory->post->create_many( 6 );

        $post_with_end_past  = $post_ids[0];
        $post_with_end_now   = $post_ids[1];
        $post_empty_end_past = $post_ids[2];
        $post_empty_end_now  = $post_ids[3];
        $post_null_end_past  = $post_ids[4];
        $post_null_end_now   = $post_ids[5];

        // This post has an end date in the past.
        update_post_meta( $post_with_end_past, \'start_date\', $yesterday_date );
        update_post_meta( $post_with_end_past, \'end_date\', $yesterday_date );

        // This post has an end date in the present.
        update_post_meta( $post_with_end_now, \'start_date\', $yesterday_date );
        update_post_meta( $post_with_end_now, \'end_date\', $current_date );

        // This post has no end date, but a start date in the past.
        update_post_meta( $post_empty_end_past, \'start_date\', $yesterday_date );
        update_post_meta( $post_empty_end_past, \'end_date\', \'\' );

        // This post has an empty end date, but the start date is now.
        update_post_meta( $post_empty_end_now, \'start_date\', $current_date );
        update_post_meta( $post_empty_end_now, \'end_date\', \'\' );

        // This post has no end date set at all, and the start date is past.
        update_post_meta( $post_null_end_past, \'start_date\', $yesterday_date );

        // This post has no end date set at all, but the start date is now.
        update_post_meta( $post_null_end_now, \'start_date\', $current_date );

        $args = array();
        $args[\'fields\'] = \'ids\';
        $args[\'meta_query\'] = array(
            // Use an OR relationship between the query in this array and the one in
            // the next array. (AND is the default.)
            \'relation\' => \'OR\',
            // If an end_date exists, check that it is upcoming.
            array(
                \'key\'        => \'end_date\',
                \'compare\'    => \'>=\',
                \'value\'      => $current_date,
            ),
            // OR!
            array(
                // If an end_date does not exist.
                array(
                    // We use another, nested set of conditions, for if the end_date
                    // value is empty, OR if it is null/not set at all.
                    \'relation\' => \'OR\',
                    array(
                        \'key\'        => \'end_date\',
                        \'compare\'    => \'=\',
                        \'value\'      => \'\',
                    ),
                    array(
                        \'key\'        => \'end_date\',
                        \'compare\'    => \'NOT EXISTS\',
                    ),
                ),
                // AND, if the start date is upcoming.
                array(
                    \'key\'        => \'start_date\',
                    \'compare\'    => \'>=\',
                    \'value\'      => $current_date,
                ),
            ),
        );

        $post_query = new WP_Query();
        $posts_list = $post_query->query( $args );

        // Only the "now" posts should be returned.
        $this->assertSame(
            array( $post_with_end_now, $post_empty_end_now, $post_null_end_now )
            , $posts_list
        );
    }
}

SO网友:Pedro Coitinho

我想我知道你在经历什么。。。我最近不得不处理一种情况，我（除其他外）比较了一个可能存在或不存在的元值。

我找到的解决方案涉及一个非常疯狂的SQl语句，其中包含一个WHERE IF子句。

在您的情况下，它可能看起来像这样（解释如下）：

global $wpdb;

// prepare SQL statement
$sql = $wpdb->prepare("
  SELECT * 
  FROM $wpdb->posts
  INNER JOIN $wpdb->postmeta
    ON $wpdb->posts.ID = $wpdb->postmeta.post_id
  WHERE post_type = \'event\'
    AND post_status = \'publish\'
    AND IF(
      (
        SELECT COUNT( post_id) 
        FROM $wpdb->postmeta 
        WHERE meta_key = \'end_date\' 
        AND post_id = ID 
      ) > 0,
      meta_key = \'end_date\',
      meta_key = \'start_date\'
    )
    AND meta_value >= %s
  LIMIT %d
", date( \'Ymd\'), 10 );

// get results
$results = $wpdb->get_results( $sql );

// iterate through results
foreach( $results as $result ) {
  setup_postdata( $result );

  // your loop
}

（注意，这是在一个类似的设置上测试的，但没有使用您的确切post元字段。可能需要一些调整）。

首先，我们得到全球wpdb 变量，以访问原始wordpress数据库。

然后，我们准备一个SQL查询，该查询获取post并将其与post元数据连接（用于比较）

但是这里是诀窍。WHERE语句同时具有meta_key, meta_value.

我们知道meta_value 始终相同，即当前日期。

所以对于meta_key 我们运行一个IF语句，检查当前post ID的meta键等于“end\\u date”的post meta行数。

如果有超过0行，则设置meta_key 设置为“end\\u date”（IF函数中的第二个参数），否则将其设置为“start\\u date”（第三个参数）。

我们还检查帖子类型，确保它们已发布，并将返回限制为10（或任何您喜欢的内容）。

最后，我们获取结果，以便根据需要使用它们。

我知道这有点像黑客，但它很管用。它也有点慢，所以可能值得缓存结果

希望这就是你要找的！如果您找到更好的解决方案，请告诉我：）

SO网友:TrubinE

//  $startday, $endday - format YYYY-MM-DD and field format: YYYY-MM-DD      
        $args= array();    
        $args[\'post_type\'] = "event";
        $args[\'meta_query\'] = array(
              relation\' => \'OR\',   
              array(
                            \'key\'        => \'end_date\',
                            \'value\' => array( $startday, $endday ),
                            \'type\' => \'DATE\',
                            \'compare\' => \'BETWEEN\'
               ),       
              array(
                            \'key\'        => \'start_date\',
                            \'value\' => array( $startday, $endday ),
                            \'type\' => \'DATE\',
                            \'compare\' => \'BETWEEN\'
               )          
        );
        $post_query = new WP_Query();
        $posts_list = $post_query->query($args);

也许我还是不理解你

SO网友:Phoenix Online

我在这里看到了一些很棒的解决方案；但我们会不会想得太多了？

这行吗。。。

$args= array();
$args[\'post_type\'] = "event";
$args[\'meta_query\'] = array();

if (!empty($end_date) && strtotime($end_date)) {
    $args[\'meta_query\'][] = array(
        \'key\'        => \'end_date\',
        \'compare\'    => \'>=\',
        \'value\'      => date("Ymd",$current_date),
    );
} elseif (!empty($start_date) && strtotime($start_date)) {
    $args[\'meta_query\'][] = array(
        \'key\'        => \'start_date \',
        \'compare\'    => \'>=\',
        \'value\'      => date("Ymd",$current_date),
    )
}

$post_query = new WP_Query();
$posts_list = $post_query->query($args);

SO网友:Thilak

我对你的问题提出这个问题。请检查一下。让我知道它起作用了？。

$global $wpdb;

$qry = "SELECT * from $wpdb->posts t1 WHERE t1.post_type=\'event\' and t1.ID IN (SELECT t2.post_id from $wpdb->postmeta t2 WHERE ( CASE WHEN t2.meta_key = \'end_date\' AND t2.meta_value IS NULL THEN t2.meta_key = \'start_date\' AND t2.meta_value >= \'". $currentdate ."\' ELSE t2.meta_key = \'end_date\' AND t2.meta_key >= \'". $currentdate ."\' END ))"

$post_query = new WP_Query();
$posts_list = $post_query->query($qry);

小码农CODE

如何处理META_QUERY中COMPARE子句中的可选结束日期

相关推荐

如何通过jQuery Datepicker搜索帖子？