Meta Query for posts with a meta key not set (IS NULL).

[johnnyb]

In the meta_query, we should be able to query for posts that don't have a certain meta key at all. I suggest a syntax something like this:

... 
meta_query => array(
        array(
            'key' => '_my_special_meta',
            'compare' => 'NOT EXISTS'
        )
    )
...

Alternately this could be handled invisibly along the lines of:

... 
meta_query => array(
        array(
            'key' => '_my_special_meta',
            'value' => 'a_value_this_might_have',
            'compare' => '!='
        )
    )
...

This could be done using a left join, and not some sort of special case like above. Since it's a left join we may have to be careful that we don't end up with a bunch of junk rows in the query, however.

Definitely something worth adding. An explicit compare value, such as 'compare' => 'NOT EXISTS', would be better, IMO.

I was playing with it. I am not sure what the solution will be. The query now is pretty hard since it does a INNER JOIN on all meta keys/values. So a WHERE seems not to be possible.

How I see it there should be an join on the post_meta table and join on the meta key. Somehow that looks like an ugly solution to me since you need te keep track on the naming of it.
For a singular meta-query you can do only one join to the post_meta table I guess.

Each inner meta query is one join. So for the NOT EXISTS case, it could be:

... INNER JOIN wp_postmeta as m1
... AND m1.meta_key != $meta_key

Actually, this should work:

... LEFT JOIN wp_postmeta as m1
... AND m1.meta_key IS NULL

This can be done for as many queries as necessary.

Hi there,

is there any chance this will be implemented ?

as far as I know there are no wyas to do this without resorting to custom queries rigt now

would be nice as as wp grows one expects it to be able to this 'out of the box'

hope i can find time to delve into the pb

I agree with the explicit 'NOT EXISTS' option. You could string it in with other queries using the relation 'OR' to achieve the proper result.

I'm attaching a first draft of a patch for this. It's not ideal, and I'm sure there's a better way to do some of the bits, but it's a functional proof of concept. (or should be, I'm going to double-check and test it this evening, but it should be structurally sound based on other similar things I've done)

So, if it's an INNER JOIN, how could meta_id ever be null?

Oh drat, I left it as INNER JOIN, didn't I? I meant for that to be LEFT JOIN. My bad.

Basically, if it can't find anything to join to, it will just return 'NULL' for those values. So if you test for NULL in a field that would never be null if a value is returned (like post_id for instance, like I have) -- it will tell you if it found something to join with, or came up empty.

The only thing I haven't considered if what if there's multiple instances of a meta_key -- if that would return multiple rows in the final result. I wrote it assuming a max of 1 per. I'll need to test for that.

Revised patch

Usage would basically be ...

---

$my_query = new WP_Query(

array(

'meta_query' => array(

array(

'key' => 'foo',
'compare' => 'not exists',
'value' => null,

),

)

)

);

---

'value' has to be included, or the meta_query gets skipped. However, it is entirely ignored for EXISTS and NOT EXISTS meta_queries.

---

I would also recommend upgrading the db version and adding an index to the meta_key column in wp_postmeta -- the idea had some support in IRC, and it would make the join much more manageable in large sites.

Replying to georgestephanis:

'value' has to be included, or the meta_query gets skipped.

That should not be the case, as you're able to specify 'meta_key' without 'meta_value' in the main query, for example.

I would also recommend upgrading the db version and adding an index to the meta_key column in wp_postmeta

Upgrading the db version is necessary only when modifying the schema. I thought the meta_key column already had an index. We should add it if it's not there.

Oh, I must have missed it going in then -- I just ran a clean install to test, and it does in fact have an index -- my mistake!

And testing -- I think I just got confused earlier, you're completely correct. The value isn't necessary.

As I had mentioned before -- there probably is a more correct way of doing this with different values, and please let me know what that would be, this is just the simple fix that works and adds the functionality in. :)

Thanks for the feedback, scribu!

No idea if it will make it into 3.4, but throwing it in anyway.

18158.3.diff​ is just some minor cleanup. I moved the if block before the original $join lines, to avoid a little extra work.

Example usage:

$q = new WP_Query( array(
	'meta_query' => array(
		array(
			'key' => 'foo',
			'compare' => 'EXISTS'
		),
		array(
			'key' => 'bar',
			'compare' => 'NOT EXISTS'
		)
	)
) );

Actually, 'compare' => 'EXISTS' is totally unnecessary. Doing an INNER JOIN is enough to ensure that the value is not null.

With 18158.4.diff​, 'compare' => 'EXISTS' still works, but you can skip it altogether.

18158.5.alt.diff​ replaces NOT EXISTS with IS NULL, which seems more consistent with the other options (closer to SQL terminology).

NOT EXISTS probably fits better here, and does have a tie-in for SQL terminology that is probably more relevant: ​http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html.

I fail to see the relevance of that link. There are no subqueries involved.

