Order category posts by last word of custom field

Question

I'm trying to order a category page by the last word of a custom field that all of its posts have. The custom field represents a name, so the last word would be the last name. In the end, the posts of the category page should be listed in alpha order by last name.

How do I achieve this?

Threads I'm looking at:

• How to order posts by meta value?, which covers everything except how to order by the last word of the meta key.
• Order by the results of a function with WP_query, but I'm not using a WP_Query since I'm just changing the order of a category.php page (so, correct me if I'm wrong, but WP_Query shouldn't be necessary in that case, correct?).

My code:

function sort_research_cycles_by_last_name($query) {
    $isResearchCategory = current_cat_is_sub_of(get_category_by_slug('research-cycles')->term_id) || current_cat_is_sub_of(get_category_by_slug('profile-expert-fr')->term_id);
    if ($isResearchCategory && $query->is_main_query()) {
        /*
         * Uncommenting the following two lines and setting orderby to $orderby
         * results in an odd order that I can't make sense of.
         */
        // global $wpdb;
        // $orderby = "SUBSTRING_INDEX($wpdb->postmeta.meta_key, ' ', -1)";
        
        /*
         * This currently only orders posts by alpha order of the first word
         */
        $query->set('meta_key','wpcf-name-of-researcher');
        $query->set('orderby','meta_value');
        $query->set('order','ASC');
    }
}
add_action('pre_get_posts','sort_research_cycles_by_last_name');

Answer 1

but I'm not using a WP_Query since I'm just changing the order of a category.php page (so, correct me if I'm wrong, but WP_Query shouldn't be necessary in that case, correct?).

Yes, you are. The whole point of the pre_get_posts action is to give you access to the query object before it goes to the database to change the parameters. That $query variable is a WP_Query object, and your code could be type-hinted like this:

function sort_research_cycles_by_last_name( \WP_Query $query) {

In fact the answer linked to there is the closest I've seen to what needs doing in the SQL section written below.

which covers everything except how to order by the last word of the meta key.

That's because this is not possible using WP_Query out of the box. You will need to extract the last name into a separate field, or re-order the name so that the last name appears first, e.g. Nowell Tom instead of Tom Nowell.

You can query post meta values based on wether they exist, their type, their value (full value, not sub-sections), you can even query for the existence of sub-strings of their value e.g. find all meta that contains XYZ. You can't order by sub-sections though, whatever comparison you make applies to the entire value.

The official docs outline these as the parameters:

meta_compare (string) – Operator to test the ‘meta_value‘. Possible values are ‘=’, ‘!=’, ‘>’, ‘>=’, ‘<‘, ‘<=’, ‘LIKE’, ‘NOT LIKE’, ‘IN’, ‘NOT IN’, ‘BETWEEN’, ‘NOT BETWEEN’, ‘NOT EXISTS’, ‘REGEXP’, ‘NOT REGEXP’ or ‘RLIKE’. Default value is ‘=’.

from https://developer.wordpress.org/reference/classes/wp_query/#custom-field-post-meta-parameters.

REGEXP is the closest to what you want but that only gets you as far as "does it end with a word that comes after a space yes or no?". As far as sorting goes, this would put all the people who fit that at the end, or at the start, but no alphabetic sorting within those 2 groups, just a haves/have nots. This is useful for filtering, but not sorting.

Raw SQL, the Unreliable Ultra Hard option

It might be possible with raw SQL, but the results will be highly unreliable, and the SQL would be broken by other plugins. As I mentioned in my comment, not all people have surnames, not all surnames are a single word, some people have titles after their surname or other indicators. Double barrel surnames or surnames with spaces in particular will be problematic, and are more common than people realise, e.g. de Sousa.

If you chose to go down this route, it would be much more difficult than using a separate meta key. The query to extract the last word would be similar to this in its most generic form as a SELECT:

SELECT SUBSTRING_INDEX(yourColumnName,’ ‘,-1) as anyVariableName from yourTableName;

You would need to figure out the full list of SQL filters, then build a check to identify only your query that needs this sorting, then, parse and rebuild the query so that it rewrote the sorting section.

You might need to create an additional query to do this, and this query will be slow and expensive. Expect a major performance hit. Also expect issues with other plugins that also try to modify the posts SQL query.

You will also need to figure out how to handle names that have trailing spaces, as this will result in an empty last field when used with your heuristic

This solution is the most time consuming, it would be easier and faster to manually update each entry by hand.

Using a Last name Meta

The real solution is to use first_name and last_name fields, combined with a display name field. This way you know both first and last names but can also account for decoratives, signifiers, postfixes, titles, middle names, etc. For example my own name Tom J Nowell does not fit into the first/last name pattern. This is also what WordPress itself does.

If necessary, you can solve this by doing a WP_Query for all users that do not have the last_name meta key, then grabbing and saving the first/last names as separate keys. Do this in batches of 50 to avoid timeouts. When all posts are processed then it will naturally find no new posts to process and the job is done. Update your site elsewhere to let users update these values in the existing forms, and the job is done.

As a bonus, having first/last names allows you to give users the option to sort using both. This is a common feature in contact applications, as there's significant cultural and regional variation. It also allows you to do sub-sorting of people who have the same last name.