13

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();
?>
  • 1
    an alternate solution could be to store the title you want to sort by as post meta data and order on that field instead of title. – Milo Feb 7 '16 at 1:43
  • I'm a little unsure as to how to go on with that. Wouldn't storing that in a new column result in an error similar to the one I'm currently getting? – rpbtz Feb 7 '16 at 2:29
  • 1
    you wouldn't use any of that code, you can query and sort on post meta with meta query parameters. – Milo Feb 7 '16 at 2:58
8

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 );
  • I think this should solve the issue better than my solution – Pieter Goosen Feb 7 '16 at 9:02
  • You were absolutely correct - changing post_fields to posts_fields fixed the issue and it's now sorting exactly the way I want it. Thank you! I feel a little stupid now seeing as that was the issue. That's what I get for coding at 4AM, guess. I'll look into the single filter solution as well. Seems like a really good idea. Thanks again. – rpbtz Feb 7 '16 at 14:46
  • I'll mark this as the correct answer as this is the one that's the most closely related to my initial questions, although as far as I can tell the other answers are valid solutions as well. – rpbtz Feb 7 '16 at 14:48
  • The single filter alternative worked like a charm as well. I can now keep the filter code in functions.php and call it via orderby when I need it. Great solution - thank you :-) – rpbtz Feb 7 '16 at 15:12
  • 1
    Glad to hear it worked for you - I added the recursive method. @rpbtz – birgire Feb 7 '16 at 16:18
12

An easier way may be to go through and change the permalink slug on those posts that need it (under the title on the post writing screen) and then just use that for ordering instead of the title.

ie. use post_name not post_title for sorting...

This would also mean that your permalink may be different if you use %postname% in your permalink structure, which could be an added bonus.

eg. gives http://example.com/rolling-stones/ not http://example.com/the-rolling-stones/

EDIT: code to update the existing slugs, removing the unwanted prefixes from post_name column...

global $wpdb;
$posttype = 'release';
$stripprefixes = array('a-','an-','the-');

$results = $wpdb->get_results("SELECT ID, post_name FROM ".$wpdb->prefix."posts" WHERE post_type = '".$posttype."' AND post_status = 'publish');
if (count($results) > 0) {
    foreach ($results as $result) {
        $postid = $result->ID;
        $postslug = $result->post_name;
        foreach ($stripprefixes as $stripprefix) {
            $checkprefix = strtolower(substr($postslug,0,strlen($stripprefix));
            if ($checkprefix == $stripprefix) {
                $newslug = substr($postslug,strlen($stripprefix),strlen($postslug));
                // echo $newslug; // debug point
                $query = $wpdb->prepare("UPDATE ".$wpdb->prefix."posts SET post_name = '%s' WHERE ID = '%d'", $newslug, $postid);
                $wpdb->query($query);
            }
        }
    }
}
  • Great solution - very simple and efficient for sorting. – BillK Feb 7 '16 at 7:36
  • The typo solution from @birgire worked like a charm, but this seems like a decent alternative. I'll go with the other one for now as there's quite a few queried posts with an initial article and changing all the permalink slugs might take some time. I like the simplicity of this solution, though. Thanks :-) – rpbtz Feb 7 '16 at 14:57
  • 1
    since you liked, added some code that should change all the slugs if wanted/needed. :-) – majick Feb 7 '16 at 15:22
6

EDIT

I have improved the code a bit. All code block are updated accordingly. Just a note though before jumping into the updates in the ORIGINAL ANSWER, I have set up the code to work with the following

  • Custom post type -> release

  • Custom taxonomy -> game

Make sure to set this according to your needs

ORIGINAL ANSWER

In addition to the other answers and the typo pointed out by @birgire, here is another approach.

First, we will set the title as a hidden custom field, but we will first remove the words like the that we would want to exclude. Before we do that, we need to first create a helper function in order to remove the banned words from the term names and post titles

/**
 * Function get_name_banned_removed()
 *
 * A helper function to handle removing banned words
 * 
 * @param string $tring  String to remove banned words from
 * @param array  $banned Array of banned words to remove
 * @return string $string
 */
