Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
A few people have commented via twitter to suggest that the JOIN statements seem to be defeating each other. To me, that doesn't appear to be true... but I'm not the brightest when it comes to SQL. –  Brian Richards Jan 9 at 19:23
    
Do all posts that have no _wpse_custom_key meta value at least one other meta value? Otherwise the second, inner join won't find them, because it requires at least one meta value, and it can't be _wpse_custom_key. –  Jan Fabry Jan 9 at 19:33
    
Please move solution part to an answer, it's more in line with site's mechanics. –  Rarst Jan 9 at 19:59
    
I would love to write it as an answer. In fact, that's how I started, and then SE stopped me cold: d.pr/i/CELU I'll be sure to post it as an answer once my roadblock has been lifted. –  Brian Richards Jan 9 at 20:11
add comment

3 Answers

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.

share|improve this answer
    
Just edited the question above with a different approach. Wouldn't running a double query (the original, plus the extra one to fetch the post__not_in array) be less performant? Even if caching that second query via a transient that gets built/busted on post_save could become taxing on site admins as the site grows to multiple thousands of posts. Right? –  Brian Richards Jan 9 at 19:52
    
@BrianRichards my not very DBA-educated opinion would be that two selects are way less likely to go horribly wrong than two joins :) There is always profiling to see if it's being an issue. –  Rarst Jan 9 at 19:58
add comment

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 );
share|improve this answer
add comment

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)
share|improve this answer
    
Negative, even changing it to a string I cannot see posts which have no custom meta, for the reason that Jan highlights in his comment –  Brian Richards Jan 9 at 20:14
add comment

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.