WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 4 years ago

Last modified 3 years ago

#18158 closed enhancement (fixed)

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

Reported by: johnnyb Owned by: georgestephanis
Milestone: 3.5 Priority: normal
Severity: normal Version: 3.2.1
Component: Query Keywords: has-patch needs-unit-tests commit
Focuses: Cc:

Description

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.

Attachments (6)

18158.diff (1.2 KB) - added by georgestephanis 5 years ago.
18158.2.diff (910 bytes) - added by georgestephanis 5 years ago.
Revised patch
18158.3.diff (887 bytes) - added by scribu 5 years ago.
18158.4.diff (1.9 KB) - added by scribu 5 years ago.
18158.5.diff (2.1 KB) - added by scribu 5 years ago.
18158.5.alt.diff (2.1 KB) - added by scribu 4 years ago.

Download all attachments as: .zip

Change History (66)

#1 @markoheijnen
5 years ago

  • Cc marko@… added
  • Keywords dev-feedback added

#2 @scribu
5 years ago

  • Keywords needs-patch added; dev-feedback removed
  • Milestone changed from Awaiting Review to Future Release

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

#3 @markoheijnen
5 years ago

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.

#4 @scribu
5 years ago

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

#5 @scottconnerly
5 years ago

  • Cc scott@… added

#6 @scribu
5 years ago

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.

#7 @wycks
5 years ago

  • Cc wycks added

#8 @micahwave
5 years ago

  • Cc micahwave added

#9 @thomas.mery
5 years ago

  • Summary changed from Meta Query for posts with a meta key not set. to Meta Query for posts with a meta key not set (IS NULL).

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

#10 @Master Jake
5 years ago

  • Cc chappellind@… added

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.

#11 @georgestephanis
5 years ago

  • Keywords has-patch added
  • Owner set to georgestephanis
  • Status changed from new to accepted

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)

#12 @PeteMall
5 years ago

  • Keywords dev-feedback needs-testing added; has-patch removed

#13 @SergeyBiryukov
5 years ago

  • Keywords has-patch added; needs-patch removed

#14 @scribu
5 years ago

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

Version 0, edited 5 years ago by scribu (next)

#15 @georgestephanis
5 years ago

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.

@georgestephanis
5 years ago

Revised patch

#16 follow-up: @georgestephanis
5 years ago

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.

Last edited 5 years ago by scribu (previous) (diff)

#17 in reply to: ↑ 16 @scribu
5 years ago

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.

#18 @georgestephanis
5 years ago

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!

#19 @scribu
5 years ago

  • Keywords dev-feedback removed
  • Milestone changed from Future Release to 3.4

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

#20 @goto10
5 years ago

  • Cc dromsey@… added

#21 @dwenaus
5 years ago

  • Cc deryk@… added

#22 @CoenJacobs
5 years ago

  • Cc coenjacobs@… added

#23 @aaroncampbell
5 years ago

  • Cc aaroncampbell added

@scribu
5 years ago

#24 @scribu
5 years ago

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

#25 @scribu
5 years ago

Example usage:

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

#26 @scribu
5 years ago

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

@scribu
5 years ago

#27 @scribu
5 years ago

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

@scribu
5 years ago

@scribu
4 years ago

#28 @scribu
4 years ago

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

#29 @nacin
4 years ago

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.

#30 follow-up: @scribu
4 years ago

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'.

#31 @georgestephanis
4 years ago

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.

#32 in reply to: ↑ 30 @nacin
4 years ago

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.

#33 @scribu
4 years ago

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

#34 @nacin
4 years ago

  • Keywords needs-unit-tests commit added; needs-testing removed

This looks good.

It could probably use some unit tests.

#35 @dancameron
4 years ago

  • Cc dancameron added

#36 @ryan
4 years ago

  • Keywords early added
  • Milestone changed from 3.4 to Future Release

#37 @batmoo
4 years ago

  • Cc batmoo@… added

#38 @ethitter
4 years ago

  • Cc ehitter@… added

#39 @georgestephanis
4 years ago

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?

#40 follow-up: @scribu
4 years ago

  • Keywords early removed
  • Milestone changed from Future Release to 3.5

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.

#41 @scribu
4 years ago

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.

#42 in reply to: ↑ 40 @georgestephanis
4 years ago

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.

#43 @nacin
4 years ago

  • Resolution set to fixed
  • Status changed from accepted to closed

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

#44 @bananastalktome
4 years ago

  • Cc bananastalktome@… added

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

#45 @WraithKenny
4 years ago

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.

#46 @georgestephanis
4 years ago

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.

#47 @scribu
4 years ago

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

Last edited 4 years ago by scribu (previous) (diff)

#48 @wonderboymusic
4 years ago

order by meta_value with meta_type cast here: #21621

#49 @WraithKenny
4 years ago

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?

#50 @WraithKenny
4 years ago

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

#51 follow-up: @rinatkhaziev
4 years ago

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

#52 in reply to: ↑ 51 @SergeyBiryukov
4 years ago

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].

#53 @jazbek
4 years ago

  • Cc j.yzbek@… added

#54 @dwenaus
4 years ago

  • Cc deryk@… removed

#55 @joehoyle
4 years ago

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

#56 @timersys
4 years ago

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.

#57 @timersys
4 years ago

  • Cc timersys added

#58 @DrewAPicture
3 years ago

  • Cc xoodrew@… added

#59 @wycks
3 years ago

As far as I can tell the example below still does not work using 3.6.1.

'meta_query' => array(
	array(
	      'key'     => 'some_key',
              'compare' => 'EXISTS'
              ),
        )

#60 @georgestephanis
3 years ago

Looks like the unit tests only covers NOT EXISTS. We really should add one for EXISTS.

Note: See TracTickets for help on using tickets.