Blog

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;
  • pbaylies

    I did something recently like this, but a bit lazier… my class would have looked more like this; not exactly the same, and I’m skipping initialization and doing a bit of hand-waving but… well, you’ll get the idea:

    class My_Book_Query extends WP_Query {
    		protected $where;
    		protected $groupby;
    		protected $join;
    		protected $orderby;
    		protected $fields;
    
    	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
    		) );
    
    		foreach( Array( 'groupby', 'join', 'orderby', 'fields' ) as $filter )
    			add_filter( "posts_$filter", Array( $this, $filter ) );
    
    		parent::__construct( $args );
    
    		// Make sure these filters don't affect any other queries
    		foreach( Array( 'groupby', 'join', 'orderby', 'fields' ) as $filter )
    			remove_filter( "posts_$filter", Array( $this, $filter ) );
    	}
    		
    		function __call( $name, $arguments ) {
    			if ( !empty( $this->$name ) ) return $this->$name . ' ' . $arguments[0];
    			if ( !empty( $arguments ) ) return $arguments[0];
    			return false;
    		}
    }
    
  • @pbaylies Nice, I like the loops. Much more concise.

  • Pingback: Classing up your queries : Post Status()

  • Great idea to extend the core class. Two things tough: First, I’d prefix the methods to not collide with core code in case such methods would be introduced. Second, I’d trigger the filter removal from within the methods themselves.

  • Gomy

    Hey,

    I want to list posts (buildin posts) sorted by percentage discount value between two meta key values.
    meta_key = price
    meta_key = lowprice
    discount = (price – lowprice) / price * 100)
    I want to order posts by this result.

    Any help?

  • Pingback: In the Ring with ClassiPress » WP Theme Tutorial()

  • Pingback: Filling in WP_Query when there aren't enough posts()

  • Thanks, this is a great tip on creating a custom class for this, I’ve been doing it in a much less efficient way. One thing your post doesn’t include is how to include the category titles as headings – and only display them only once per category.

    Again I’m sure this is not the most efficient way, but it’s working for me, so here goes…

    if ( $query->have_posts() ) :
    $listedCats = array();
    while ( $query->have_posts() ) : $query->the_post();
    $category = get_the_terms(get_the_ID(), 'locations');
    //Just want the first category in case there's more than one
    $category = reset($category);
    $category = $category->name;
    if ( !in_array($category, $listedCats )) echo '' . $category . '';
    $listedCats[] = $category;
    //Other loop stuff;
    endwhile;
    wp_reset_postdata();
    endif;

  • Pingback: Getting Started with WordPress Development - WP Theme Tutorial()

  • Pingback: WordPress query_var by domain | DL-UAT()

Comments Elsewhere