How can I reduce the number of database query calls for this custom homepage?

Question

I am working on the design of a blog with the below(just a sample) category hierarchy.

                                          PARENT
                                             |
                     |------------------------------------------------|
                   Food                                           Fashion
                     |                                                |
          |---------------------|                       |---------------------|
      European                Asian                  European               Asian
          |                     |                       |                     |
    |---------|           |-----------|             |--------|           |--------|
Italian    French      Chinese    Japanese       Italian  French     Chinese   Japanese

On the homepage, my client wants to display the three last posts of all the level 4 children, hence:

• The last three posts of Parent>Food>European>Italian
• The last three posts of Parent>Food>European>French
• The last three posts of Parent>Food>Asian>Chinese, etc.
• etc.

My first attempt was like this:

$allPosts = new WP_Query('cat=20, 21, 24, 22, 23, 25, 75, 84, 79, 85, 83, 96, 95, 97, 65, 68, 107, 48, 49&showposts=100&order=ASC');

According to a debugging plugin, 18 queries were called on the page but I realised that such a call makes no sense for what I want because if a category has 2000 posts, the 100 posts this line will retrieve will all be from the same category.

I therefore split the calls, like this:

$italianFood = new WP_Query('cat=20&showposts=3&order=DESC');
$italianFashion = new WP_Query('cat=21&showposts=3&order=DESC');
$frenchFood = new WP_Query('cat=24&showposts=3&order=DESC');
$frenchFashion = new WP_Query('cat=22&showposts=3&order=DESC');

This works but after calling ALL categories, the number of query calls to the database went up to 260!

I will cache the page with a plugin, but do I really have to fire 260+ DB calls to achieve what I want?

Answer 1

You could just write your own query, too. That way, it's one query per category:

global $wpdb;

$query = '
SELECT wpp1.*
FROM ' . $wpdb->posts . ' AS wpp1
LEFT JOIN ' . $wpdb->term_relationships . ' AS wptr1 ON wptr1.object_id = wpp1.ID
WHERE post_type = %s
AND post_status = %s
AND wptr1.term_taxonomy_id = %d
ORDER BY wpp1.post_date DESC
LIMIT %d
';

$query = $wpdb->prepare(
    $query,
    // Args: post type, post status, term taxonomy ID, number of posts
    'post', 'publish', 80, 3
);

$posts = $wpdb->get_results(
    $query,
    ARRAY_A
);