WP_Query `NOT EXISTS` examples

23268.diff

diff --git wp-includes/meta.php wp-includes/meta.php
index 8a9dad9..d3e4f82 100644
--- wp-includes/meta.php
+++ wp-includes/meta.php
@@ -708,7 +708,7 @@ class WP_Meta_Query {
     	// Split out the meta_key only queries (we can only do this for OR)
 		if ( 'OR' == $this->relation ) {
 			foreach ( $this->queries as $k => $q ) {
-				if ( ! isset( $q['value'] ) && ! empty( $q['key'] ) )
+				if ( ( empty( $q['compare'] ) || 'NOT EXISTS' != $q['compare'] ) && ! isset( $q['value'] ) && ! empty( $q['key'] ) )
 					$key_only_queries[$k] = $q;
 				else
 					$queries[$k] = $q;

wp_query_not_exists.sql

/*

Generated with:

$q = new \WP_Query(array(
    'meta_query'    => array(
        'relation'      => 'OR',
        array(
            'key'           => 'a_key',
            'compare'       => 'NOT EXISTS',
        ),
    )
));

*/

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM wp_posts
    INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE 1=1
    AND wp_posts.post_type = 'post'
    AND (
        wp_posts.post_status = 'publish' OR
        wp_posts.post_status = 'private'
    )
    AND (
        wp_postmeta.meta_key = 'a_key' -- Not expected
    )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10;

/*

Generated with:

$q = new \WP_Query(array(
    'meta_query'    => array(
        'relation'      => 'AND',
        array(
            'key'           => 'a_key',
            'compare'       => 'NOT EXISTS',
        ),
    )
));

*/

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM wp_posts
    LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'a_key')
WHERE 1=1
    AND wp_posts.post_type = 'post'
    AND (
        wp_posts.post_status = 'publish' OR
        wp_posts.post_status = 'private'
    )
    AND ( wp_postmeta.post_id IS NULL ) -- expected!
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10;

/*

Specifying a value with the OR query also makes NOT EXISTS work correctly.

$q = new \WP_Query(array(
    'meta_query'    => array(
        'relation'      => 'OR',
        array(
            'key'           => 'a_key',
            'compare'       => 'NOT EXISTS',
            'value'         => 'n/a',
        ),
    )
));

*/

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM wp_posts
    LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'a_key')
WHERE 1=1
    AND wp_posts.post_type = 'post'
    AND (
        wp_posts.post_status = 'publish'
        OR wp_posts.post_status = 'private'
    )
    AND ( wp_postmeta.post_id IS NULL ) -- expected!
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10;

/*

With the patched meta query builder:


$q = new \WP_Query(array(
    'meta_query'    => array(
        'relation'      => 'OR',
        array(
            'key'           => 'a_key',
            'compare'       => 'NOT EXISTS',
        ),
    )
));

*/

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM wp_posts
    LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'a_key')
WHERE 1=1
    AND wp_posts.post_type = 'post'
    AND (
        wp_posts.post_status = 'publish'
        OR wp_posts.post_status = 'private'
    )
    AND ( wp_postmeta.post_id IS NULL ) -- expected
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10;