Ordering posts by anniversary using only day and month

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 29^th) always comes after 28/02 (Feb 28^th), 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!
  

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0