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 );