Using meta_query on pre_get_posts to exclude a particular meta_key value

Question

I have a simple filter running on the main query on the front page, it should be excluding all posts with a particular meta value (by only including posts who do not have the meta_key set, or those whose meta_key value does not match what we're excluding). But, for whatever reason, it is not behaving as expected.

/**
 * Hide posts from main query on front page.
 *
 * @since  1.0.0
 *
 * @param  object $query WP Query object.
 * @return object        Modified WP Query object.
 */
function wpse_exclude_posts_from_main_query( $query ) {

    // Make sure this only runs on the main query on the front page
    if ( is_front_page() && $query->is_main_query() ) {

        // Exclude posts that have been explicitly set to hidden
        $query->set('meta_query', array(
            'relation' => 'OR',
            // Include posts where the meta key isn't set
            array(
                'key'     => '_wpse_custom_key',
                'value'   => 'asdf', // A value must exist due to https://core.trac.wordpress.org/ticket/23268
                'compare' => 'NOT EXISTS',
            ),
            // Include posts where the meta key isn't explicitly true
            array(
                'key'     => '_wpse_custom_key',
                'value'   => true,
                'compare' => '!=',
            ),
        ) );

    }

}
add_action( 'pre_get_posts', 'wpse_exclude_posts_from_main_query' );

Each half of this meta query work perfectly fine on their own. I can either see all posts where the key does not exist, or all posts where the key does exist and is not true. When used in conjunction, as shown above, I only see posts where the key does exist and is not true (the NOT EXISTS portion is ignored completely).

Here is the SQL being generated (according to the posts_request filter):

SELECT     SQL_CALC_FOUND_ROWS wp_posts.*
FROM       wp_posts
LEFT JOIN  wp_postmeta
           ON (
               wp_posts.ID = wp_postmeta.post_id
               AND wp_postmeta.meta_key = '_wpse_custom_key'
           )
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 = '_wpse_custom_key'
                   AND CAST(mt1.meta_value AS CHAR) != '1'
               )
           )
GROUP BY   wp_posts.ID
ORDER BY   wp_posts.post_date DESC
LIMIT      0, 10

You can see that when it gets down to the meta pieces it is indeed using OR in the WHERE clause, rather than AND as the referenced bug highlights: https://core.trac.wordpress.org/ticket/23268

Hopefully someone can provide some much-needed insight, because I'm completely flummoxed.

Answer 1

Yes, it behaves weird. No, shoot me, I can't get a fix on precisely why and how (stacked joins are probably it).

I say flip it. The only posts you don't want to see are those with true custom key. Query for their IDs separately, feed result into post__not_in, ditch this meta query entirely.

Answer 2

I'm siding with the folks on twitter who suspected the composited SQL as being the issue. I've just written a different function to run my own SQL at the posts_clauses, instead of using pre_get_posts, hook and the expected posts are correctly returned.

Here's an example following the same requirements as above:

/**
 * Hide posts from main query on front page.
 *
 * @since  1.0.0
 *
 * @param  object $query WP Query object.
 * @return object        Modified WP Query object.
 */
function wpse_exclude_posts_clauses( $pieces, $query ) {

    // Make sure this only runs on the main query on the front page
    if ( is_front_page() && $query->is_main_query() ) {
        $pieces['join'] = "
            LEFT JOIN  $wpdb->postmeta as hidden_meta
                       ON (
                           $wpdb->posts.ID = hidden_meta.post_id
                           AND hidden_meta.meta_key = '_wpse_custom_key'
                       )
            ";
        $pieces['where'] = "
            AND (
                hidden_meta.post_id IS NULL
                OR CAST(hidden_meta.meta_value AS CHAR) != '1'
            )
            ";
    }

    return $pieces;
}
add_filter( 'posts_clauses', 'wpse_exclude_posts_clauses', 10, 2 );

Answer 3

I think your problem is using the true boolean as the value to exclude. I changed the exclude value to a string, 'asdf' and changed the dummy string and it worked.

The query SQL was copy and pasted from a dump of the global $wp_query object. The database has 5 posts as can be seen from the 1st query. 3 of the post have the exclude meta key and 1 of them contains the exclude meta value.

mysql> SELECT ID FROM wpomni_posts WHERE post_type ='post' AND post_status = 'publish';
+------+
| ID   |
+------+
|    1 |
| 5900 |
| 5904 |
| 5908 |
| 5925 |
+------+
5 rows in set (0.00 sec)
    mysql> SELECT SQL_CALC_FOUND_ROWS  wpomni_posts.ID FROM wpomni_posts  
        -> LEFT JOIN wpomni_postmeta 
        -> ON (wpomni_posts.ID = wpomni_postmeta.post_id 
        -> AND wpomni_postmeta.meta_key = '_wpse_custom_key')
        -> INNER JOIN wpomni_postmeta AS mt1 
        -> ON (wpomni_posts.ID = mt1.post_id) 
        -> WHERE 1=1  
        -> AND wpomni_posts.post_type = 'post' 
        -> AND (wpomni_posts.post_status = 'publish' 
        -> OR wpomni_posts.post_status = 'private') 
        -> AND ( wpomni_postmeta.post_id IS NULL
        -> OR  (mt1.meta_key = '_wpse_custom_key' 
        -> AND CAST(mt1.meta_value AS CHAR) != 'asdf') ) 
        -> GROUP BY wpomni_posts.ID 
        -> ORDER BY wpomni_posts.post_date DESC LIMIT 0, 18;
    +------+
    | ID   |
    +------+
    | 5925 |
    | 5908 |
    | 5900 |
    |    1 |
    +------+
    4 rows in set (0.00 sec)

    mysql> SELECT * FROM wpomni_postmeta WHERE meta_key = '_wpse_custom_key';
    +---------+---------+------------------+----------------+
    | meta_id | post_id | meta_key         | meta_value     |
    +---------+---------+------------------+----------------+
    |  137033 |    5908 | _wpse_custom_key | 1              |
    |  137034 |    5904 | _wpse_custom_key | asdf           |
    |  137042 |    5925 | _wpse_custom_key | what the blank |
    +---------+---------+------------------+----------------+
    3 rows in set (0.00 sec)