How to handle optional end date in compare clause in meta_query

I have a custom post type event in WordPress, and I need to query upcoming event posts comparing $current_date.

Query conditions are :

  • start_date is a valid date always
  • end_date can be a valid date or null or empty string.
  • IF end_date is a valid date in db record then compare end_date = $current_date
  • ELSE IF end_date is null or empty then compare start_date =$current_date.

Now If end_date was not optional , I could use below code to get desired results.

$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);

My problem is, how do I handle optional end_date in above code.

Thanks in advance.

Edit: Reformatted code and text above to make it more clear

Topic date meta-query comparison Wordpress

Category Web

1
answered at

I Make this query for your issue. Kindly check it. Let me Know It's working?.

$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);
1
answered at

I'm seeing some awesome solutions, here; but could we be overthinking it?

Would this work...

$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);
1
answered at

There is no need to craft a custom SQL query in order to achieve this. Since version 4.1, WordPress's query classes have supported complex/nested meta queries. So you can craft a query like this:

    $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 ),
            ),
        ),
    );

I have tested this, and it works perfectly. My PHPUnit testcase:

/**
 * 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
        );
    }
}
1
answered at

I think I know what you are going through ... I recently had to deal with a situation where I was (among other things) comparing a meta value that may or may not exist.

The solution I found involved a very crazy SQl statment, with a WHERE IF clause.

In your case, it could look something like this (explanation below):

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
}

(Note this was tested on a similar setup, but not with your exact post meta fields. Might need a bit of tweaking).

First, we get the global wpdb variable, to access the raw wordpress db.

Then we prepare an SQL query which gets posts and joins them with post meta values (for comparison)

BUT. Here is the trick. The WHERE Statement has both meta_key, meta_value.

We know meta_value is always the same, which is the current date.

So for meta_key we run an IF statement that checks how many post meta rows the current post ID has where its meta key is equal to 'end_date'.

If it has more than 0 rows, then set meta_key to 'end_date' (second argument in the IF function), otherwise set it to 'start_date' (third argument).

We also check post type and make sure they are published and limit the return to 10 (or whatever you'd like).

Finally, we fetch the results to use them as we please.

I know its a bit of a hack, but it works. Its also a bit slow, so might be worth caching the results

Hope that's what you are looking for! And if you find a better solution, please let me know :)

1
answered at 
//  $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);

Perhaps again I did not understand you

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.