How to sort posts in admin by titles with dd.mm.yyyy format?

by

I have created a Custom Post Type for Booking, new posts are inserted by visitors and the title is formatted as date on which the booking takes place. So, the post list in admin looks like this:

enter image description here

Is there a way I could order those posts in admin list by those date titles?
Generally solution would be I guess to get the titles, transform them to dates and then sort those posts. If so, how can I do it inside pre_get_posts() filter?

Maybe I could have done this better, but everything else about these functionality works great. Each post contains ACF repeater with time in steps so it is a little bit more complicated inside.

EDIT:

This is how I have filter, it correctly modifies posts_per_page just to make sure it works. I have tried recreating it as a plugin class.

public function __construct(){
    add_action( 'pre_get_posts', array($this, 'sort_admin_daily_reservations'));
    add_filter( 'posts_orderby', array($this, 'test_sorting_filter' ), 10, 2);
}


public function sort_admin_daily_reservations($query) {

    if(is_admin() && $query->is_main_query() && in_array ($query->get('post_type'), array('Booking')) )
    {
        $query->set('orderby', 'wpse_mod_title');
        $query->set('order', 'DESC');   
        //$query->set('posts_per_page', 2);
    }
    return $query;

}


public function test_sorting_filter($orderby, \WP_Query $q) {

    global $wpdb;

    $_orderby = $q->get( 'orderby' );
    $_order   = $q->get( 'order' );

    if('wpse_mod_title ' === $_orderby && in_array( $_order , [ 'ASC', 'DESC' ], true ))
        $orderby = " CONCAT(
            SUBSTRING( {$wpdb->posts}.post_title, 7, 4 ), 
            SUBSTRING( {$wpdb->posts}.post_title, 4, 2 ), 
            SUBSTRING( {$wpdb->posts}.post_title, 1, 2 ) 
        ) {$_order} ";

    return $orderby;

}

1 Answer
1

It looks like the post titles have the date format DD.MM.YYYY.

We can order the DD.MM.YYYY titles as YYYYMMDD with:

$query = new WP_Query( [ 'orderby' => 'wpse_mod_title ' ] );

that’s supported with this kind of plugin:

/**
 * Plugin Name: WPSE-274981: Sort DD.MM.YYYY Post Titles as YYYYMMDD In WP_Query 
 * Desription:  Supported by the wpse_mod_title orderby value
 */
add_filter( 'posts_orderby', function( $orderby, \WP_Query $q ) use ( &$wpdb )
{
    $_orderby = $q->get( 'orderby' );
    $_order   = $q->get( 'order' );

    if( 'wpse_mod_title' === $_orderby && in_array( $_order , [ 'ASC', 'DESC' ], true ) )
        $orderby = " CONCAT(
            SUBSTRING( {$wpdb->posts}.post_title, 7, 4 ), // Get the YYYY part
            SUBSTRING( {$wpdb->posts}.post_title, 4, 2 ), // Get the MM part
            SUBSTRING( {$wpdb->posts}.post_title, 1, 2 )  // Get the DD part
        ) {$_order} ";

    return $orderby; 
}, 10, 2 );

Note that this assumes the strict DD.MM.YYYY title format, without spaces.

We might consider trimming it in SQL to make it more flexible.

I think I’m under the influence of @bonger or @thedeadmedic to write this kind of substring ordering. 😉

But consider another approaches first, like writing the titles in a sortable way, in the first place.

Leave a Comment