How to sort WordPress posts by custom field (post meta)

There are lots of different options for sorting posts available in WP_Query class, sorting by custom fields, or post meta, is one of them. However, sometimes the default sorting mechanism is not enough. Let’s take a look at one of such cases and find the solution.

Here is the case: we have a numeric custom field (post meta) named order. Some posts does not have it filled. Now, we want to use this meta value to sort posts, but we also want to display posts which have a meta value first, followed by all the posts without any value.

First thing which comes to mind is to use meta_query to select posts with and without the meta and then add proper order option to sort by this meta query:

$queryArgs['meta_query'] = [
	'relation' => 'OR',
	[
		'key' => 'order',
		'compare' => 'EXISTS',
		'type' => 'NUMERIC',
	],
	[
		'key' => 'order',
		'compare' => 'NOT EXISTS',
	],
];

$queryArgs['order'] = 'meta_value';

While this approach could be useful in many cases, in our scenario it has a one problem: depending on the sorting order, posts without the meta value will be displayed after (DESC) or before (ASC) the posts with meta value, but we want them to always display at the end.

Here i how to solve it with custom SQL JOIN clause:

1. Add custom JOIN clause instead of meta_query

add_action( 'posts_join_request', function( string $orderby, \WP_Query $query ) {
	if ( $thisIsTheQueryYouWantToAlter ) {
		global $wpdb;

		$metaKey = 'order'; // Use whathever your post meta key is...

		$join .= " LEFT JOIN $wpdb->postmeta as custom_order_meta ON (
			$wpdb->posts.ID = custom_order_meta.post_id AND custom_order_meta.meta_key = '$metaKey'
		) ";
	}

	return $join;
} );

2. Manually add order clauses

Now we need to add two orderby clauses, first to sort posts with any meta value before the ones without meta value and second to sort posts by meta value itself.

add_action( 'posts_orderby_request', function( string $orderby, \WP_Query $query ) {
	if ( ! $thisIsTheQueryYouWantToAlter ) {
		return $orderby;
	}

	$order = 'ASC'; // or 'DESC';

	return join(
		', ',
		[
			'custom_order_meta.meta_value IS NOT NULL DESC', // use `ASC` to show posts without any value first.
			"custom_order_meta.meta_value {$order}",
			ltrim($orderby, ', '),
		]
	);
} );