When you write 'compare' => 'BETWEEN', that's what the generated SQL contains. It should be the same for 'compare' => 'IS NULL'.

Quite frankly, EXISTS and NOT EXISTS is a much more straightforward and simpler to understand way of phrasing what we're trying to achieve. It's simple, descriptive, and will do the job. I'm fully in favor of EXISTS and NOT EXISTS. If you want to include other options as well, go ahead, but I'd recommend including these two at a minimum.

Replying to scribu:

I fail to see the relevance of that link. There are no subqueries involved.

SELECT wp_posts.ID
FROM wp_posts
WHERE NOT EXISTS (
   SELECT ID
   FROM wp_postmeta
   WHERE wp_posts.ID = wp_postmeta.post_id
     AND meta_key = %s
   )
   AND wp_posts.post_type = 'post'
   AND (wp_posts.post_status = 'publish')
   GROUP BY wp_posts.ID
   ORDER BY wp_posts.post_date DESC
   LIMIT 0, 10

While there is no subquery involved, there very well could be. Here's that query, above. The point is, this is the standard way of checking for existence, not the left join. The join simply happens to be more performant in my brief testing.

When you write 'compare' => 'BETWEEN', that's what the generated SQL contains. It should be the same for 'compare' => 'IS NULL'.

Yeah, but it's only NULL cause of the LEFT JOIN and that meta_value cannot be null. It's not very intuitive for someone who is used to an ORM or raw database management to recognize what WordPress is using IS NULL to achieve. NOT EXISTS is far more obvious regardless of what we translate it to.

Ok, nevermind. We'll stick with NOT EXISTS.

This looks good.

It could probably use some unit tests.

scribu: I think we need a if() continue; statement before it deals with the value part of the where clause ... EXISTS and NOT EXISTS are all referring exclusively to the meta_key -- if they accidentally specify a $q['value'] it may add on some extra where conditionals, which I don't think we want.

Also, for EXISTS, wouldn't a multi-parameter join most often be more efficient than a single-param join and a where clause? Or do they break down to the same in the MySQL server's compiler?

wouldn't a multi-parameter join most often be more efficient than a single-param join and a where clause?

I don't know. Do an EXPLAIN and share what you find out.

if they accidentally specify a $qvalue? it may add on some extra where conditionals, which I don't think we want.

Sure; we should skip adding any SQL for meta_value for NOT EXISTS.

Replying to scribu:

wouldn't a multi-parameter join most often be more efficient than a single-param join and a where clause?

I don't know. Do an EXPLAIN and share what you find out.

They distill down to the same thing, but the consensus seems to be that keeping the filter in the WHERE may be more readable later.

In [21185]:

Add NOT EXISTS to meta queries, allowing you to query for the non-existence of a meta key.

You could already use EXISTS by omitting a value to check.

props georgestephanis, scribu
fixes #18158

I added some unit tests for this ​UT:#115 and any feedback on them is appreciated.

Does this make it possible to orderby a meta_value and not exclude records lacking the meta?

I've tried a bunch of things, including:

'meta_query' => array(
	'relation' => 'OR',
	array(
		'key' => 'geo_public',
	),
	array(
		'key' => 'geo_public',
		'compare' => 'NOT EXISTS',
	),
),

but it seems the first trumps the 2nd. It does work if by itself.

You're doing it wrong.

Meta query is an array of arrays of comparisons. You're nesting stuff one level above where it should go.

WraithKenny's query is correct, in syntax: the outer array contains the 'relation', while the inner arrays contain the specific clauses. It doesn't make sense logically, though.

Does this make it possible to orderby a meta_value and not exclude records lacking the meta?

This change has no bearing on order. For making 'meta_query' play better with orderby see #15031

order by meta_value with meta_type cast here: #21621

Sorry, what I meant was 'compare' => 'NOT EXISTS' doesn't work in combination: if you have the other clause, or if you set 'meta_key' to anything. Is that intentional?

I'll move my questions over to #19653 as it's a better fit

What happend to this? Don't see this changeset in #21825. Is it still not merged in?

Replying to rinatkhaziev:

What happend to this? Don't see this changeset in #21825. Is it still not merged in?

It's in, see [21185].

It looks like some tests were committed in [974/tests] , is the "needs-unit-tests" label still needed here?

This query should work or not?

	$the_query = new WP_Query(
		array(
			'post_type' => 'post',
			'category__in'    => array(1,2) ,
			'posts_per_page' => -1,
			'meta_query'	=> array(
				relation' => 'OR',
 
				array( // posts with not key at all
				'key' => 'my_custom_key',
				'compare' => 'NOT EXISTS'
				 ),
				array( //or posts with key set to false 
				'key'	=> 'my_custom_key',
				'value' => '0',
				'compare' => '='
				)
			)
		)
	);

If i make the meta_query with one condition it works in both cases.
As soon as i add both conditions and 'OR' relation it wont work at all.