Ordering posts by custom field named "date" in backend

Question

I defined a custom field named "date" and now want to order the post list in the backend by this field.

I defined the filters to make ordering work and header is clickable and shows the order arrow icon. The problem is that the post list gets ordered by publish date instead of custom field "date".

function crimes_columns($columns) {
    $columns['crimedate'] = 'Crime date';
    return $columns;
}
add_filter('manage_posts_columns', 'crimes_columns');

function crimes_show_columns($name) {
    global $post;
    switch ($name) {
        case 'crimedate':
            echo get_post_meta($post->ID, "date", true);
            break;
    }
    return $row_output;
}
add_action('manage_posts_custom_column',  'crimes_show_columns');

add_filter( 'manage_edit-sortable_columns', 'crimes_add_custom_column_make_sortable' );
function crimes_add_custom_column_make_sortable( $columns ) {
    $columns['crimedate'] = 'crimedate';
    return $columns;
}

add_action( 'pre_get_posts', 'crimes_orderby_meta' );
function crimes_orderby_meta( $query ) {
    if(!is_admin())
        return;
 
    $orderby = $query->get( 'orderby');
 
    if( 'crimedate' == $orderby ) {
        $query->set('meta_key','date');
        $query->set('meta_type', 'DATE');
        $query->set('orderby','meta_value_date');
    }
}

Do you think the problem is the name of the field, do I have to rename the field to make it work? Or is there something wrong with my code?

Answer 1

The main issue in your code is this part: $query->set('orderby','meta_value_date'); whereby you should use meta_value and not meta_value_date. (There is meta_value_num, but that's not suitable for used with sorting by dates.)

Then make sure the meta_type is set to DATE (which yes, you already done it correctly), and use the right date format — 10132020 is really not a good date and MySQL/MariaDB also won't be able to cast such values to a valid date — WordPress/WP_Query instructs MySQL/MariaDB to cast the value as a date when the meta type is set to date.

And in reply to (your comment): "But is there any solution without changing the format and ordering the result query with the chosen format mmddYYYY?", sorry, but I don't think there's an easy way to do so.

And the better practice is use the proper format when saving the metadata and not when retrieving it. For example, you could use the ISO 8601 date format which is also used by current_time( 'mysql' ) in WordPress: Y-m-d — but that's with PHP; with the jQuery Datepicker widget, you would use yy-mm-dd or $.datepicker.ISO_8601 which then gives a date like 2020-10-14 for October 14th 2020.

So correct the date format and use meta_value with the orderby parameter, and the sorting would work as expected.

Other issues in your code

1. In crimes_show_columns(), if you need to access the full post data, use get_post() and not the global call. So for example, you can rewrite the function to:

   function crimes_show_columns( $name, $post_id ) {
   //  $post = get_post( $post_id ); // like this
   //  global $post;                 // not this
   
       switch ( $name ) {
           case 'crimedate':
               echo get_post_meta( $post_id, 'date', true );
               break;
       }
   
       // action hooks do not need anything be returned back
   //  return $row_output; // moreover, $row_output is not defined anywhere..
   }
   add_action( 'manage_posts_custom_column',  'crimes_show_columns', 10, 2 );
   

2. The correct hook (used for adding a custom sortable column name) is manage_edit-post_sortable_columns and not manage_edit-sortable_columns because on the edit.php?post_type=post admin screen, the screen ID is edit-post; i.e. <name of the PHP file without .php>-<post type>.