WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 22 months ago

Last modified 7 months 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 2 years ago.
18158.2.diff (910 bytes) - added by georgestephanis 2 years ago.
Revised patch
18158.3.diff (887 bytes) - added by scribu 2 years ago.
18158.4.diff (1.9 KB) - added by scribu 2 years ago.
18158.5.diff (2.1 KB) - added by scribu 2 years ago.
18158.5.alt.diff (2.1 KB) - added by scribu 2 years ago.

Download all attachments as: .zip

Change History (66)

comment:1 markoheijnen2 years ago

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

comment:2 scribu2 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.

comment:3 markoheijnen2 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.

comment:4 scribu2 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

comment:5 scottconnerly2 years ago

  • Cc scott@… added

comment:6 scribu2 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.

comment:7 wycks2 years ago

  • Cc wycks added

comment:8 micahwave2 years ago

  • Cc micahwave added

comment:9 thomas.mery2 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

comment:10 Master Jake2 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.

comment:11 georgestephanis2 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)

georgestephanis2 years ago

comment:12 PeteMall2 years ago

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

comment:13 SergeyBiryukov2 years ago

  • Keywords has-patch added; needs-patch removed

comment:14 scribu2 years ago

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

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

comment:15 georgestephanis2 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.

georgestephanis2 years ago

Revised patch

comment:16 follow-up: georgestephanis2 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 2 years ago by scribu (previous) (diff)

comment:17 in reply to: ↑ 16 scribu2 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.

comment:18 georgestephanis2 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!

comment:19 scribu2 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.

comment:20 goto102 years ago

  • Cc dromsey@… added

comment:21 dwenaus2 years ago

  • Cc deryk@… added

comment:22 CoenJacobs2 years ago

  • Cc coenjacobs@… added

comment:23 aaroncampbell2 years ago

  • Cc aaroncampbell added

scribu2 years ago

comment:24 scribu2 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.

comment:25 scribu2 years ago

Example usage:

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

comment:26 scribu2 years ago

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

scribu2 years ago

comment:27 scribu2 years ago

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

scribu2 years ago

scribu2 years ago

comment:28 scribu2 years ago

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

comment:29 nacin2 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.

comment:30 follow-up: scribu2 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'.

comment:31 georgestephanis2 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.

comment:32 in reply to: ↑ 30 nacin2 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.

comment:33 scribu2 years ago

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

comment:34 nacin2 years ago

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

This looks good.

It could probably use some unit tests.

comment:35 dancameron2 years ago

  • Cc dancameron added

comment:36 ryan2 years ago

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

comment:37 batmoo2 years ago

  • Cc batmoo@… added

comment:38 ethitter23 months ago

  • Cc ehitter@… added

comment:39 georgestephanis22 months 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?

comment:40 follow-up: scribu22 months 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.

comment:41 scribu22 months 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.

comment:42 in reply to: ↑ 40 georgestephanis22 months 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.

comment:43 nacin22 months 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

comment:44 bananastalktome21 months ago

  • Cc bananastalktome@… added

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

comment:45 WraithKenny20 months 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.

comment:46 georgestephanis20 months 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.

comment:47 scribu20 months 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 20 months ago by scribu (previous) (diff)

comment:48 wonderboymusic20 months ago

order by meta_value with meta_type cast here: #21621

comment:49 WraithKenny20 months 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?

comment:50 WraithKenny20 months ago

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

comment:51 follow-up: rinatkhaziev19 months ago

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

comment:52 in reply to: ↑ 51 SergeyBiryukov19 months 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].

comment:53 jazbek17 months ago

  • Cc j.yzbek@… added

comment:54 dwenaus17 months ago

  • Cc deryk@… removed

comment:55 joehoyle16 months ago

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

comment:56 timersys15 months 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.

comment:57 timersys15 months ago

  • Cc timersys added

comment:58 DrewAPicture12 months ago

  • Cc xoodrew@… added

comment:59 wycks7 months 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'
              ),
        )

comment:60 georgestephanis7 months 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.