Order post type navigation by name

On many client projects, there is a need to create custom post types. I usually create them using the wp scaffold post-type command from the WP-CLI. This will create the necessary PHP functions with the most common definitions and a large array where you can set nice labels. But in the most basic way, registering a post type could looks like this:

function optn_register_post_type() {
	register_post_type(
		'sponsor',
		array(
			'label'       => __( 'Sponsor', 'optn' ),
			'public'      => true,
			'has_archive' => true,
			'show_ui'     => true,
		)
	);
}
add_action( 'init', 'optn_register_post_type' );

Setting the has_archive flag to true will allow you to have an archive page for that post type. After you have created the some entries and visit that page, you will find them ordered in descending order by their publish date, just like posts:

Order post types by name

Now for this sponsor post type we don’t want to sort them by publish date, but by name. How do we achieve that? Well, that’s fairly simple. You simply have to use the pre_get_posts hook, that could look like this:

function optn_pre_get_posts_order_by_name( $query ) {
	if ( is_admin() || ! $query->is_main_query() ) {
		return;
	}

	if ( is_post_type_archive( 'sponsor' ) ) {
		$query->set( 'orderby', 'name' );
		$query->set( 'order', 'ASC' );
	}
}
add_action( 'pre_get_posts', 'optn_pre_get_posts_order_by_name' );

This will set the archive query to sort by the name of a post type (which is stored in the post_title column in the database) and sort it ascending. Now our overview looks like this:

Perfect! That’s exactly what we’ve wanted! Now let open up a sponsor as a test:

Below the sponsor’s content, you can see a “post navigation” which was added to the single template of that post type. But when you check against the previous screenshot, the “Previous Sponsor” should link to “Jetpack” and the “Next Sponsor” to “WooCommerce”. Why is the order not reflected in the posts navigation?

Ordering post navigation by name

Inside of the the_post_navigation function, which is used to print those two links, the two functions get_previous_post_link and get_next_post_link are used to get the previous/next link. If we take a look at the database query that is being used, we get this:

SELECT p.ID
FROM wp_posts AS p
WHERE p.post_date < '2020-08-22 09:00:00'
  AND p.post_type = 'sponsor'
  AND (p.post_status = 'publish'
    OR p.post_status = 'private')
ORDER BY p.post_date DESC
LIMIT 1;

As you can see, the previous link is queried by taking the publish date and searching for the post/sponsor with a smaller date.

Fortunately there are filters we can use to change that. To adjust the queries, we have to change the WHERE and the ORDER BY clauses of the query. This is the solution I came up with for the WHERE clause:

function optn_get_adjacent_post_where( $where, $in_same_term, $excluded_terms, $taxonomy, $post ) {
	if ( 'sponsor' !== $post->post_type ) {
		return $where;
	}

	return preg_replace( '/p.post_date (.) ([^A-Z]+)/', "p.post_title $1 '{$post->post_title}' ", $where );
}
add_filter( 'get_previous_post_where', 'optn_get_adjacent_post_where', 10, 5 );
add_filter( 'get_next_post_where', 'optn_get_adjacent_post_where', 10, 5 );

Instead of writing a callback function for both the get_previous_post_where and the get_next_post_where, I’m using just one. As other plugins might have used the hook as well, we don’t simply overwrite the whole clause, but rather use a regex replacement for the parts we know we have to overwrite. So we search for p.post_date and the comparison operator and the date string (using a catch group not matching and alphabetic characters to be able to catch different types of date formats). Then we replace that with the column p.post_title we want to order by, the catched comparison operator and the post title of the current post/sponsor.

If you wonder if that really work, it does! MySQL would get the correct “alphanumerically smaller string”, just as in a sort.

The ORDER BY clause is a lot simpler. We just have to replace the column we want to order by and can keep the original order:

function optn_get_adjacent_post_sort( $order_by, $post, $order ) {
	if ( 'sponsor' !== $post->post_type ) {
		return $order_by;
	}

	return preg_replace( '/p.post_date/', 'p.post_title', $order_by );
}
add_filter( 'get_previous_post_sort', 'optn_get_adjacent_post_sort', 10, 3 );
add_filter( 'get_next_post_sort', 'optn_get_adjacent_post_sort', 10, 3 );

After this modification, our complete SQL query would not look like this:

SELECT p.ID
FROM wp_posts AS p
WHERE p.post_title < 'Siteground'
  AND p.post_type = 'sponsor'
  AND (p.post_status = 'publish'
    OR p.post_status = 'private')
ORDER BY p.post_title DESC
LIMIT 1;

When we open up the single view again, we finally get the correct previous/next sponsor link:

Conclusion

Changing the order of a post type on it’s archive page is really easy. But that might not be enough for all cases. One example of that is the post navigation, but there might be other places where the sort order should also be adjusted. I hope this blog post could give you and idea on how to fix it in similar cases.

If you want to play around with this code yourself, you can find a complete working example as a plugin in this GIST.

Posted by

Bernhard is a full time web developer who likes to write WordPress plugins in his free time and is an active member of the WP Meetups in Berlin and Potsdam.

2 comments » Write a comment

  1. This works well… until you have a post title with an apostrophe in the name. Then it doesn’t. I found that out just now. I couldn’t figure out what was going wrong until I looked in the error logs and saw that the SQL query was getting messed up.

Leave a Reply

Your email address will not be published. Required fields are marked *