Query posts between two dates (custom meta keys) CMB2

Question

I have a custom post type (projects) which have custom cmb2 meta keys (str_dte and end_dte), so each project has a start date and end date. What I am trying to do is to get projects between two dates.

Example:

Project #1: start date = 20120101 (Jan 2012) / end date = 20120601 (Jun 2012) (Ymd)

Project #2: start date = 20120401 (Apr 2012) / end date = 20121201 (Dec 2012) (Ymd)

The query:

query_posts(array(
               'posts_per_page' => $numPosts,
               'paged'          => $page,
               'post_type' => 'projects',
               'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'       => 'str_dte',
            'compare'   => '>=',
            'value'     => '20120501', //May 2012
            'type'      => 'DATE'
        ),
        array(
            'key'       => 'end_dte',
            'compare'   => '<=',
            'value'     => '20120701', //July 2012
            'type'      => 'DATE'
        )
    ),

               ));

Problem: this query didn't display any project, because it didn't realise the date range between the start date and end date of each project.

Shortly: I need to find a solution to query projects/posts between two dates (start date and end date) considering the date range between each project start and end date.

Online Resources: the most useful solution I have found online was (robbiegod/keesiemeijer) solution but it didn't work for me, I don't know why!

Your help is appreciated. Thank you.

SOLUTION

query_posts(array(
                   'posts_per_page' => $numPosts,
                   'paged'          => $page,
                   'post_type' => 'projects',
                   'meta_query' => array(
            'relation' => 'AND',
            array(
                'key'       => 'str_dte',
                'compare'   => '<=',
                'value'     => '$end_date_value', // 20120701 July (Ymd or UnixTime)
                'type'      => 'DATE'
            ),
            array(
                'key'       => 'end_dte',
                'compare'   => '>=',
                'value'     => '$start_date_value', // 20120501 May (Ymd or UnixTime)
                'type'      => 'DATE'
            )
        ),

                   ));

Just put the start date value result (date) as value for the end date meta key, and put the end date value result (date) as value for the start date meta key.

That's it.

Answer 1

To find projects happening within a range of dates, there are 3 cases you have to account for:

• A project starts within the range
• A project ends within the range
• A project starts before the range and ends after the range

You can test for these 3 cases separately with an OR meta query and 1 nested AND:

'meta_query' => array(
    'relation' => 'OR',
    array(
        'key'     => 'str_dte',
        'value'   => array( $range_start, $range_end ),
        'compare' => 'BETWEEN',
    ),
    array(
        'key'     => 'end_dte',
        'value'   => array( $range_start, $range_end ),
        'compare' => 'BETWEEN',
    ),
    array(
        'relation' => 'AND',
        array(
            'key' => 'str_dte',
            'value' => $range_start,
            'compare' => '<',
        ),
        array(
            'key' => 'end_dte',
            'value' => $range_end,
            'compare' => '>',
        ),
    ),
),

If you're doing lots of these queries and performance is a concern, you can probably come up with a faster query manually than this will generate.

Answer 2

First, do not use query_posts! (Why not? That is why!)

Second, if your custom fields (CF) are saved in the Ymd-form (you are sure about that, yes?!), everything should be fine, even if you do not define the "type" as "date". Reading through several discussions on SE, etc. it seems to be better just to avoid the "date"-type, if you are not comparing a date-value against an array of two dates in one meta_query-array. So, remove 'type'=>'date'.

Having said that, I wonder that your query returns a result at all, because if I walk through it, this is what comes out: Both conditions have to be true as you are using a relation of "and".

First condition: If the value of 20120101 (= value) is greater than or equal to (= compare) the value stored in str_date (= key), we are good to go.

• Project #1: 20120101 >= 20120101: true.
• Project #2: 20120101 >= 20120401: false.

Second condition: If the value of 20120630 (= value) is less than or equal to (= compare) the value stored in end_dte (= key), we are good to go.

• Project #1: 20120630 <= 20120601: false.
• Project #2: 20120630 <= 20121201: true.

As neither project fulfills both conditions, I am wondering that WP returns even one. I think for what you want, this query should work:

// New Query: se216174
$se216174_args = array(
  'posts_per_page' => -1, // Why '-1'? Because the custom fields limit the results anyway
  'post_type' => 'projects',
  'meta_query' => array(
    'relation' => 'AND', // This is default, you could skip it
    array(
      'key' => 'str_dte',
      'compare' => '<=', // I have changed this, so everything with a date greater than what we have in the next line should be displayed.
      'value' => '20120101',
    ),
    array(
      'key' => 'end_dte',
      'compare' => '>=', // Same as above
      'value' => '20121201', // New date so that both your projects will match
    ),
  ),
);
$se216174_query = new WP_Query( $se216174_args );
if ( $se216174_query->have_posts() ) : 
  while ( $se216174_query->have_posts() ) :
    $se216174_query->the_post();
    // Display post, eg. echo the_title();
  endwhile; wp_reset_postdata();
else :
  // No posts, eg. echo 'Nothing found';
endif;

Not tested, but this should work. Let me quickly check as I did above:

First condition: If the value of 20120101 (= value) is less than or equal to (= compare) the value stored in str_date (= key), we are good to go.

• Project #1: 20120101 <= 20120101: true.
• Project #2: 20120101 <= 20120401: true.

Second condition: If the value of 20120630 (= value) is greater than or equal to (= compare) the value stored in end_dte (= key), we are good to go.

• Project #1: 20121201 >= 20120601: true.
• Project #2: 20121201 >= 20121201: true.

So, if you want to show everything between 20120101 and 20121201 - this is the query you need.