0

Include Taxonomy Terms in WordPress get_posts() for Efficient Lookups

Posted October 9th, 2013 in Database, PHP

Sometimes in WordPress you want to include associated taxonomy terms with your get_posts() or WP_Query lookups. Doing so can have a noticeable impact on performance. Not to mention it’s much cleaner code-wise.

Here’s an example. I wanted to group image attachments into genre - action, adventure etc. and display that information on my sites frontend. Firstly I added my genre taxonomy to the attachment post type:

1
2
3
4
5
6
7
8
9
register_taxonomy('genre', 'attachment', array(
	'label' => 'Genres',
	'rewrite' => array( 'slug' => 'genre' ),
	'hierarchical' => true,
	'capabilities' => array(
		'assign_terms' => 'edit_posts',
		'edit_terms' => 'publish_posts'
	)
));

I now needed to display that information on the images associated post page (single.php) on the frontend.

 

The dumb way

On my first attempt I looped through the images, grabbing the associated genres and displaying them:

1
2
3
4
5
6
7
8
9
10
$images = get_posts(array(
	'post_parent' => get_the_ID(),
	'post_type' => 'attachment',
	'numberposts' => -1,
	'orderby'        => 'title',
	'order'           => 'ASC',
	'post_mime_type' => 'image',
));
foreach ( $images as $image )
	echo $image->post_title . ': ' . strip_tags(get_the_term_list($image->ID, 'genre', '', ', ', ''));

My image: Action, Adventure

This resulted in one unnecessary database call per image which could add up quickly. I needed a better way.

 

A smarter approach

WP_Query (which get_posts() uses to retrieve its results) supports a filter posts_clauses that lets you modify various parts of the SQL query it is about to perform. I used this to JOIN the taxonomy tables on and include the genre name(s) in the result array.

Firstly the filter (only works if you drop it in functions.php):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/**
 * Include 'size' name in image attachment lookups. This only applies if
 * INCLUDE_GENRES global variable flag is set - otherwise it will affect
 * the_loop
 *
 * @param array $pieces Includes where, groupby, join, orderby, distinct, fields, limits
 *
 * @return array $pieces
 */
add_filter( 'posts_clauses', function( $pieces )
{
	global $wpdb, $INCLUDE_SIZE;
 
	if ( empty($INCLUDE_GENRES) )
		return $pieces;
 
	$pieces['join'] .= " LEFT JOIN $wpdb->term_relationships iqctr ON iqctr.object_id=$wpdb->posts.ID
						 LEFT JOIN $wpdb->term_taxonomy iqctt ON iqctt.term_taxonomy_id=iqctr.term_taxonomy_id AND iqctt.taxonomy='genre'
						 LEFT JOIN $wpdb->terms iqct ON iqct.term_id=iqctt.term_id";
	$pieces['fields'] .= ",GROUP_CONCAT(iqct.name SEPARATOR ', ') AS genres";
 
	return $pieces;
}, 10, 1 );

You’ll notice the $INCLUDE_GENRES variable. This is required because without it the filter will apply to all the_loop and other queries. We only want it to apply for one specific query. Now how to use it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$INCLUDE_GENRES = true;
$images = get_posts(array(
	'post_parent' => get_the_ID(),
	'post_type' => 'attachment',
	'numberposts' => -1,
	'orderby'        => 'title',
	'order'           => 'ASC',
	'post_mime_type' => 'image',
	'suppress_filters' => false,
));
$INCLUDE_GENRES = false;
 
foreach ( $images as $image )
	echo $image->post_title . ': ' . $image->genres;

My image: Action, Adventure

Perfect!