0

I defined a custom field named crimedate using standard date format Y-m-d. I defined the filters to make ordering by this date work in the post list of the backend, which works fine.

Now I need the possibility to show posts ordered by anniversary, using only day and month of the crimedate, but I don't know how to do that:

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

function crimes_show_columns($name, $post_id) {
    switch ($name) {
        case 'crimedate':
            // show whole date
            echo get_post_meta($post_id, "crimedate", true);
            break;
        case 'crimeanniversary':
            // show only anniversary in format dd/mm
            $date = get_post_meta($post_id, "crimedate", false);
            echo substr($date,6)."/".substr($date,4,2);
            break;
    }
    return $row_output;
}
add_action('manage_posts_custom_column',  'crimes_show_columns');

add_filter( 'manage_edit-post_sortable_columns', 'crimes_add_custom_column_make_sortable' );
function crimes_add_custom_column_make_sortable( $columns ) {
    $columns['crimedate'] = 'crimedate';
    $columns['crimeanniversary'] = 'Crime anniversary';
    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','crimedate');
        $query->set('orderby','meta_value');
    }
    else if( 'crimeanniversary' == $orderby ) {
        // ¿¿¿ what to do here ???
    }
}

Is it possible to sort dates only by day and month ignoring the year in hook pre_get_posts? Or do I have to save days and month in two separte fields to make that work?

1 Answer 1

1

No, it's not possible by using pre_get_posts alone. But you don't have to use separate fields for the date.

WP_Query, although doesn't provide a simple query arg that would sort the posts by month and day only, you can use the posts_orderby filter hook to modify the ORDER BY clause (i.e. ORDER BY <the part here>) for sorting the posts.

Working Example

  1. In the pre_get_posts hook, set the meta_key to crimedate:

    // In crimes_orderby_meta():
    
    elseif ( 'crimeanniversary' == $orderby ) {
        $query->set( 'meta_key', 'crimedate' );
    }
    
  2. Then on the posts_orderby hook, you can use the STR_TO_DATE() function in MySQL to sort the posts by the month and day of the meta value:

    Note that I purposely used a static leap year, which means 29/02 (Feb 29th) always comes after 28/02 (Feb 28th), when the sort order is ASC.

    add_filter( 'posts_orderby', 'posts_orderby_crimeanniversary', 10, 2 );
    function posts_orderby_crimeanniversary( $orderby, $query ) {
        if ( is_admin() && 'crimeanniversary' === $query->get( 'orderby' ) ) {
            global $wpdb;
    
            $order = ( 'ASC' === strtoupper( $query->get( 'order' ) ) ) ? 'ASC': 'DESC';
    
            // This means, we still sort by the date, but we "recreate" the date using
            // the year 2020 (or any leap year), and month and day from the meta value.
            $orderby = "STR_TO_DATE( CONCAT( '2020-',
                MONTH( {$wpdb->postmeta}.meta_value ), '-',
                DAY( {$wpdb->postmeta}.meta_value
            ) ), '%Y-%m-%d' ) $order";
        }
    
        return $orderby;
    }
    

I hope that helps, and please correct these issues in your code:

  • In crimes_add_custom_column_make_sortable():

    $columns['crimeanniversary'] = 'crimeanniversary';  // like this
    $columns['crimeanniversary'] = 'Crime anniversary'; // not this
    
  • In crimes_show_columns():

    get_post_meta($post_id, "crimedate", true)  // like this
    get_post_meta($post_id, "crimedate", false) // not this
    

    And just remove that line with $row_output — which is not defined.

  • And don't forget the fourth parameter here:

    add_action('manage_posts_custom_column', 'crimes_show_columns', 10, 2); // like this
    add_action('manage_posts_custom_column', 'crimes_show_columns');        // not this!
    
1
  • 1
    That solution worked great. Thanks also for all your code hints!
    – geraldo
    Oct 19, 2020 at 15:43

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.