NOT EXISTS meta query with OR relation

[timfield]

With this meta query ( which is trying to exclude posts that have the app_exclude checkbox checked, without excluding posts that don't have it set at all )

                 $query['meta_query'] = array(
                    'relation' => 'OR',
                    array(
                        'key' => 'app_exclude',
                        'compare' => 'NOT EXISTS'
                    ),
                    array(
                        'key' => 'app_exclude',
                        'compare' => '!=',
                        'value' => '1'
                    ),
                );

I'd expect / hope to get this sql.

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 = 'app_exclude')
        INNER JOIN
    wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE
    1 = 1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.post_id IS NULL 
    OR (mt1.meta_key = 'app_exclude' AND CAST(mt1.meta_value AS CHAR) != '1'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0 , 6

but I get this SQL

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 = 'app_exclude')
        INNER JOIN
    wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE
    1 = 1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') 
    AND (wp_postmeta.post_id IS NULL AND (mt1.meta_key = 'app_exclude' AND CAST(mt1.meta_value AS CHAR) != '1'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0 , 6

Note the... (wp_postmeta.post_id IS NULL AND (mt1.meta_key = 'app_exclude' AND CAST(mt1.meta_value AS CHAR) != '1'))

[hereswhatidid]

#23294 was marked as a duplicate.

[panbolec]

It is possible to fool WordPress a little :) by asigning anything to 'value' of EXISTS/NOT EXISTS subquery. This workaround works (and may be even funny :-)

                 $query['meta_query'] = array(
                    'relation' => 'OR',
                    array(
                        'key' => 'app_exclude',
                        'compare' => 'NOT EXISTS'
                        'value'   => 'completely'
                    ),
                    array(
                        'key' => 'app_exclude',
                        'compare' => '!=',
                        'value' => '1'
                    ),
                );

The problem is in meta.php, lines:

		if ( 'OR' == $this->relation ) {
			foreach ( $this->queries as $k => $q ) {
				if ( ! isset( $q['value'] ) && ! empty( $q['key'] ) )
					$key_only_queries[$k] = $q;
				else
					$queries[$k] = $q;
			}
		} else {
			$queries = $this->queries;
		}

Following condition:

      if ( ! isset( $q['value'] ) && ! empty( $q['key'] ) )

is the source of the problem.

[SergeyBiryukov]

Related: [22103] (for #19729).

[chrisguitarguy]

I also ran into this issue. NOT EXISTS depends on a value when using the OR relation when it obviously shouldn't.

Some test cases: ​https://gist.github.com/chrisguitarguy/5116384

I was able to get this fixed by adding some more conditionals the lines in wp-includes/meta.php that panbolec pointed out. Diff attached.

[chrisguitarguy]

Make WP_Meta_Query respect NOT EXISTS queries without values

[tomauger]

This is silly. ​Codex updated for now.

[sboisvert]

I just made a change to the codex since it implied that this effected EXIST as well as NOT EXIST. This doesn't seem to be the case on 3.6.1.

If you put in a value of any kind when using EXIST you get the behaviour of "=" not the behaviour defined previously defined as being the behaviour of the bug that affects NOT EXISTS.

so to clarify on 3.6.1
this:

array(
'key' => 'disclosure_regular_travel',
'compare' => 'EXISTS'
),

works as expected.
While the following:

array(
'key' => 'disclosure_regular_travel',
'value' => 'non empty value',
'compare' => 'EXISTS'
),

Causes behaviour similar to "=" compare. (Which is not in itself bad, just not what was written up in the codex and referenced this bug report)

[knutsp]

Ran into this bug today. Please fix for 3.9.

[knutsp]

No, I did not change keywords. Trac bug?

The from keywords are comma separated, the to keywords are not. Inconsistent. Well, this belongs in another trac.

[nacin]

Let's make sure we get this fixed for 3.9. It results in a funny hack when using meta queries with custom headers. See #21785.

[wonderboymusic]

In 27689:

When using meta_query in a WP_Query, passing NOT EXISTS or '' to compare should not require value to be set. The resulting SQL should then produce the appropriate OR clause for existence of non-existence after passing the query to the $key_only_queries stack internally.

Adds unit tests.

Props chrisguitarguy, for the original patch.
Fixes #23268.

[o----o]

the problem persists in WP.4.1

	$args = array(
		'post_type' => $page_type,
		'posts_per_page' => -1,
		'author' => $user_ID,
		'post_status' => array('publish', 'draft'),
		'relation' => 'OR',
		'meta_query' => array(
	        array(
	            'key' => 'event_boolean',
	            'compare' => 'NOT EXISTS',
	            'value'   => 'completely'
	        ),
	        array(
	            'key' => 'event_boolean',
	            'compare' => '!=',
	            'value' => '1'
	        ),
        )
	);

one or the other meta comparison returns result, but when I use both no result is returned at all.
Truth is that the value for "NON EXISTS" is not needed but pairing the meta arrays cancel out the functionality.
(in my case I have one post with "event_boolean" empty, and the other with 0, so this should return 2 posts but none is returned)

[boonebgorges]

@o----o - At a glance, it looks like the problem is that your 'relation' => 'OR' parameter is not within the meta_query like it should be, which would make the query relation default to AND. Try moving it inside of the meta_query array.

If you continue to have problems, please open a new ticket with a reference back to this one - we like to avoid reopening tickets that have been fixed against a closed milestone.

[o----o]

hell yea,
sorry and thanks