We use the posts to posts (p2p) plugin a lot for building relationships between posts and post types. Occasionally you will have a query to get connected posts that will return multiple copies of the same post. So I worked out this method to handle that efficiently.
This method can be used for more than just query injections but that seems to be where I find myself doing it most. Sometimes you need a tweak to a get_posts or a new WP_Query but it’s so localized to the particular function it is in that it’s best to just take care of it there. No need to build out a class to handle it, just give this a shot.
Being Efficient
When coding, avoiding unnecessary sql queries and loops with conditionals is at the top of the list in keeping your site’s speed as optimized as possible. I had a query that was pulling in duplicate copies of posts thanks to the way p2p works. I could certainly just loop through the results and build a new array of distinct articles but that would be wasting processor power and making my query return far more than I needed.
I needed to do this right. So my thought was to filter into the query and adjust the sql before it ran. There’s a multitude of ways to do that but I also felt that this was so unique to this particular function that building out a class or a helper function to do it was unnecessary and would involve too much code for a simple task.
So the goal is to slap an sql clause filter on before the query and make sure that filter was removed before any other queries ran.
Choosing The Clause Filter
So the simplest filter would be to use ‘posts_distinct’ and return “DISTINCT.” This puts a DISTINCT string right after the SELECT statement. It works great for a typical query but when you have a complex JOIN query sometimes it just won’t work.
I toyed with the ‘posts_fields’ filter, but didn’t get far and never got the results I was after. I finally realized the sql I was invoking through p2p and WP_Query was needing a GROUP BY piece to group the results by post ID and remove duplicates.
function groupby( $groupby ) {
global $wpdb;
return "{$wpdb->posts}.ID";
}
Now I just needed to implement my filter somewhere.
Add Filter, Remove Filter
We need to make sure our filter only hits this single query we are running. What we can do is this:
- Build a closure: Assign a variable’s value a function to handle the groupby needs.
- Add a filter that executes our closure.
- Run our get_posts() or new WP_Query.
- Remove the filter.
It’s actually super simple and efficient. Here’s the code I used:
https://gist.github.com/ChrisFlannagan/73bf8b7c892a1e3106e8b16991875863
By adding the filter right before get_posts() and removing it right after we are making sure that filter only hits the query executed by get_posts(). Also, by storing the function as a closure in the $distinct_ids variable we are able to target it in our add_filter and remove_filter with ease.
WP_Query Filters
There’s a whole bunch of cool filters you can tap into to customize the sql query before it executes.
- posts_fields – Filter the fields right after the SELECT of the query
- posts_join – Filter the JOIN
- posts_orderby – Filter the ORDER BY
- posts_where – Filter the WHERE statements
- posts_distinct – Use return “DISTINCT” to avoid duplicates (unless you have a situation like mine here).
- posts_clauses – This one is awesome. It passes in a single parameter which is an array of all the above and you can adjust them all in the same function.
This shit is really powerful and when you start reaching more advanced levels of WordPress development you’ll find yourself using them regularly. Check out my co-workers blog post about building out a query for calculating distance between two geo coordinates. This was what taught me a lot about this stuff and it’s really amazing.