The WP_Query class in WordPress is extremely robust and allows you to construct just about any kind of query you need for retrieving data from the wp_posts table, but there are still scenarios you will encounter when building your plugins that WP_Query doesn’t support. Before WordPress 3.7, the posts_where filter was used primarily for setting up date-range queries and queries that pulled all posts except those by a set of authors. Both of these kind of queries are supported in WordPress 3.7+, but there are still numerous cases where the posts_where filter is the tool of choice for querying just the data you need.
The video below walks (quickly) through how I used the posts_where filter to set up queries for specific date ranges in Easy Digital Downloads. We needed to query payment data (stored in wp_posts) between two dates, which at the time was not supported by the default WP_Query args.
The EDD_Stats class is the base class we use to setup the date properties. Once the dates are setup, we use the posts_where filter to modify the where clause of the SQL query that WordPress performs. You can see our payments_where() callback here and how the callback is used here.
If you are not familiar with how the SQL where clause works, I’d suggest you read up on it. Here are a few good articles: