Modifying date filter on admin page for custom post type to link to custom field

Question

Apologies if a similar question has already been asked, but I can't seem to find the exact functionality I am looking for.

Basically I have a custom post type (sermons) which has 'recorded_date' as a custom meta data field. What I would like to do is on the custom post admin page (where all posts are shown) is change the existing date filter action to filter by this 'recorded_date' field and not the post_date as it normally does. I've managed to change the date filter dropdown using the following:

add_filter('months_dropdown_results', 'custom_date_dropdown');
function custom_date_dropdown($months){
        global $wpdb;
        $months = $wpdb->get_results("SELECT DISTINCT YEAR( meta_value ) AS year, 
        MONTH(meta_value) AS month FROM wp_postmeta 
        WHERE meta_key = 'recorded_date' ORDER BY meta_value DESC");
        return $months;
}

Which works perfectly. But selecting any value and filtering filters against the post_date. Is there anyway I can intercept this behaviour and change the filter query to my own custom query? Or I am I better off removing the filter and adding a custom one from scratch?

Thanks for any help in advance.

Answer 1

Use the pre_get_posts action to override the default query. In the admin, a date query will be the query var m in the format YYYYMM. Assuming your meta dates are stored the same as MySQL dates (YYYY-MM-DD), you can use the following:

function wpse_189824_date_meta_query( $wp_query ) {
    if ( is_admin() && $wp_query->is_main_query() && $wp_query->get( 'post_type' ) === 'sermons' ) {
        if ( $m = $wp_query->get( 'm' ) ) {
            $y = substr( $m, 0, 4 ); // Year
            $m = substr( $m, 4, 2 ); // Month

            if ( ! $meta_query = $wp_query->get( 'meta_query' ) ) // Keep meta query if there currently is one
                $meta_query = array();

            // Using LIKE query

            $meta_query[] = array(
                'key' => 'recorded_date',
                'value' => "$y-$m-%",
                'compare' => 'LIKE',
            );

            /*

            // OR using DATE

            $meta_query[] = array(
                'key' => 'recorded_date',
                'cast' => 'DATE',
                'value' => array( "$y-$m-01", "$y-$m-31" ),
                'compare' => 'BETWEEN',
            );

            */

            $wp_query->set( 'meta_query', $meta_query );
            $wp_query->set( 'm', null );
        }
    }
}

add_action( 'pre_get_posts', 'wpse_189824_date_meta_query' );

I've given two options (LIKE or DATE), you might want to test and see which gives the best performance.