0

I have a page where I show the teams of a football club. I order these with 'order' => 'ASC'. But the problem is that the teams order like I want it to.

It orders like this: Team E1, Team E10, Team E11, Team E2

It must be like this: Team E1, Team E2 ..... Team E10, Team E11

What is the best solution to order this the right way?

  • 1
    If you don't have anything else that would uniquely identify the order, you need to create it, because E1, E10 is alphabetical order. I would look into storing meta information that indicates your display order and then use a meta query when calling WP_Query. – Andrew Bartel Jan 12 '15 at 20:59
5

Old answer (which no longer works):

A sneaky way to do it using MySQL, assuming the number's always at the end, is to reverse the title, convert it to a number (+0), and then reverse again and convert it to a number:

function wpse174646_posts_orderby( $orderby, $query ) {
    if ( $query->get( 'orderby' ) != 'title_number' ) return $orderby;
    global $wpdb;
    return 'REVERSE(REVERSE(TRIM(' . $wpdb->posts . '.post_title))+0)+0 ' . $query->get( 'order' );
}
$args = array( 'orderby' => 'title_number', 'order' => 'ASC' );
add_filter( 'posts_orderby', 'wpse174646_posts_orderby', 10, 2 );
$query = new WP_Query( $args );
remove_filter( 'posts_orderby', 'wpse174646_posts_orderby', 10 );

New answer:

A robust (hopefully) way to do it is to use the answer I posted on stackexchange (https://stackoverflow.com/a/28808798/664741), a simplified non-udf version of the best response there of @plaix/Richard Toth/Luke Hoggett, which pads out the (first encountered) number with zeros:

add_filter( 'posts_clauses', function ( $pieces, $query ) {
    if ( $query->get( 'orderby' ) != 'title_number' ) return $pieces;
    global $wpdb;
    $field = $wpdb->posts . '.post_title';
    $pieces[ 'fields' ] .= $wpdb->prepare(
        ', LEAST(' . implode( ',', array_fill( 0, 10, 'IFNULL(NULLIF(LOCATE(%s, ' . $field . '), 0), ~0)' ) )
        . ') AS first_int',
        '0', '1', '2', '3', '4', '5', '6', '7', '8', '9'
    );
    $pieces[ 'orderby' ] = $wpdb->prepare(
        'IF(first_int = ~0, ' . $field . ', CONCAT('
            . 'SUBSTR(' . $field . ', 1, first_int - 1),'
            . 'LPAD(CAST(SUBSTR(' . $field . ', first_int) AS UNSIGNED), LENGTH(~0), %s),'
            . 'SUBSTR(' . $field . ', first_int + LENGTH(CAST(SUBSTR(' . $field . ', first_int) AS UNSIGNED)))'
        . ')) ' . $query->get( 'order' )
        , 0
    );
    return $pieces;
}, 10, 2 );
$args = array( 'orderby' => 'title_number', 'order' => 'ASC' );
$query = new WP_Query( $args );
  • I can't' -1 this but it was always dodgy and has stopped working in MySQL 5.6.25 (at least) ... eg "10" gets transformed (reasonably enough) into just "1" after reverse plus addition (before it remained "01") ... will post a better answer. – bonger Jul 30 '15 at 7:18
  • I've hunted and hunted for this... more people should be aware of it :) – Sam Holguin Dec 3 '18 at 15:15
0

PHP has a sort_natural flag on its sort() function (MySQL doesn't). So if you built them into an array before displaying them, you could sort it that way.

Something like:

$teams = array(
    'Team E1',
    'Team E10',
    'Team E2',
    'Team E11',
);

sort($teams, SORT_NATURAL);

print_r($teams);

Will output:

Array
(
    [0] => Team E1
    [1] => Team E2
    [2] => Team E10
    [3] => Team E11
)

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.