WordPress Orderby Last Word In Title

Question

I have a custom post type of "staff". I need to get this to display the staff alphabetically by last name on the page. I know a work around would be to use custom meta boxes and break up first and last names into two fields but I'm trying to avoid that as that seems very hackish and not as clean as just using the title field.

I have a shortcode working that will show the custom post type with the staff "type" taxonomy attribute requested. Here is an example:

[staff type="local"] 

I can't just assume that it's the second word in the title that I want since some staff are couples and will have both of their names listed like: "Bob and Cindy Smith".

Here is the shortcode that I have so far.

function get_staff($atts) {
    extract( shortcode_atts( array( 'type' => 'international' ), $atts ) );
    $loop = new WP_Query(
        array (
            'post_type' => 'staff',
            'orderby' => 'title',
            'staff-type' => $type
        )
    );

if ($loop->have_posts()) {
    $output = '<div class="staff">';

    while($loop->have_posts()){
        $loop->the_post();
        $meta = get_post_meta(get_the_id());

        $output .= '
            <div class="staff" style="float: left; display: block; border: 1px solid #CCC; margin: 10px; padding: 12px; background-color: #eee;">
                <a href="' . get_permalink() . '">
                    ' . get_the_post_thumbnail($post->ID, 'thumbnail') . '<br />
                ' . get_the_title()  . '</a><br />
                ' . get_the_excerpt() . '
            </div>
        ';
    }
    $output .= "</div>";
} else {
    $output = 'No Staff Meet This Criteria Yet.';
}

return $output;
};

add_shortcode('staff', 'get_staff'); 

This works great but is missing the alphabetizing by last name. Thanks for any help you can offer. This is my first real attempt at an elaborate shortcode so please be a little specific with your answer.

Camillacamille

Answer 1

Try this. First add the following orderby filter in functions.php

function posts_orderby_lastname ($orderby_statement) 
{
  $orderby_statement = "RIGHT(post_title, LOCATE(' ', REVERSE(post_title)) - 1) DESC";
    return $orderby_statement;
}

and then use it in your query like so

add_filter( 'posts_orderby' , 'posts_orderby_lastname' );
    $loop = new WP_Query(
        array (
            'post_type' => 'staff',
            'staff-type' => $type
        )
    );

and after the loop remove the filter

remove_filter( 'posts_orderby' , 'posts_orderby_lastname' );

Neilneila

It works perfectly. Is there a way to put the first function someplace else other than the functions.php file? I'm making my custom post types plugins for portability and in the future if my client changes their theme this would break. Thanks for your help. – Camillacamille

I found this article about plugin development which shows an example of adding a action plugintutorials.blogspot.com/2011/04/… - I guess you can just add it in your plugin file. – Neilneila

Answer 2

The answer posted above is great. But there is an issue if the person in the list goes by only a single name. For example, Kanye would appear at the top of the results and not get sorted with the Ks.

I've updated the code and this will alphabetize those with just a single name as well as those with a first and last name. So Kanye will now appear with the Ks.

function posts_orderby_lastname ($orderby_statement) 
{
    $orderby_statement = "RIGHT(post_title, LOCATE(' ', CONCAT(REVERSE(post_title), ' ')) - 1) ASC";
    return $orderby_statement;
}

Cowpoke

Answer 3

This has worked for me:

function my_movie_posts_orderby( $orderby, $query ) {
    if ( $query->is_main_query() && $query->is_post_type_archive( 'my-movie' ) ) {
        $orderby = "SUBSTRING_INDEX(post_title, ' ', -1)";
    }
    return $orderby;
}
add_filter( 'posts_orderby', 'my_movie_posts_orderby', 10, 2 );

Alodee