SQL Statement generated by WP_Query not producing expected results

Question

I am trying to alter WP_Query on the pre_get_posts action hook, for filtering the posts returned, depending on the built-in category and post_tag taxonomies.

I would like that the only posts returned by the query have either both taxonomies not registered at all, or no term is set for both taxonomies for the post, or, last case, both taxonomies have a specific value.

It is stated in the Codex WP_Query page that for this kind of purpose "Nested Taxonomies" should be used (see here).

Though in my particular case it is not working - here is my code:

// In a function hooked on 'pre_get_posts' action
$query->set('post_type', array('post','wpgdacces_acces', 'page'));
$query_tax_param = array(
    'relation' => 'OR',
    array( 
        'relation' => 'AND',
        array(  'taxonomy' => 'category',
                'field' => 'slug',
                'terms' => array( 'private' ),
                'operator' => 'IN'),
        array( 'taxonomy' => 'post_tag',
                'field' => 'slug',
                'terms' => array('private'),
                'operator' => 'IN')
    ),
    array(
        'relation' => 'AND',
        array( 'taxonomy' => 'category',
               'field' => 'slug',
               'operator' => 'NOT EXISTS'
               ),
        array( 'taxonomy' => 'post_tag',
               'field' => 'slug',
               'operator' => 'NOT EXISTS'
               ) 
    )
);

Before that, I have registered the custom post type 'wpgdacces_acces', and also registered both built-in taxonomies category and post_tag for the page post type.

I am testing the query on a local environment, on a WP 4.3 installation with the default theme. On this setup, I have 3 standard posts, of which only one has the 'private' term for both Category and Tags. The 2 remaining posts have "Unclassified" as Category term. I also have 3 pages, for which no term is set for both taxonomies. And in my Custom Post Type 'wpgdacces_acces', for which both taxonomies have not been registered at all, I have 2 posts.

So on the home page, the query is altered for showing the 3 post types post, page and 'wpgdacces_acces' (the first line of code in the block here above). And therefore I should see 1 post + 3 pages + 2 wpgdacces_acces posts. But the only element which is showing off is the standard post with both Category and Tags set to 'private'.

Really odd: when I modify and run the query with the two conditions separately (altering the query for getting only posts with taxonomies not set, on one side, and altering the query for getting posts with taxonomies set to private, on the other side) it is perfectly working.

This lets me think that the SQL statement that is generated by WP_Query after pre_get_posts might be wrong somewhere. Here it is, printed with echo $GLOBALS['wp_query']->request; on the 'wp_footer' hook :

SELECT SQL_CALC_FOUND_ROWS  wptom_posts.ID 
FROM wptom_posts  
INNER JOIN wptom_term_relationships 
ON (wptom_posts.ID = wptom_term_relationships.object_id)  
INNER JOIN wptom_term_relationships 
AS tt1 
ON (wptom_posts.ID = tt1.object_id) 
WHERE 1=1  
AND ( 
   ( 
    wptom_term_relationships.term_taxonomy_id IN (7) 
    AND 
    tt1.term_taxonomy_id IN (8)
  ) 
   OR 
  ( 
    NOT EXISTS (
            SELECT 1
            FROM wptom_term_relationships
            INNER JOIN wptom_term_taxonomy
            ON wptom_term_taxonomy.term_taxonomy_id = wptom_term_relationships.term_taxonomy_id
            WHERE wptom_term_taxonomy.taxonomy = 'category'
            AND wptom_term_relationships.object_id = wptom_posts.ID
        ) 
    AND 
    NOT EXISTS (
            SELECT 1
            FROM wptom_term_relationships
            INNER JOIN wptom_term_taxonomy
            ON wptom_term_taxonomy.term_taxonomy_id = 
            wptom_term_relationships.term_taxonomy_id
            WHERE wptom_term_taxonomy.taxonomy = 'post_tag'
            AND wptom_term_relationships.object_id = wptom_posts.ID
        )
  )
) AND wptom_posts.post_type 
  IN ('post', 'wpgdacces_acces', 'page') 
  AND (wptom_posts.post_status = 'publish' 
  OR wptom_posts.post_author = 1 
  AND wptom_posts.post_status = 'private') 
  GROUP BY wptom_posts.ID 
  ORDER BY wptom_posts.post_date 
  DESC LIMIT 0, 10

Do you see anything wrong in the above SQL statement? I would like to find out what is wrong in that SQL statement. If I know what is wrong, maybe I will be able to use one of the WP_Query filters provided to modify the query after pre_get_posts, but before it is actually executed.

Answer 1

It looks like this section:

AND (wptom_posts.post_status = 'publish' 
OR wptom_posts.post_author = 1 
AND wptom_posts.post_status = 'private') 

is not tied in at all to the two different query conditions...

Perhaps the original code should be three conditions for part 1:

array( 
    'relation' => 'AND',
    array(  'taxonomy' => 'category',
            'field' => 'slug',
            'operator' => 'IN'),
    array( 'taxonomy' => 'post_tag',
            'field' => 'slug',
            'operator' => 'IN'),
    array( 'terms' => array('private') )
),

I am guessing a bit here without testing it, but that's what I'd try.