#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)
Change History (67)
#2
@
13 years ago
- Keywords needs-patch added; dev-feedback removed
- Milestone changed from Awaiting Review to Future Release
#3
@
13 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
@
13 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
#6
@
13 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.
#9
@
13 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
@
13 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
@
13 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)
#15
@
13 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.
#16
follow-up:
↓ 17
@
13 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.
#17
in reply to:
↑ 16
@
13 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 inwp_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
@
13 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
@
13 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.
#24
@
13 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
@
13 years ago
Example usage:
$q = new WP_Query( array( 'meta_query' => array( array( 'key' => 'foo', 'compare' => 'EXISTS' ), array( 'key' => 'bar', 'compare' => 'NOT EXISTS' ) ) ) );
#26
@
13 years ago
Actually, 'compare' => 'EXISTS' is totally unnecessary. Doing an INNER JOIN is enough to ensure that the value is not null.
#27
@
13 years ago
With 18158.4.diff, 'compare' => 'EXISTS' still works, but you can skip it altogether.
#28
@
13 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
@
13 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:
↓ 32
@
13 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
@
13 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
@
13 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.
#34
@
13 years ago
- Keywords needs-unit-tests commit added; needs-testing removed
This looks good.
It could probably use some unit tests.
#39
@
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:
↓ 42
@
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
@
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
@
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.
#44
@
12 years ago
- Cc bananastalktome@… added
I added some unit tests for this UT:#115 and any feedback on them is appreciated.
#45
@
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
@
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
@
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
#49
@
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?
#51
follow-up:
↓ 52
@
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
@
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].
#55
@
12 years ago
It looks like some tests were committed in [974/tests] , is the "needs-unit-tests" label still needed here?
#56
@
12 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.
#59
@
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
@
11 years ago
Looks like the unit tests only covers NOT EXISTS. We really should add one for EXISTS.
#61
@
2 years 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.
Definitely something worth adding. An explicit compare value, such as
'compare' => 'NOT EXISTS'
, would be better, IMO.