Extending WP_Query

I’ve had the pleasure of contributing a couple of WordPress (WP) snippets to Elliot Richmond’s WP Snippets Til Christmas advent calendar this holiday season. Today’s snippet happens to be one of my contributions and I’d like to expand on it a bit more here.

Let’s say you have a custom post type book and a custom taxonomy book_category. You want to list all the book categories in alphabetical order and list the books under each one, like this:

Business

• Blue Ocean Strategy
• The Innovator’s Dilemma

Fiction

• Dracula
• Frankenstein
• The Hobbit
• Winter of the World

History

• With the Old Breed

Again, notice that both categories and book titles are sorted alphabetically. Unfortunately, there is no easy way to do this using a single call to WP_Query. There is no option to sort by taxonomy. Instead, most developers would get all the categories, then loop through them, calling WP_Query for each one. That would be ok for the tiny bit of data we have above, but let’s pretend we are outputting a printable catalog of books and we have hundreds of categories and thousands of books. We would definitely want to do this more efficiently, with just one call to WP_Query. So, we need to make use of the filters in WP_Query. In the past, I would have done something like this:

function my_posts_fields( $sql ) {
	global $wpdb;
	return $sql . ", $wpdb->terms.name AS 'book_category'";
}

function my_posts_join( $sql ) {
	global $wpdb;
	return $sql . "
		INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) 
		INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) 
		INNER JOIN $wpdb->terms ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) 
	";
}

function my_posts_where( $sql ) {
	global $wpdb;
	return $sql . " AND $wpdb->term_taxonomy.taxonomy = 'book_category'";
}

function my_posts_orderby( $sql ) {
	global $wpdb;
	return "$wpdb->terms.name ASC, $wpdb->posts.post_title ASC";
}

add_filter( 'posts_fields', 'my_posts_fields' );
add_filter( 'posts_join', 'my_posts_join' );
add_filter( 'posts_where', 'my_posts_where' );
add_filter( 'posts_orderby', 'my_posts_orderby' );

$r = new WP_Query( array(
	'post_type' => 'book',
	'posts_per_page' => -1,
	// Optimize query for no paging
	'no_found_rows' => true,
	'update_post_term_cache' => false,
	'update_post_meta_cache' => false
) );

// Make sure these filters don't affect any other queries
remove_filter( 'posts_fields', 'my_posts_fields' );
remove_filter( 'posts_join', 'my_posts_join' );
remove_filter( 'posts_where', 'my_posts_where' );
remove_filter( 'posts_orderby', 'my_posts_orderby' );

Look at all those single purpose functions in the global scope. Gross. We can tidy this up by wrapping it in a class that extends WP_Query.

class My_Book_Query extends WP_Query {

	function __construct( $args = array() ) {
		// Force these args
		$args = array_merge( $args, array(
			'post_type' => 'book',
			'posts_per_page' => -1,  // Turn off paging
			'no_found_rows' => true, // Optimize query for no paging
			'update_post_term_cache' => false,
			'update_post_meta_cache' => false
		) );

		add_filter( 'posts_fields', array( $this, 'posts_fields' ) );
		add_filter( 'posts_join', array( $this, 'posts_join' ) );
		add_filter( 'posts_where', array( $this, 'posts_where' ) );
		add_filter( 'posts_orderby', array( $this, 'posts_orderby' ) );

		parent::__construct( $args );

		// Make sure these filters don't affect any other queries
		remove_filter( 'posts_fields', array( $this, 'posts_fields' ) );
		remove_filter( 'posts_join', array( $this, 'posts_join' ) );
		remove_filter( 'posts_where', array( $this, 'posts_where' ) );
		remove_filter( 'posts_orderby', array( $this, 'posts_orderby' ) );
	}

	function posts_fields( $sql ) {
		global $wpdb;
		return $sql . ", $wpdb->terms.name AS 'book_category'";
	}

	function posts_join( $sql ) {
		global $wpdb;
		return $sql . "
			INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) 
			INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) 
			INNER JOIN $wpdb->terms ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) 
		";
	}

	function posts_where( $sql ) {
		global $wpdb;
		return $sql . " AND $wpdb->term_taxonomy.taxonomy = 'book_category'";
	}

	function posts_orderby( $sql ) {
		global $wpdb;
		return "$wpdb->terms.name ASC, $wpdb->posts.post_title ASC";
	}

}

Now all your custom query code is neatly contained in this class file that you can include when necessary. And because we’re extending WP_Query, you can use the usual functions in your loops:

$query = new My_Book_Query();

if ( $query->have_posts() ) :

	while ( $query->have_posts() ) :

		$query->the_post();
		...

	endwhile;

	wp_reset_postdata();

endif;