How to avoid multiple querying inside a foreach using a single $wpdb?

Question

Okey, this is going to be big. As many of you know, in encouraged to not querying the database multiple times inside a loop, but for this task is very difficult not to do so - at least for me. Let's get going.

In Wordpress I have six categories of posts. A page has to call 3 of the most recent posts of each category and show them in order (not mixed).

To complete that task, first I ask the DB for the categories. Then, foreach category I run a query to retrieve the last 3 post under that category name with $wp_query object - that makes like 6 queries. Categories can change and of course I don't want to be called every time these do to change the code.

I tried to use a one-time MySQL (5.5) query using $wpdb to retrieve all these posts and variables:

SELECT
    DISTINCT $wpdb->posts.ID,
    $wpdb->posts.post_title,
    $wpdb->posts.post_excerpt,
    $wpdb->posts.comment_count,
    $wpdb->posts.guid,
    $wpdb->posts.post_status,
    $wpdb->posts.post_name,
    $wpdb->posts.post_date,
    $wpdb->posts.post_type,
    $wpdb->terms.slug
FROM (
      SELECT
        $wpdb->posts.ID,
        $wpdb->posts.post_title,
        $wpdb->posts.post_excerpt,
        $wpdb->posts.comment_count,
        $wpdb->posts.guid,
        $wpdb->posts.post_status,
        $wpdb->posts.post_name,
        $wpdb->posts.post_date,
        $wpdb->posts.post_type,
        $wpdb->terms.slug,
        @num := if(@group = $wpdb->terms.slug, @num + 1, 1) as row_number,
        @group := $wpdb->terms.slug
      FROM $wpdb->posts
      LEFT JOIN $wpdb->term_relationships
             ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
      LEFT JOIN $wpdb->term_taxonomy
             ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
      LEFT JOIN $wpdb->terms
             ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
      ORDER BY $wpdb->terms.slug ASC, $wpdb->posts.post_date DESC
    ) as $wpdb->posts

LEFT JOIN $wpdb->term_relationships
       ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy
       ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms
       ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)

WHERE $wpdb->posts.post_type = 'site_type'
  AND $wpdb->posts.post_status = 'publish'
  AND $wpdb->term_taxonomy.taxonomy = 'site_category'
  AND $wpdb->terms.slug IN ('cars', 'planes', 'motorcycles', '4x4', 'bicycles', 'jetpacks')
  AND row_number <= 5

ORDER BY $wpdb->terms.slug ASC, $wpdb->posts.post_date DESC
LIMIT $total

Then, after that, I split the resulting array depending on the slug. The result is the problem.

In PHPMyAdmin I execute this query and row_number doesn't sum, every row gets the value 1. That makes every "group of post under X slug" not have a limit, row_number <= 5 doesn't work. In the custom page, a the first category (with 7 posts) shows more than 3.

The Alpha · Answer 1 · 2012-07-05T06:57:52.877

If I understand your question then I would tell you to use query_posts

<?php 
    $categories=array('cat1', 'cat2', 'cat3', 'cat4', 'cat5', 'cat6');
    foreach($categories as $cat)
    {
        query_posts('category_name='.$cat.'&showposts=3&orderby=date&oredr=DESC');
        while (have_posts()) : the_post();
        ?>
            <li><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></li>
        <?php
        endwhile;
        wp_reset_query();
    }
?>

Just replace cat1, cat2......cat6 in the $categories array with your real category name.

This snippet will show latest 3 posts (just the title here as an example) from each category from the the $categories array we've assigned earlier.

You understandit well, but, is performance-wise to call query_posts 6 times? — DarkGhostHunter, Jul 05 '12 at 17:14

score 0 · Answer 2 · answered Jul 05 '12 at 18:54

After trying again with MySQL variables, and failing to do so, I saw another option: using UNION ALL - has stated here, you SELECT N-number of each group and then you unite them all, in simple words. Performance-wise, at least in my configuration it performs almost the same than using variables.

So, I construct a foreach category and add the query for that group.

    $p = 1;
    $numb = count($products);
    global $wpdb;
foreach($categories as $product) {
    $query .=  "(SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->posts.post_excerpt, $wpdb->posts.comment_count, $wpdb->posts.guid,
        $wpdb->posts.post_status,
        $wpdb->posts.post_name,
        $wpdb->posts.post_date,
        $wpdb->posts.post_type,
        $wpdb->terms.slug
    FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships
        ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy
        ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms
        ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
    WHERE $wpdb->posts.post_type = 'pb_review'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->term_taxonomy.taxonomy = 'product_reviews'
    AND $wpdb->terms.slug IN ('$product->slug')
    ORDER BY $wpdb->posts.post_date DESC
    LIMIT 3)" . ($p == $numb ? '' : ' UNION ALL ');
    $p++;
}

After the $query is constructed, I pass it as a variable to $wpdb and voilà:

$productos_query = $wpdb->get_results($query, OBJECT);

Why the variables didn't work, adding +1 to row_number? My guess is because I was using LEFT JOIN, but be my guest.

How to avoid multiple querying inside a foreach using a single $wpdb?

2 Answers2