function get_name_banned_removed( $string = '', $banned = [] )
{
    // Make sure we have a $string to handle
    if ( !$string )
        return $string;

    // Sanitize the string
    $string = filter_var( $string, FILTER_SANITIZE_STRING );

    // Make sure we have an array of banned words
    if (    !$banned
         || !is_array( $banned )
    )
        return $string; 

    // Make sure that all banned words is lowercase
    $banned = array_map( 'strtolower', $banned );

    // Trim the string and explode into an array, remove banned words and implode
    $text          = trim( $string );
    $text          = strtolower( $text );
    $text_exploded = explode( ' ', $text );

    if ( in_array( $text_exploded[0], $banned ) )
        unset( $text_exploded[0] );

    $text_as_string = implode( ' ', $text_exploded );

    return $string = $text_as_string;
}

Now that we have that covered, lets look at the piece of code to set our custom field. You must remove this code completely as soon as you have loaded any page once. If you have a huge site with a ton of posts, you can set posts_per_page to something to 100 and run the scripts a couple of times until all posts have the custom field have been set to all posts

add_action( 'wp', function ()
{
    add_filter( 'posts_fields', function ( $fields, \WP_Query $q ) 
    {
        global $wpdb;

        remove_filter( current_filter(), __FUNCTION__ );

        // Only target a query where the new custom_query parameter is set with a value of custom_meta_1
        if ( 'custom_meta_1' === $q->get( 'custom_query' ) ) {
            // Only get the ID and post title fields to reduce server load
            $fields = "$wpdb->posts.ID, $wpdb->posts.post_title";
        }

        return $fields;
    }, 10, 2);

    $args = [
        'post_type'        => 'release',       // Set according to needs
        'posts_per_page'   => -1,              // Set to execute smaller chucks per page load if necessary
        'suppress_filters' => false,           // Allow the posts_fields filter
        'custom_query'     => 'custom_meta_1', // New parameter to allow that our filter only target this query
        'meta_query'       => [
            [
                'key'      => '_custom_sort_post_title', // Make it a hidden custom field
                'compare'  => 'NOT EXISTS'
            ]
        ]
    ];
    $q = get_posts( $args );

    // Make sure we have posts before we continue, if not, bail
    if ( !$q ) 
        return;

    foreach ( $q as $p ) {
        $new_post_title = strtolower( $p->post_title );

        if ( function_exists( 'get_name_banned_removed' ) )
            $new_post_title = get_name_banned_removed( $new_post_title, ['the'] );

        // Set our custom field value
        add_post_meta( 
            $p->ID,                    // Post ID
            '_custom_sort_post_title', // Custom field name
            $new_post_title            // Custom field value
        );  
    } //endforeach $q
});

Now that the custom fields are set to all posts and the code above is removed, we need to make sure that we set the this custom field to all new posts or whenever we update the post title. For this we will use the transition_post_status hook. The following code can go into a plugin (which I recommend) or in your functions.php

add_action( 'transition_post_status', function ( $new_status, $old_status, $post )
{
    // Make sure we only run this for the release post type
    if ( 'release' !== $post->post_type )
        return;

    $text = strtolower( $post->post_title );   

    if ( function_exists( 'get_name_banned_removed' ) )
        $text = get_name_banned_removed( $text, ['the'] );

    // Set our custom field value
    update_post_meta( 
        $post->ID,                 // Post ID
        '_custom_sort_post_title', // Custom field name
        $text                      // Custom field value
    );
}, 10, 3 );

QUERYING YOUR POSTS

You can run your queries as normal without any custom filters. You can query and sort your posts as follow

$args_post = [
    'post_type'      => 'release', 
    'orderby'        => 'meta_value', 
    'meta_key'       => '_custom_sort_post_title',
    'order'          => 'ASC', 
    'posts_per_page' => -1, 
];
$loop = new WP_Query( $args );
  • I like this approach (maybe it's enough to remove the banned word from the beginning of the title) – birgire Feb 7 '16 at 9:11
  • @birgire I only went with this because my SQL knowledge as is poor as a church mouse, hahahaha. Thanks for the typo – Pieter Goosen Feb 7 '16 at 9:20
  • 1
    The witty mouse can be much more agile than the hardcoded SQL elephant ;-) – birgire Feb 7 '16 at 10:09
0

birgire's answers works good when ordering only by this field. I made some modifications to make it work when ordering by multiple fields (I am not sure that it works correctly when title ordering is the primary one):

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

global $wpdb;

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

// Custom ordering (SQL)
if (is_array($q->get( 'orderby' ))) {
    return sprintf( 
        " $orderby, 
        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( 'orderby' )['_custom'] ) ? 'ASC' : 'DESC'     
    );
}
else {
    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 );

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.