0

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' );

1 Answer 1

0

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.

5
  • Thanks so much kuchenundkakao. Unfortunately this is not working, when I click on the sort icon the following error is being returned.
    – Connor
    May 7, 2019 at 9:31
  • WordPress database error: [Unknown column 'wp_postmeta.post_id' in 'on clause']SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta my_order_meta ON ((wp_posts.ID = wp_postmeta.post_id) AND (wp_postmeta.meta_key = 'online_start')) WHERE 1=1 AND wp_posts.post_type = 'courses' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') ORDER BY STR_TO_DATE(my_order_meta.meta_value,'%d-%m-%Y') ASC LIMIT 0, 20
    – Connor
    May 7, 2019 at 9:31
  • I should also mention that I'm using the advanced custom fields plugin. The dates however are being used in a regular text custom field. Would that have any bearing on your provided functions?
    – Connor
    May 7, 2019 at 9:32
  • Sorry, i corrected the do_order_by_custom_date_field_join function. Should work now ;) May 7, 2019 at 10:38
  • Thanks kuchenundkakao, this now works.
    – Connor
    May 10, 2019 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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