[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           |  Resolution:
 Keywords:                   |
-----------------------------+-----------------------------
Description changed by scribu:

Old description:

> 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.
>
> Final query:
>
> {{{
> SELECT *
> FROM wp_posts
> WHERE post_type = 'city'
> AND post_parent IN (1, 2, 3, ...)
> }}}
>
> 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:
>
> 2) Get all the cities belonging to that country:
>
> {{{
> $cities = get_posts( array(
>   'post_type' => 'city',
>   'post_parent__in' => $country_ids
> ) );
> }}}
>
> Final query:
>
> {{{
> SELECT *
> FROM wp_posts
> WHERE post_type = 'city'
> AND post_parent IN (
>   SELECT ID
>   FROM wp_posts
>   WHERE post_type = 'country'
>   INNER JOIN wp_terms ...
> )
> }}}

New description:

 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.

 Final query:

 {{{
 SELECT *
 FROM wp_posts
 WHERE post_type = 'city'
 AND post_parent IN (1, 2, 3, ...)
 }}}

 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'
 ) );
 }}}

 2) Get all the cities belonging to that country:

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

 $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:

 Final query:

 {{{
 SELECT *
 FROM wp_posts
 WHERE post_type = 'city'
 AND post_parent IN (
   SELECT ID
   FROM wp_posts
   WHERE post_type = 'country'
   INNER JOIN wp_terms ...
 )
 }}}

--

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