Make WordPress Core

Opened 13 years ago

Closed 12 years ago

Last modified 19 months ago

#18158 closed enhancement (fixed)

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

Reported by: johnnyb's profile johnnyb Owned by: georgestephanis's profile 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 12 years ago.
18158.2.diff (910 bytes) - added by georgestephanis 12 years ago.
Revised patch
18158.3.diff (887 bytes) - added by scribu 12 years ago.
18158.4.diff (1.9 KB) - added by scribu 12 years ago.
18158.5.diff (2.1 KB) - added by scribu 12 years ago.
18158.5.alt.diff (2.1 KB) - added by scribu 12 years ago.

Download all attachments as: .zip

Change History (67)

#1 @markoheijnen
12 years ago

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

#2 @scribu
12 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
12 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
12 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
12 years ago

  • Cc scott@… added

#6 @scribu
12 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
12 years ago

  • Cc wycks added

#8 @micahwave
12 years ago

  • Cc micahwave added

#9 @thomas.mery
12 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
12 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
12 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
12 years ago

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

#13 @SergeyBiryukov
12 years ago

  • Keywords has-patch added; needs-patch removed

#14 @scribu
12 years ago

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

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

#15 @georgestephanis
12 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
12 years ago

Revised patch

#16 follow-up: @georgestephanis
12 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.

Version 0, edited 12 years ago by georgestephanis (next)

#17 in reply to: ↑ 16 @scribu
12 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
12 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
12 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
12 years ago

  • Cc dromsey@… added

#21 @dwenaus
12 years ago

  • Cc deryk@… added

#22 @CoenJacobs
12 years ago

  • Cc coenjacobs@… added

#23 @aaroncampbell
12 years ago

  • Cc aaroncampbell added

@scribu
12 years ago

#24 @scribu
12 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
12 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
12 years ago

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

@scribu
12 years ago

#27 @scribu
12 years ago

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

@scribu
12 years ago

@scribu
12 years ago

#28 @scribu
12 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
12 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
12 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
12 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
12 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
12 years ago

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

#34 @nacin
12 years ago

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

This looks good.

It could probably use some unit tests.

#35 @dancameron
12 years ago

  • Cc dancameron added

#36 @ryan
12 years ago

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

#37 @batmoo
12 years ago

  • Cc batmoo@… added

#38 @ethitter
12 years ago

  • Cc ehitter@… added

#39 @georgestephanis
12 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
12 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
12 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
12 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
12 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
12 years ago

  • Cc bananastalktome@… added

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

#45 @WraithKenny
12 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
12 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
12 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 12 years ago by scribu (previous) (diff)

#48 @wonderboymusic
12 years ago

order by meta_value with meta_type cast here: #21621

#49 @WraithKenny
12 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
12 years ago

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

#51 follow-up: @rinatkhaziev
12 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
12 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
11 years ago

  • Cc j.yzbek@… added

#54 @dwenaus
11 years ago

  • Cc deryk@… removed

#55 @joehoyle
11 years ago

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

#56 @timersys
11 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
11 years ago

  • Cc timersys added

#58 @DrewAPicture
11 years ago

  • Cc xoodrew@… added

#59 @wycks
11 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
11 years ago

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

#61 @jerry45jessu
19 months ago

aomei partition assistant crack is an easy to use all-in-one Hard Disk Partition Software. AOMEI Partition Assistant 9.7.0 Crack offers various free partition management features for both all home users and commercial users. it guarantees the full features for creating, resizing, moving, copying, deleting, wiping, aligning, formating, merging, splitting partition, and more.
https://www.google.com/url?q=https://freeactivationkeys.org/
https://freeactivationkeys.org/aomei-partition-assistant-crack/
https://freeactivationkeys.org/ibm-spss-statistics-crack/
ibm spss statistics crack is a powerful tool used to analyze statistics and manage data and documents. It helps users quickly hold a significant business, social science, and many other relevant fields.

Note: See TracTickets for help on using tickets.