6

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

  • What does it mean when an event has no end date? The event never ends, or it ends the same day it starts? You could probably make your life much easier by storing some value in end date- either the same date as start date, or something impossibly high, like all 9's. – Milo Feb 17 '17 at 4:38
  • when end_date is not specified (means if it has null or empty value), system assumes that event will end on same start_date.and this is an old db schema and so so many records are arranged already this way. so there is nothing much I can change now in schema. – Dashrath Feb 17 '17 at 10:11
8
+50

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
        );
    }
}
| improve this answer | |
  • +5 if I could. Proper approach and not only testing, but providing a unit test. Bravo! – bosco Feb 23 '17 at 0:47
  • 1
    Unfortunately one thing is certain- that will not be a fast query. – Milo Feb 23 '17 at 4:47
  • @Milo Yeah, that's the unfortunate thing about meta queries anyway. Although, because of the OR condition, the latter more complex part of the query will only run when the end_date is not set. So the most posts that don't have the end_date set, the more expensive it will be. Of course, none of this is an effect of using WP_Query, the best alternative would be to still use WP_Query, but process some of the conditions in PHP instead. – J.D. Feb 23 '17 at 12:49
1

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 :)

| improve this answer | |
  • I know a solution of this problem in sql query version,i'll post it too later but is there a way to do it in meta_query way or posts_where filter ? – Dashrath Feb 17 '17 at 11:03
0
//  $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

| improve this answer | |
  • sorry If I could not make query conditions clear. question is updated now – Dashrath Feb 16 '17 at 20:18
0

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);
| improve this answer | |
0

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);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.