How to Sort by Date When Using d-m-Y Format

Question

This question is pursuant to the following - How to Sort Custom Field Admin Column by Date

I'm attempting to add functionality to sort a custom post admin list column by a date manually specified in each posts custom field. The date is added to the posts in the d-m-Y format, this cannot be changed.

I've used the below code to add the new column in the custom post admin list and everything is working correctly aside from the sorting. The dates are not ordering correctly when the sort link is clicked.

I'm told that the issue is due to the fact that the dates are not stored in a mysql format and that I need to manually change the sql query using the posts_orderby hook, but I'm not quite sure how to achieve this.

Any help would be much appreciated.

//add custom field column to post list
function add_admin_course_column_title( $columns ) {
  $columns['online_start'] = __( 'Online Start' );
  return $columns;
}
add_filter( 'manage_courses_posts_columns', 'add_admin_course_column_title' );

function add_admin_course_column( $column, $post_id ) {
    if ( 'online_start' === $column ) {
        $onlinestart = get_post_meta( $post_id, 'online_start', true );

        if ( ! $onlinestart ) {
                _e( 'n/a' );
        } 
        else {
            echo $onlinestart;
        }
    }
}
add_action( 'manage_courses_posts_custom_column', 'add_admin_course_column', 10, 2);

function add_sortable_date_column( $columns ) {
  $columns['online_start'] = 'online_start';
  return $columns;
}
add_filter( 'manage_edit-courses_sortable_columns', 'add_sortable_date_column');

function courses_columns_orderby( $query ) {
    if( ! is_admin() )
    return;
    $orderby = $query->get( 'orderby');
    switch( $orderby ){
        case 'online_start': 
            $query->set('meta_key','online_start');
            $query->set('orderby','meta_value_datetime');
            $query->set('meta_type', 'datetime');
            break;
        default: break;
    }
}
add_action( 'pre_get_posts', 'courses_columns_orderby' );

Answer 1

You need to extend the MySQL-Query directly, as you have to use the MySQL-Function STR_TO_DATE in order to not mess with the pagination etc.

You should be able to achieve this with a change in your pre_get_posts like this:

add_action( 'pre_get_posts', 'courses_columns_orderby' );

function courses_columns_orderby( $query ) {
    if( ! is_admin() )
    return;
    $orderby = $query->get( 'orderby');
    switch( $orderby ){
        case 'online_start': 
            add_filter( 'posts_join', 'do_order_by_custom_date_field_join' );
            add_filter( 'posts_orderby', 'do_order_by_custom_date_field_orderby',10,2 );
            break;
        default: break;
    }
}

Now we join our meta_table manually so that we can define which alias it gets:

function do_order_by_custom_date_field_join($join){
    global $wpdb;
    $join.="LEFT JOIN $wpdb->postmeta my_order_meta ON (($wpdb->posts.ID = my_order_meta.post_id) AND (my_order_meta.meta_key = 'online_start')) ";
    //as we don't want our join to be used more than once on this page, we remove the filter
    remove_filter( 'posts_join', 'do_order_by_custom_date_field_join' );
    return $join;
}

At last, we change our ordering to the date, but with the STR_TO_DATE function:

function do_order_by_custom_date_field_orderby($orderby, $wp_query){
    $orderby = "STR_TO_DATE(my_order_meta.meta_value,'%d-%m-%Y') ".$wp_query->get('order');
    remove_filter('posts_orderby', 'do_order_by_custom_date_field_orderby');
    return $orderby;
}

Now, the orderby should work as intended (didn't test it tho)

Happy Coding.