[wp-trac] [WordPress Trac] #62401: WP_Query add extra JOINs after calling get_posts() with category__in in query arguments

[WordPress Trac]

#62401: WP_Query add extra JOINs after calling get_posts() with category__in in
query arguments
--------------------------+-----------------------------
 Reporter:  elancerteam   |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  6.6.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 I'm working with a custom query in WordPress to retrieve posts of a
 specific type and category. Here's the setup:


 {{{#!php
 <?php
 $args = [
     'post_type'      => 'post',
     'post_status'    => 'publish',
     'posts_per_page' => -1
 ];

 $args['category__in'] = array_map('intval', $selected_categories);

 $articles_query = new WP_Query($args);

 echo '<pre>';
 // Dumping SQL query before calling get_posts()
 var_dump($articles_query->request);

 $posts = $articles_query->get_posts();

 // Dumping SQL query after calling get_posts()
 var_dump($articles_query->request);
 echo '</pre>';
 }}}


 The `$selected_categories` array contains IDs of categories I want to
 filter by. My expectation was that the SQL query generated by WP_Query
 would be consistent. However, I noticed a difference in the SQL query
 output before and after calling `get_posts()`.

 When calling get_posts() only the posts with the first category returns.

 SQL Query before `get_posts()`:


 {{{#!sql
 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
 FROM wp_posts
 LEFT JOIN wp_term_relationships ON (wp_posts.ID =
 wp_term_relationships.object_id)
 WHERE 1=1
   AND (wp_term_relationships.term_taxonomy_id IN (1,17))
   AND wp_posts.post_type = 'post'
   AND ((wp_posts.post_status = 'publish'))
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_date DESC
 LIMIT 0, 10
 }}}



 SQL Query after `get_posts()`:

 {{{#!sql
 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
 FROM wp_posts
 LEFT JOIN wp_term_relationships ON (wp_posts.ID =
 wp_term_relationships.object_id)
 LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id)
 LEFT JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id)
 WHERE 1=1
   AND (wp_term_relationships.term_taxonomy_id IN (1,17)
   AND tt1.term_taxonomy_id IN (1)
   AND tt2.term_taxonomy_id IN (1))
   AND wp_posts.post_type = 'post'
   AND ((wp_posts.post_status = 'publish'))
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_date DESC
 LIMIT 0, 10
 }}}

 Why is WP_Query adding additional LEFT JOIN clauses (with aliases `tt1`
 and `tt2`) after calling `get_posts()`? I only expect it to filter by the
 categories provided in `$selected_categories`. I suspect it might be
 related to how `category__in` is handled, but I’m not sure why these extra
 joins are introduced.

 I have disabled all plugins and I don't use any pre_get_posts filter or
 anything.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/62401>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform