[wp-trac] [WordPress Trac] #16910: Direct subqueries

[WordPress Trac]

#16910: Direct subqueries
-----------------------------+----------------------------
 Reporter:  scribu           |      Owner:
     Type:  feature request  |     Status:  new
 Priority:  normal           |  Milestone:  Future Release
Component:  General          |    Version:
 Severity:  normal           |   Keywords:  2nd-opinion
-----------------------------+----------------------------
 Suppose we have a 'city' post type, which is associated with a 'country'
 post type, such that each 'city' post has a 'country' parent post.

 Then the 'country' CPT has a 'language' taxonomy associated to it.

 Now, let's say we want to find all the cities which speak a certain
 language.

 Let's assume we already have the 'post_parent__in' query var: #13927

 We could construct a two-step query, like this:

 1) Get all the countries with that language:

 {{{
 $country_ids = get_posts( array(
   'fields' => 'ids',
   'post_type' => 'country',
   'language' => 'french',
   'nopaging' => true
 ) );
 }}}

 2) Get all the cities belonging to that country:

 {{{
 $cities = get_posts( array(
   'post_type' => 'city',
   'post_parent__in' => $country_ids
 ) );
 }}}

 No custom SQL queries; fantastic!

 But, if you have many many countries (not a good example, I know), you
 waste a lot of time passing the IDs back and forth from PHP to SQL.

 It would be a lot more scalable to put the first query into the second,
 directly, as a subquery.

 So, it would now look like this:

 1) Get all the countries with that language:

 {{{
 $country_ids = get_posts( array(
   'fields' => 'ids',
   'post_type' => 'country',
   'language' => 'french',
   'nopaging' => true,

   'lazy' => true'
 ) );
 }}}

 $country_ids would now be a WP_Lazy_Query object, which would just contain
 the subquery SQL. It would be appended to the second query, when needed.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/16910>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software