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?

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?

• custom-field
• post-meta
• meta-query
• order
• date

• That meta_value_date should be meta_value, but why do you need another date field? – Sally CJ Commented Oct 13, 2020 at 13:22
• I also tried with $query->set('orderby','meta_value'); - and without meta_type, but without success. To keep it simple I didn't tell that the custom field "date" is defined in a CPT. – geraldo Commented Oct 13, 2020 at 15:42
• I don't know why using meta_value doesn't work for you, but you need to specify the correct meta type for the sorting to works as expected. So what is the format of that meta value, is it like 20201013? – Sally CJ Commented Oct 14, 2020 at 3:46
• The format is mmddYYYY, e.g. 10132020. So do I have to convert it into a MySQL timestamp with format Y-m-d h:i:s? How could I do this conversion? – geraldo Commented Oct 14, 2020 at 8:14
• Well, if you use a plugin for creating the custom field UI, then you should check the plugin site for documentation on the available date formats. But seems like you can try with YYYY-mm-dd.. – Sally CJ Commented Oct 14, 2020 at 9:28

1 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>.