[wp-trac] [WordPress Trac] #18158: Meta Query for posts with a meta key not set (IS NULL).

[WordPress Trac]

#18158: Meta Query for posts with a meta key not set (IS NULL).
-------------------------------------+------------------------------
 Reporter:  johnnyb                  |       Owner:  georgestephanis
     Type:  enhancement              |      Status:  accepted
 Priority:  normal                   |   Milestone:  3.4
Component:  Query                    |     Version:  3.2.1
 Severity:  normal                   |  Resolution:
 Keywords:  needs-testing has-patch  |
-------------------------------------+------------------------------

Comment (by nacin):

 Replying to [comment:30 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.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/18158#comment:32>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software