pre_get_posts query between 2 dates (date stored in custom post meta)

Question

I have a custom post type with posts which have a custom field of date.

On the posts list (edit.php) I want to be able to filter posts between 2 dates based on the custom post meta date (not post date), this field is _exm_date.

I have read these articles:

https://rudrastyh.com/wordpress/date-range-filter.html

Which works great for the actual post date but not for custom fields as it uses date_query.

Therefore I have changed the code around to use meta_query.

I now have the query being passed and I can see my meta_query in the pre_get_posts array but I am getting no results.

I have also attempted the solution here: Query posts between two dates (custom meta keys) CMB2

But this does not work for me, the code is similar to mine.

I have also studied: Date query for a custom meta field

Here is my pre_get_posts action query:

    public function filterquery( $admin_query ){
        global $pagenow;

        if (
            is_admin()
            && $admin_query->is_main_query()
            // by default filter will be added to all post types, you can operate with $_GET['post_type'] to restrict it for some types
            && in_array( $pagenow, array( 'edit.php', 'upload.php' ) )
            && ( ! empty( $_GET['mishaDateFrom'] ) || ! empty( $_GET['mishaDateTo'] ) )
        ) {




            $meta_query = array(
                'relation' => 'AND',
                array(
                    'key' => '_exm_date',
                    'value' => date( 'ymd', strtotime( $_GET['mishaDateFrom'] ) ),
                    'compare' => '>=',
                    'type' => 'DATE'
                ), 
                array(
                    'key' => '_exm_date',
                    'value' => date( 'ymd', strtotime( $_GET['mishaDateTo'] ) ),
                    'compare' => '<=',
                    'type' => 'DATE'
                )
            );

             $admin_query->set( 'meta_query', $meta_query );

        }

        print_r($admin_query);

        return $admin_query;

    }

$admin_query output is:

### Solution ###

I think I may have just solved this within one minute of posting this. It's because the date being saved as custom meta was in a different format to the value in the query. Doh!

Answer 1

You can always use a BETWEEN comparison in your query, I have used this, maybe you can adapt it to work in your situation. It avoids doing multiple checks for no reason :)

$first_date = 110501;
$second_date = 170514;
$meta_query = array(
           'key' => '_exm_date',
           'value' => array($first_date, $second_date ),
           'type' => 'DATE',
           'compare' => 'BETWEEN'
        );