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?
Share Improve this question asked Oct 13, 2020 at 13:09 geraldogeraldo 1457 bronze badges 6 | Show 1 more comment1 Answer
Reset to default 3The 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
In
crimes_show_columns()
, if you need to access the full post data, useget_post()
and not theglobal
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 );
The correct hook (used for adding a custom sortable column name) is
manage_edit-post_sortable_columns
and notmanage_edit-sortable_columns
because on theedit.php?post_type=post
admin screen, the screen ID isedit-post
; i.e.<name of the PHP file without .php>-<post type>
.
meta_value_date
should bemeta_value
, but why do you need another date field? – Sally CJ Commented Oct 13, 2020 at 13:22$query->set('orderby','meta_value');
- and withoutmeta_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:42meta_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 like20201013
? – Sally CJ Commented Oct 14, 2020 at 3:46mmddYYYY
, e.g.10132020
. So do I have to convert it into a MySQL timestamp with formatY-m-d h:i:s
? How could I do this conversion? – geraldo Commented Oct 14, 2020 at 8:14YYYY-mm-dd
.. – Sally CJ Commented Oct 14, 2020 at 9:28