Benefits of posts_clauses filter or how to easily hook into WP_Query

In this post we’ll look at how we can utilize the posts_clauses filter to inject SQL into the WP_Query. But before we get into more details, a bit of background on how I came upon this filter. I have a personal plugin that I’ve created in 2015. And it exports all WooCommerce products into an XML format file, which could later be imported into various online market places.

Quick introduction

As with any plugin on the WordPress repository, developers will frequently get asked to add new features into their plugins. And I am not an exception – often I get requests to integrate with different third-party plugins or services. Most of the time, the integration is straightforward – you just utilize one of the available filters in one of the plugins and add the required functionality. But sometimes, it’s not as easy, because a plugin might not have any registered filters, and developers need to think of custom solutions, which are not always the best ones, but they solve a user problem.

One of such requests was for a WooCommerce Warehouses integration, which allowed selecting the warehouse for product stock. I wanted a solution that did not change the way how my plugin did database queries through WP_Query. It was possible to rewrite the code to use custom SQL queries and extend those through WordPress filters, but I didn’t like the complexity of such a solution. I started digging through WordPress documentation to see where I could hook in to manipulate queries from the WP_Query calls, and behold I found such a way – the posts_clauses filter.

The posts_clauses filter

The posts_clauses filter runs before the query gets executed and is essentially the sum of all filters that run immediately before it. So it should be used if you don’t intend to let another plugin override it, or if you need to alter several different parts of the query at once.

WordPress documentation

There are more detailed filters that allow manipulating specific WHERE, JOIN and other queries, however I decided to use posts_clauses as a more universal filter.

To register the posts_clauses filter we’re going to use the add_filter function:

add_filter( 'posts_clauses', 'filter_by_warehouse_id', 10, 2 );

Or if using classes:

add_filter( 'posts_clauses', array( $this, 'filter_by_warehouse_id' ), 10, 2 );

We also need to create the filter_by_warehouse_id method:

public function filter_by_warehouse_id( $clauses, $query_object ) {
	global $wpdb;

	$warehouse_id = apply_filters( 'custom_warehouse_id', 1 );

	$join = &$clauses['join'];
	
	if ( ' INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )' === $join ) {
		$join .= " JOIN {$wpdb->prefix}inventory_manager_product_warehouse AS warehouse ON {$wpdb->prefix}posts.ID = warehouse.product_id";	
		
		$where = &$clauses['where'];
		$where .= " AND ( warehouse.warehouse_id = {$warehouse_id} AND warehouse.stock > 0 )";
	}

	return $clauses;
}

Let’s quickly go over the method.

On line 3 we’re taking the global $wpdb object to get access to the database table prefix:

global $wpdb;

On line 5 we are setting the warehouse ID. Doing it through a filter, so we can easily modify it from outside the plugin code:

$warehouse_id = apply_filters( 'custom_warehouse_id', 1 );

On line 7, we’re going to reference the JOIN clause of the SQL query:

$join = &$clauses['join'];

On line 9 we’re going to check the JOIN query and make sure it’s the one we need to modify:

if ( ' INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )' === $join ) {

And if it does match, we will add our JOIN query (see line 10).

On lines 12-13 we’re adding an additional WHERE clause:

$where = &$clauses['where'];
$where .= " AND ( warehouse.warehouse_id = {$warehouse_id} AND warehouse.stock > 0 )";

And now we have an SQL query that will make an additional request to the wp_inventory_manager_product_warehouse table and check product availability in the selected warehouse. Hopefully, this quick example illustrated how to make use of the posts_clauses filter and you’ve learned something new.

If you need help with custom web development or you’re looking to start a new project, reach out to us using our contact form.