WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to get a query to retrieve all the posts where a specific meta_key does not exist and then create it.

I am having problems finding those posts as the query I am testing does not seem to work.

Here is the code I am using to try to get those posts:

$args = array(
   'posts_per_page' => 18,
   'cat'=>1955,
   'post_status'=>'publish',
   'meta_query' => array(
                  array(
                     'key' => 'colors',
                     'compare' => 'NOT EXISTS'
                  ),
   ));      

query_posts($args);

This returns nothing if there are no posts with the key colors, but returns they ids of the posts with the key colors whenever that key is present (the opposite of what I need). I tried with EXIST instead but no luck.

If someone can tip me on the correct way of creating a query like the one I need I will appreciate it.

Thanks!

share|improve this question
    
What version of WordPress are you using? – s_ha_dum Jan 12 '13 at 17:10
    
Hi,sorry for the omission. I am using v3.5 – JordanBel Jan 12 '13 at 18:27
    
It seems as though that type of query (with compare set to NOT EXISTS) was added in 3.5, so it should work as it is, as far as I can see. It would be easy to do it via custom SELECT query, though... – Tomas Buteler Jan 12 '13 at 20:00
    
Thanks I will try using select. I must learn before which tables to query and how to conform the query though :( – JordanBel Jan 12 '13 at 21:08
    
Very strange. I can't spot a problem with that code and you are using 3.5+, which is why I asked. Have you actually looked at the database to confirm that your data is being inserted the way you think it is? – s_ha_dum Jan 13 '13 at 17:07
up vote 28 down vote accepted

I did some more testing with this, and honestly can't find a reason it wouldn't work (unless the code above is just a snippet and the real code fits on my examples below). I did, however, discover a couple of things that might lead you in the right direction.

1) By itself, this meta query is the equivalent of "colors IS NULL", i.e. it'll return the posts which don't have that key set in the postmeta table. This is the case shown above, and it should've worked.

'meta_query' => array(
    array(
     'key' => 'colors',
     'compare' => 'NOT EXISTS' // this should work...
    ),
)

2) Establishing the 'relation' index to 'OR', though, changes this condition. It returns the opposite. Don't ask me why. This is especially important when doing multiple meta queries. That means that is not initially possible to do a query for posts that have 'colors' key set to 'blue' (or whatever) or not set at all. The query below will ignore the first condition and return only those that match the second condition.

'meta_query' => array(
   'relation' => 'OR',
    array(
     'key' => 'colors',
     'compare' => 'NOT EXISTS' // doesn't work
    ),
    array(
     'key' => 'colors',
     'value' => 'blue'
    )
)

3) However, we can fool WordPress into using the first condition if we set the 'value'. It doesn't need a relevant value (it's ignored, as far as I know), but it needs to be set in order for the NOT EXISTS condition to have any effect.

'meta_query' => array(
   'relation' => 'OR',
    array(
     'key' => 'colors',
     'compare' => 'NOT EXISTS', // works!
     'value' => '' // This is ignored, but is necessary...
    ),
    array(
     'key' => 'colors',
     'value' => 'blue'
    )
)

This is true as of WordPress 3.5. Maybe it's unintentional and they'll fix it in future versions, but there could be a reason why this behaves this way, and this is a viable workaround.

share|improve this answer
    
Thanks! And sorry for the delay. I ended up using a query, but I will be testing your solution in the following hours so I can switch back and maybe if this work we can help some other. I will let you know as soon as I can check it. Thanks again – JordanBel Jan 17 '13 at 1:49
    
Well written and confirmed that adding an empty value returns expected results. I'd say it's unintentional, may be worth a look at trac.wordpress.org to see if there's already a ticket, if not, this is reproducible. – Taylor Dewey Mar 6 '13 at 19:22
    
Thanks for the great explanation and solution to trick WP :) Did take some time to get here - but now I want to click upvote for at least 10 times (if only I could ;)) – lorem monkey Mar 21 '13 at 14:18
    
Brilliant answer!! – sri Mar 11 '14 at 12:41
    
If I use compare EXISTS, value is unfortunately not ignored in newer versions of WP (tested in 4.2.2) – Igor Jerosimić Jul 1 '15 at 10:08

Using a custom query, this worked for me:

SELECT * FROM wp_posts as posts
            WHERE   posts.post_type     = 'post'
            AND NOT EXISTS (
              SELECT * FROM `wp_postmeta`
               WHERE `wp_postmeta`.`meta_key` = "your_meta_key"
                AND `wp_postmeta`.`post_id`=posts.ID
            ) 
share|improve this answer

Use this query:

select * from wp_posts
where id not in
(select distinct post_id from wp_postmeta where meta_key='_your_meta_key')
share|improve this answer
1  
Please explain why this helps. – kaiser Apr 11 at 1:16
    
Here we got the same problem like with your other answer. Aside from that, this as well is an old question and the other answers are far more throughout than yours. What's the point of adding such answers? Gaining reputation? If you are trying to do that, then please read in the help center the "how to write a good answer" section and take on some more current and urging problems – or at least some old and unanswered questions. – kaiser Apr 11 at 2:57
    
well i dnt knw how you gain popularity by posting on "someone elses" website" :) i was working on this issue too and made some queries, had couple of pages including this one opened through google while i was trying to solve it, and when luckily i solved it, i thought to share the code :) anyway, you won't understand, good luck in getting popular on some question answer website :) – Nabeel Khan Apr 11 at 4:02
    
There's no "popularity" anywhere, but a "points" system, where the "points" are named "reputation points". That is what I am referring to. Can't you just edit your answer and make this WordPress specific? Adding an explanation of what your query does and how it solves the OP problem might even drive upvotes (that give your "reputation points" which end up giving your privileges for certain actions/access to capabilities on this site) to your answer. – kaiser Apr 11 at 11:04
    
well i dunno why would someone "downvote" an answer, which is "irrelevant" to him (at least while downvoting), just to gain some "reputation", without even reading and understanding the original question posters requirement that he needs the "sql query" – Nabeel Khan Apr 11 at 12:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.