Make WordPress Core

Opened 13 years ago

Last modified 5 years ago

#16910 new feature request

Lazy evaluation for WP_Query

Reported by: scribu's profile scribu Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Query Keywords: needs-patch
Focuses: Cc:

Description (last modified by scribu)

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 ...
)

Change History (7)

#1 @scribu
13 years ago

  • Description modified (diff)
  • Keywords 2nd-opinion removed

#2 @scribu
13 years ago

  • Description modified (diff)

#3 @scribu
13 years ago

  • Description modified (diff)

#4 @scribu
12 years ago

  • Description modified (diff)

#5 @scribu
12 years ago

  • Component changed from General to Query
  • Summary changed from Direct subqueries to Lazy evaluation for WP_Query

#6 @wonderboymusic
11 years ago

  • Keywords needs-patch added

I yearn for things like this

#7 @chriscct7
9 years ago

@wonderboymusic still want to take a stab at this?

Note: See TracTickets for help on using tickets.