Wordpress 4.2 introduced a more sophisticated handling of ordering for meta queries. I started trying to use it and found it tricky to adapt to what I needed for my particular situation. In fact I’d go so far as to say the solution is completely counter-intuitive.

All I was trying to do was order attachments by a custom field. That seemed pretty simple, and my first attempt looked something like this:

$args = array(
	'post_type' => 'attachment',
	'post_status' => 'inherit',
	'orderby' => 'meta_value',
	'order' => 'ASC',
	'meta_query' => array(
		array( 
			'key' => 'my_custom_field',
			'compare' => 'EXISTS'
		)
	),
	'posts_per_page' => -1
);

$query = new WP_Query( $args );

This worked in a sense, but it only returned those attachments that had the custom field set. Which if I’d thought about it properly to start with would have been obvious. So how to adapt the query so that all attachments would be returned, whether or not the custom field was set for any given one?

The next attempt was to add in another clause, this time using 'compare' => 'NOT EXISTS', and join it with the first clause using OR:

$args = array(
	'post_type' => 'attachment',
	'post_status' => 'inherit',
	'orderby' => 'meta_value',
	'order' => 'ASC',
	'meta_query' => array(
		'relation' => 'OR',
		array( 
			'key' => 'my_custom_field',
			'compare' => 'EXISTS'
		),
		array( 
			'key' => 'my_custom_field',
			'compare' => 'NOT EXISTS'
		)
	),
	'posts_per_page' => -1
);

$query = new WP_Query( $args );

This certainly returned all posts, but the ordering didn’t seem to be working. It was doing something because the attachments were coming back ordered differently from the default order, but they certainly weren’t ordered by my custom field. What the blue blistering blazes was going on?

The next step was to examine the SQL query WP was actually generating, found in the result property of the WP_Query object:

SELECT wp_{prefix}_posts.* FROM wp_{prefix}_posts
LEFT JOIN wp_{prefix}_postmeta ON ( wp_{prefix}_posts.ID = wp_{prefix}_postmeta.post_id )
LEFT JOIN wp_{prefix}_postmeta AS mt1 ON (wp_{prefix}_posts.ID = mt1.post_id AND mt1.meta_key = 'my_custom_field' )
WHERE 1=1  AND ( 
	wp_{prefix}_postmeta.meta_key = 'my_custom_field' 
	OR 
	mt1.post_id IS NULL
) AND
wp_{prefix}_posts.post_type = 'attachment' AND ((wp_{prefix}_posts.post_status = 'inherit'))
GROUP BY wp_{prefix}_posts.ID ORDER BY wp_{prefix}_postmeta.meta_value ASC 

This showed how WP was parsing my meta query vars. Essentially each “clause” was being converted into a postmeta table (with one being given the mt1 alias), and both were being joined to the posts table via LEFT JOIN.

My head starts throbbing when I try to figure out what multi-joined tables are going to produce, and I do better if I can see what’s actually happening. So I amended the query slightly and ran it directly on the database. The only two changes were:

• Amending the fields being selected – wp_{prefix}_posts.ID, wp_{prefix}_postmeta.meta_value, mt1.meta_value instead of wp_{prefix}_posts.*; and
• Removing the GROUP BY clause.

Suddenly everything became clear. The column my query was ordering by – wp_{prefix}_postmeta.meta_value – was populated not only by the meta_values of the ‘my_custom_field' custom field, but also by every other custom field used by attachments. Which included, for example, the _wp_attached_file and _wp_attachment_metadata meta_keys. No wonder the ordering was all over the shop.

The mt1.meta_value column though, that was doing what I needed. Wherever an attachment had postmeta with a meta_key of 'my_custom_field', the value was being shown. And wherever an attachment didn’t have the custom field, NULL was being returned.

So that meant all I had to do was change the ordering from the EXISTS clause to the NOT EXISTS clause.

Wait, what?

Order by the clause which is searching for where there is no meta_value? That makes no sense – but it worked. And here are the two ways I used of doing the same thing…

Solution 1

Since WP sorts by the first meta-query clause, I just had to swap the two clauses around, so that the NOT EXISTS clause came first:

$args = array(
	'post_type' => 'attachment',
	'post_status' => 'inherit',
	'orderby' => 'meta_value',
	'order' => 'ASC',
	'meta_query' => array(
		'relation' => 'OR',
		array( 
			'key' => 'my_custom_field',
			'compare' => 'NOT EXISTS'
		),
		array( 
			'key' => 'my_custom_field',
			'compare' => 'EXISTS'
		)
	),
	'posts_per_page' => -1
);

$query = new WP_Query( $args );

Solution 2

The more transparent method is to make the clauses associative arrays and then orderby the appropriate key:

$args = array(
	'post_type' => 'attachment',
	'post_status' => 'inherit',
	'orderby' => 'not_exists_clause',
	'order' => 'ASC',
	'meta_query' => array(
		'relation' => 'OR',
		'exists_clause' => array( 
			'key' => 'my_custom_field',
			'compare' => 'EXISTS'
		),
		'not_exists_clause' => array( 
			'key' => 'my_custom_field',
			'compare' => 'NOT EXISTS'
		)
	),
	'posts_per_page' => -1
);

$query = new WP_Query( $args );

Sorting out capabilities for custom post types is tricky. At least I think it is – mainly because it’s hard to figure out what you’re supposed to be doing. Both the code and the documentation seem somewhat opaque to me.

So this is my attempt at unraveling how it works – together with some different ways you might want to set up your custom post types. And it is an attempt, so if I’ve got something wrong or I’m not making sense, please let me know…