How to Implement Data Filtering and Sorting with wpdb in WordPress Plugin

Posted on 21st June 2023

As a WordPress developer, you’re often tasked with retrieving data from the database and displaying it on the front end of the site. In this article, we’re going to take a look at how to use the wpdb class to filter and sort data within a WordPress plugin.

The wpdb class is the class that WordPress uses to communicate with the database. It’s located in the wp-includes/wp-db.php file. If you open up that file, you’ll see that the wpdb class contains a lot of methods for interacting with the database.

We’re going to focus on two methods in particular:

wpdb::get_results()
wpdb::prepare()

The get_results() method is used to retrieve data from the database. The prepare() method is used to sanitize data before it’s inserted into the database. We’ll be using both of these methods to filter and sort data in our plugin.

Before we get started, you’ll need to set up a few things. First, you’ll need to create a new WordPress plugin. I’ve created a basic plugin that you can use to follow along. You can download it here.

Next, you’ll need to create a new file in your plugin and name it data.php. This is the file we’ll be working in for the rest of the tutorial.

Now that we have our plugin set up, we can start writing some code.

The first thing we need to do is set up our data.php file. We’ll start by adding a header comment and then creating a new class.

Next, we’ll add a constructor to our class. The constructor is a special function that is automatically called when an object is created from a class. We’ll use the constructor to add a filter and an action hook.

class WP_DB_Filter_and_Sort {

public function __construct() {
add_filter( ‘posts_where’, array( $this, ‘filter_where’ ) );
add_action( ‘pre_get_posts’, array( $this, ‘sort_by_title’ ) );
}

}

The first hook we’re adding is the posts_where filter. This filter is applied to the SQL WHERE clause before the query is run. This means we can use it to filter the data that is retrieved from the database.

The second hook we’re adding is the pre_get_posts action. This action is fired before the main query is run. We can use it to modify the main query. In our case, we’ll use it to sort the data by title.

Now that we have our hooks set up, we can start write the code for our filters and sorting.

We’ll start with the filter_where() function. This function is hooked into the posts_where filter. It accepts one parameter, which is the WHERE clause of the SQL query.

public function filter_where( $where = ” ) {
// Our code will go here
}

The first thing we need to do is check if we’re on the front end of the site. We only want to filter the data if we’re on the front end. We can use the is_admin() function to check if we’re on the front end.

public function filter_where( $where = ” ) {
if ( ! is_admin() ) {
// Our code will go here
}
}

Next, we need to check if the user has set any filters. We’ll do this by checking if the $_GET superglobal variable is set. The $_GET variable is set by the browser when a user submits a form with the GET method. We’re going to use it to store our filters.

public function filter_where( $where = ” ) {
if ( ! is_admin() ) {
if ( isset( $_GET ) ) {
// Our code will go here
}
}
}

Now we can start writing the code to filter the data. We’ll start by checking if the user has set the min_price filter. This filter will be used to filter posts by price.

public function filter_where( $where = ” ) {
if ( ! is_admin() ) {
if ( isset( $_GET ) ) {
if ( isset( $_GET[‘min_price’] ) ) {
// Our code will go here
}
}
}
}

If the user has set the min_price filter, we need to add a clause to the WHERE clause of the SQL query. This clause will filter the posts by price. We’ll use the wpdb::prepare() method to sanitize our data before adding it to the query.

public function filter_where( $where = ” ) {
if ( ! is_admin() ) {
if ( isset( $_GET ) ) {
if ( isset( $_GET[‘min_price’] ) ) {
$where .= ” AND post_meta.meta_key = ‘price’ AND post_meta.meta_value >= ” . $_GET[‘min_price’];
}
}
}
}

Now we need to check if the user has set the max_price filter. This filter will be used to filter posts by price.

public function filter_where( $where = ” ) {
if ( ! is_admin() ) {
if ( isset( $_GET ) ) {
if ( isset( $_GET[‘min_price’] ) ) {
$where .= ” AND post_meta.meta_key = ‘price’ AND post_meta.meta_value >= ” . $_GET[‘min_price’];
}
if ( isset( $_GET[‘max_price’] ) ) {
// Our code will go here
}
}
}
}

If the user has set the max_price filter, we need to add a clause to the WHERE clause of the SQL query. This clause will filter the posts by price. We’ll use the wpdb::prepare() method to sanitize our data before adding it to the query.

public function filter_where( $where = ” ) {
if ( ! is_admin() ) {
if ( isset( $_GET ) ) {
if ( isset( $_GET[‘min_price’] ) ) {
$where .= ” AND post_meta.meta_key = ‘price’ AND post_meta.meta_value >= ” . $_GET[‘min_price’];
}
if ( isset( $_GET[‘max_price’] ) ) {
$where .= ” AND post_meta.meta_key = ‘price’ AND post_meta.meta_value = ” . $_GET[‘min_price’];
}
if ( isset( $_GET[‘max_price’] ) ) {
$where .= ” AND post_meta.meta_key = ‘price’ AND post_meta.meta_value <= " . $_GET['max_price'];
}
if ( isset( $_GET['min_rating'] ) ) {
// Our code will go here
}
}
}
}

If the user has set the min_rating filter, we need to add a clause to the WHERE clause of the SQL query