“Issues” loading MANY featured images on a single page (custom template)

by

The answer is eager loading or cache or both.

Eager loading

Have a look to this pseudo-code:

$ids = get_ids_from_a_db_table();
foreach ( $ids as $id ) {
  $row = get_row_from_foreign_table_using( $id );
  echo "Row title for the ID: $id is $row->title";
}

If the number of $ids is n than this simple code runs n+1 queries, the first to load the ids, then one for each id.

You code is even worse, it run 2 queries for every id, one to get attachment post object, one to get attachemnt url (is WordPress, not you, really)

So you run 2n+1 queries…

Eager loading, referred to a db query, is the way you solve the n+1 (or 2n+1) queries problem by querying in only one db request all the data you need (normally is done using proper JOIN + WHERE )

$data = get_data_from_joined_db_tables();
foreach ( $data as $row ) {
  echo "Row title for the ID: $row->id is $row->title";
}

How do that in WordPress? You need two things:

  • add proper join and where clauses to merge the post meta table
  • filter the fields returned by WP_Query to include meta table fields

Both can be done using query filters, here a class extending WP_Query that do that:

class Thumb_Query extends WP_Query {

  protected static $args = array( 'nopaging' => TRUE );

  public function __construct( $args="" ) {
    if ( empty( $args ) ) $args = self::$args; // defaults
    parent::__construct( $args );
  }

  public function get_posts() {
    add_filter('posts_clauses', array( __CLASS__, 'thumb_filters') );
    $results = parent::get_posts();
    remove_filter('posts_clauses', array( __CLASS__, 'thumb_filters') );
    return $this->parse_images( $results );
  }

  public static function thumb_filters( $pieces ) {
    $meta = $GLOBALS['wpdb']->postmeta;
    $posts = $GLOBALS['wpdb']->posts;
    $pieces['fields'] .= ", thumbs.meta_value as thumb_id";
    $pieces['fields'] .= ", imgs.post_title as thumb_title";
    $pieces['fields'] .= ", imgdata.meta_value as thumb_file";
    $pieces['join'] .= "INNER JOIN {$meta} thumbs ON ({$posts}.ID = thumbs.post_id)";
    $pieces['join'] .= " INNER JOIN {$posts} imgs ON (thumbs.meta_value = imgs.ID)";
    $pieces['join'] .= " INNER JOIN {$meta} imgdata ON (imgs.ID = imgdata.post_id)";
    $where = " AND ( thumbs.meta_key = '_thumbnail_id' AND ";
    $where .= " CAST( thumbs.meta_value AS SIGNED ) > 0 )";
    $where .= " AND ( imgdata.meta_key = '_wp_attached_file' )";
    $pieces['where'] .= $where;
    $pieces['groupby'] = " {$posts}.ID";
    return $pieces;
  }

  protected function parse_images( $rows ) {
    $exts = array('jpg', 'jpeg', 'gif', 'png');
    foreach ( $rows as $i => $row ) {
       $urls = wp_extract_urls( $row->post_content );
       $img = FALSE;
       while ( ! $img && ! empty($urls) ) {
         $url = array_shift($urls);
         $ext = strtolower ( pathinfo( $url, PATHINFO_EXTENSION ) );
         if ( in_array( $ext, $exts ) ) $img = $url;
       }
       $rows[$i]->thumb_link = $img ? $img : '#';
    }
  }
}

This class extends WP_Query, so accepts same things of its parent but add some filters to change the eager loading thumnail post id, thumnail post name and thumbnail file path.

So if your query returns 50 posts, to display thumbnails you run 101 (2n+1) queries, my class you runs only 1 query.

In addition before output the results, the class parse all the rows, and extract first image url from post content and remove the filter added.

How to use

Use the standard WP_Query arguments:

$args = array(
  'post_type' => 'portfolio',
  'category_name' => 'featured',
  'posts_per_page' => 50
);

But use Thumb_Query instead of WP_Query

$portfolio = new Thumb_Query( $args );

Then loop and output:

if ( $portfolio->have_posts() ) {
  $u = wp_upload_dir();
  global $post;
  foreach( $portfolio->posts as $post ) { 
    setup_postdata( $post );
    $f="<div class="oneCell"><a href="%s" title="%s">";
    $f .= '<img class="lazy" alt="%s" width="189" src="%s" data-original="%s"/>';
    $f .= '</a></div>';
    $title = esc_attr( get_the_title() );
    printf( $f,
      esc_url( $post->thumb_link ),
      $title, $title,
      esc_url( get_template_directory_uri() . "https://wordpress.stackexchange.com/images/grey.png"  ),
      esc_url( trailingslashit($u['baseurl']) . $post->thumb_file )
    );
  }
  wp_reset_postdata();
}

Inside the loop you have access to all standard post properties, but every post has additional 4 properties:

  • $post->thumb_id the thumbnail attachment id
  • $post->thumb_title the thumbnail attachment title
  • $post->thumb_file the thumbnail file relative to uploads folder, something like '/2014/04/myfile.jpg'
  • $post->thumb_link the full url of the first image in post content or ‘#’ if no image found