2

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
  • Are those dates string? If so, you can't sort it by date. 02.12.2099 comes before 20.01.1990, and after 01.11.1980. But if this is the real post's date, then you can sort them by date.
    – Jack Johansson
    Jul 28 '17 at 9:46
3

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.

14
  • Hmm I don't seem to understand what does this code do :) by the way, could you please look at my comment on the question?
    – Jack Johansson
    Jul 28 '17 at 9:55
  • CurrentIy the title looks like dd.mm.yyyy., that's not easy to date sort, but if we reverse it to .yyyy.mm.dd in the SQL query, then it's easier to sort. (skipping date conversions here) @JackJohansson
    – birgire
    Jul 28 '17 at 9:58
  • 1
    hehe, you mean before 0000:00:00, I guess one could consider prefixing or postfixing the titles to fix the ordering or mark them in other ways ;-)
    – birgire
    Jul 28 '17 at 10:10
  • 1
    I updated the answer, as I wasn't thinking clearly regarding reversing the titles ;-)
    – birgire
    Jul 28 '17 at 11:37
  • 1
    You are right, I have played with various parameters. PHP substr starts from 0, MySQL substring starts from 1. But I am so stupid, you have an empty space after 'wpse_mod_title ' in IF clause so that is why the code didn't evaluated :D OMG Anyway, thank you very much, it works great now :) Jul 28 '17 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.