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…

Read More →

I find admin notices are a pain. Every time I write a plugin there comes a point where I think “I’ll just add in some notices to help the user” – and two hours later…

I kept thinking, “This should be so much easier”, so finally I sat down and wrote a class that I can use in any plugin to handle pretty much every admin notice scenario I could think of. It’s available on Github for download with how-to explanatory info and examples.

Read More →

This post is one of the examples of how the Hooked Editable Content plugin can be used.

I’ve created this post as a user with the Author role, and in doing that I’ve also been able to add the gallery of two racing car images you can see beneath the main menu.

To do that, I used the hooked editor which was available to me on the Edit Post screen, below the main post content editor. As you can see, it’s just a normal WP Editor, but with the title Hooked Editor: Images after Main menu and some instructions. Using that editor, I could add a gallery of images in the normal way – just as if I was adding a gallery in the content of a normal post.

Read More →

Recently I was working on a child theme of Storefront, which by default changes the colour of whichever menu item you’re hovering over. If you hover over the example menu below you’ll see what I mean.

  • Home
  • Blog
  • Contact

Works fine, right? Then I decided I’d like the selected menu to show in a different colour to help users know where they are. So for example if you’re on the home page, the menu would look like this:

  • Home
  • Blog
  • Contact

But now when you hover over the menu items, there’s a problem. Move the cursor between Home and Blog and a small gap between the menu items is glaringly obvious. The same gap is between all the items actually, but it’s not noticeable in the first example.

Read More →

Epson EB-U32

I belong to a small camera club – 30 to 40 members. Our projector was several years old and had a 1024 x 768 resolution. Time to upgrade, and it fell to me to do the research and make the choice.

So here’s what I learned in case it’s useful to other camera clubs. No doubt it’ll be out of date in a few months, but anyway…

We ended up buying the Epson EB-U32 for about £600 incl sales tax. It’s much much better than what we had.

Here are various things I wrote to our club committee about various considerations at the time. Much of what I am about to summarize is excellently presented by Projector Point, from whom we ended up buying the projector.

Read More →

A wet afternoon in Eastbourne, looking for odd stuff to photograph. This was the best of the lot – a leaf half-submerged in a puddle, and the lights from a nearby shop-window giving it a neat look…

I’m using Fancybox as a lightbox on this site, which was working fine for individual images. But when I posted the gallery of three images in my previous post, I realised the images weren’t linked together in a gallery in a way Fancybox understood. What’s needed is the rel attribute, and a quick google showed loads of people suggesting the same snippet to add to the functions.php file:

Which works fine, provided you only have one gallery on the page. More than one gallery and Fancybox is going to treat all gallery images as though they’re in the same gallery.

Read More →

I entered these in my local camera club competition – theme “Water”. The one I liked the most (Icy stream) did worst, and the one I liked the least (Leaping Pebbles) did best, winning in fact. Shows what I know.

Just finished reading The Year without Pants by Scott Berkun, which is about his 2 year tenure at Automattic working for WordPress.com. Inspiring and worth a look, but not what I’m here to say.

Unusually for me, because it was borrowed from a friend, the book was in traditional paper form instead of downloaded to my Kindle. I love the convenience of the Kindle, but I do get it when people say to me, “It’s just not the same – I need the feel of the paper”. And as a slight aside, before my main point, I have found I need the location feature turned on when reading my Kindle. I am disconcerted that I don’t know how far I am through the book. With the paper version, I know instinctively because of the size of the wedges of paper either side of my thumb. Not so with an e-reader, and it turns out I like to know where I am in the story arc.

Read More →