Ignoring initial articles (like ‘a’, ‘an’ or ‘the’) when sorting queries?

by

I’m currently trying to output a list of music titles and would like to have the sorting ignore (but still display) the initial article of the title.

For example if I had a list of bands it will be displayed alphabetically in WordPress like this:

  • Black Sabbath
  • Led Zeppelin
  • Pink Floyd
  • The Beatles
  • The Kinks
  • The Rolling Stones
  • Thin Lizzy

Instead I would like to have it displayed alphabetically while ignoring the initial article ‘The’, like this:

  • The Beatles
  • Black Sabbath
  • The Kinks
  • Led Zeppelin
  • Pink Floyd
  • The Rolling Stones
  • Thin Lizzy

I came across a solution in a blog entry from last year, that suggests the following code in functions.php:

function wpcf_create_temp_column($fields) {
  global $wpdb;
  $matches="The";
  $has_the = " CASE 
      WHEN $wpdb->posts.post_title regexp( '^($matches)[[:space:]]' )
        THEN trim(substr($wpdb->posts.post_title from 4)) 
      ELSE $wpdb->posts.post_title 
        END AS title2";
  if ($has_the) {
    $fields .= ( preg_match( '/^(\s+)?,/', $has_the ) ) ? $has_the : ", $has_the";
  }
  return $fields;
}

function wpcf_sort_by_temp_column ($orderby) {
  $custom_orderby = " UPPER(title2) ASC";
  if ($custom_orderby) {
    $orderby = $custom_orderby;
  }
  return $orderby;
}

and then wrapping the query with add_filter before and remove_filter after.

I’ve tried this, but I keep getting the following error on my site:

WordPress database error: [Unknown column ‘title2’ in ‘order clause’]

SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type=”release” AND (wp_posts.post_status=”publish” OR
wp_posts.post_status=”private”) ORDER BY UPPER(title2) ASC

I’m not gonna lie, I’m pretty new to the php part of WordPress, so I’m unsure as to why I’m getting this error. I can see it’s got something to do with the ‘title2’ column, but it was my understanding that the first function should take care of that. Also, if there’s a smarter way to do this I’m all ears. I’ve been googling around and searching this site, but I haven’t really found a lot of solutions.

My code using the filters looks like this if it’s any help:

<?php 
    $args_post = array('post_type' => 'release', 'orderby' => 'title', 'order' => 'ASC', 'posts_per_page' => -1, );

    add_filter('post_fields', 'wpcf_create_temp_column'); /* remove initial 'The' from post titles */
    add_filter('posts_orderby', 'wpcf_sort_by_temp_column');

    $loop = new WP_Query($args_post);

    remove_filter('post_fields', 'wpcf_create_temp_column');
    remove_filter('posts_orderby', 'wpcf_sort_by_temp_column');

        while ($loop->have_posts() ) : $loop->the_post();
?>

4

The Problem

I think there’s a typo in there:

The name of the filter is posts_fields not post_fields.

That could explain why the title2 field is unknown, because it’s definition isn’t added to the generated SQL string.

Alternative – Single filter

We can rewrite it to use only a single filter:

add_filter( 'posts_orderby', function( $orderby, \WP_Query $q )
{
    // Do nothing
    if( '_custom' !== $q->get( 'orderby' ) )
        return $orderby;

    global $wpdb;

    $matches="The";   // REGEXP is not case sensitive here

    // Custom ordering (SQL)
    return sprintf( 
        " 
        CASE 
            WHEN {$wpdb->posts}.post_title REGEXP( '^($matches)[[:space:]]+' )
                THEN TRIM( SUBSTR( {$wpdb->posts}.post_title FROM %d )) 
            ELSE {$wpdb->posts}.post_title 
        END %s
        ",
        strlen( $matches ) + 1,
        'ASC' === strtoupper( $q->get( 'order' ) ) ? 'ASC' : 'DESC'     
    );

}, 10, 2 );

where you can now activate the custom ordering with the _custom orderby parameter:

$args_post = array
    'post_type'      => 'release', 
    'orderby'        => '_custom',    // Activate the custom ordering 
    'order'          => 'ASC', 
    'posts_per_page' => -1, 
);

$loop = new WP_Query($args_post);

while ($loop->have_posts() ) : $loop->the_post();

Alternative – Recursive TRIM()

Let’s implement the recursive idea by Pascal Birchler, commented here:

add_filter( 'posts_orderby', function( $orderby, \WP_Query $q )
{
    if( '_custom' !== $q->get( 'orderby' ) )
        return $orderby;

    global $wpdb;

    // Adjust this to your needs:
    $matches = [ 'the ', 'an ', 'a ' ];

    return sprintf( 
        " %s %s ",
        wpse_sql( $matches, " LOWER( {$wpdb->posts}.post_title) " ),
        'ASC' === strtoupper( $q->get( 'order' ) ) ? 'ASC' : 'DESC'     
    );

}, 10, 2 );

where we can for example construct the recursive function as:

function wpse_sql( &$matches, $sql )
{
    if( empty( $matches ) || ! is_array( $matches ) )
        return $sql;

    $sql = sprintf( " TRIM( LEADING '%s' FROM ( %s ) ) ", $matches[0], $sql );
    array_shift( $matches );    
    return wpse_sql( $matches, $sql );
}

This means that 

$matches = [ 'the ', 'an ', 'a ' ];
echo wpse_sql( $matches, " LOWER( {$wpdb->posts}.post_title) " );

will generate:

TRIM( LEADING 'a ' FROM ( 
    TRIM( LEADING 'an ' FROM ( 
        TRIM( LEADING 'the ' FROM ( 
            LOWER( wp_posts.post_title) 
        ) )
    ) )
) )

Alternative – MariaDB

In general I like to use MariaDB instead of MySQL. Then it’s much easier because MariaDB 10.0.5 supports REGEXP_REPLACE:

/**
 * Ignore (the,an,a) in post title ordering
 *
 * @uses MariaDB 10.0.5+
 */
add_filter( 'posts_orderby', function( $orderby, \WP_Query $q )
{
    if( '_custom' !== $q->get( 'orderby' ) )
        return $orderby;

    global $wpdb;
    return sprintf( 
        " REGEXP_REPLACE( {$wpdb->posts}.post_title, '^(the|a|an)[[:space:]]+', '' ) %s",
        'ASC' === strtoupper( $q->get( 'order' ) ) ? 'ASC' : 'DESC'     
    );
}, 10, 2 );

Leave a Comment