How to customize the WordPress user SQL query
In this article, we are going to deep dive into using the WordPress user query with custom statements. We’ll see how to hook to the pre_user_query action to modify the default user query. In addition, we’ll discover how to edit the user SQL for better performance and privacy.
When it comes to WordPress projects, there are several levels of complexity. The truth is, an average user doesn’t even know what SQL is. And that’s fine, you don’t need to know it too.
SQL is short for Structured Query Language, is a programming language designed for managing data in relational database management systems (RDBMS). SQL is widely used by WordPress developers to interact with WordPress databases. This is because WordPress uses MySQL as a database management system. SQL is a query programming language that interacts with RDBMS. MySQL is a relational database management system that uses SQL.
That’s why we dive into deep waters and explore some concepts of the SQL parameters of the WordPress user query. We have previously shown how to work with the WordPress user meta query. In short, it’s a class that can be used to load users with their metadata. With it, you can load users based on their names, IDs, and custom fields.
SQL is a programming language designed for managing data in relational database management systems.
This is all great, but sometimes we need more.
As we saw in our article, some things can’t be done just with the class parameters. We need to write our own SQL rules. And in these cases, we use the direct interaction with the SQL query parameters. These are usually for better data formatting, data restriction, or performance.
That’s because WordPress’s default SQL can be quite heavy, and we can use our tricks to make it better. In a single query, you can load all the data you want, as opposed to getting data for each user separately.
Just a side note: to be able to do anything with SQL and WordPress first you need to access the database with phpMyAdmin. With phpMyAdmin, you can easily create and manage databases and tables, import and export data, perform SQL queries and operations, administer multiple servers, manage user accounts and privileges, and much more.
Now let’s get a quick recap of the basic syntax, debugging, and dive into the parameters.
The pre_user_query action
To modify the user database query, we need to use the pre_user_query action. The action passes the $query object, so we can modify different aspects of it.
It is important to mention that the pre_user_query action is applied to every user query that runs on the page. Therefore, if we just hook to this action to change the query, our changes will be applied to every query, which will most likely not be the desired result. That’s why we need to register the action callback exactly before we run our custom query and remove it right after the query has loaded the results.
Also, please note that pre_user_query is an action and not a filter, so when hooking to it, we don’t need to return the $query object – we are going to modify the object directly via the PHP code.
The following snippet illustrates the process of hooking our custom my_query_clauses function before we run the query and removing it right after it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
function my_load_users() { //hook our custom query action add_action( 'pre_user_query', 'my_query_clauses' ); //run the query as usual $query = new WP_User_Query(array( 'fields' => array('ID', 'user_login', 'user_email'))); $users = $query->get_results(); //remove the action so it doesn't mess up with the rest of the user queries remove_action( 'pre_user_query', 'my_query_clauses' ); return $users; } function my_query_clauses( $query ) { //MODIFY THE QUERY OBJECT HERE } |
Also, please note that you would need to pass at least one parameter when initializing the WP_User_Query object. In this example, we have specified the fields that we want to load, but you can apply any other allowed parameters depending on your requirements.
Running the code like this would execute the following query:
|
1 2 3 4 |
SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.user_login,wp_users.user_email FROM wp_users WHERE 1=1 ORDER BY user_login ASC |
and this is the result that the query is going to give us:
In the following sections, we are going to show you how to modify the clauses of this query. To do this, we’ll modify the different properties of the $query object inside our custom my_query_clauses function. We’re going to cover the following parameters:
- $query->query_from
- $query->query_fields
- $query->query_where
- $query->query_orderby
- $query->query_limit
Custom FROM clauses with query_from
As its name implies, the query_from parameter allows us to modify the default query FROM clause. By default, the query loads all the records from the wp_users table. Since we want to load the user records, there is no point in changing the table from which the users are loaded. However, there is another useful way to use the query_from parameter – we can use it to specify custom table join clauses.
Oftentimes you might need to join another database table to the user’s table, so you can load additional user data. For instance, you could load additional metadata or user data from a custom plugin table.
In this example we’ll show you how to LEFT JOIN the wp_usermeta table, however, you can apply the same principle to any other table. In this way, we join additional columns to the records from the wp_users table and we’ll still have the same result of records, even if there are no matching data for some of them from the joining table. This is useful when we want to have the users AND something else.
Depending on your data structure, and what you want to load different JOINS can be done. For instance, you can join data that is hidden in a plugin such as WooCommerce or Ultimate Member. You can load referenced data about your users, such as their orders.
Now here’s an example that joins the wp_usermeta table’s billing_country meta key field. In the way we have setup the query, this data can be available as a separate column in our query:
|
1 2 3 4 5 6 7 |
function my_query_clauses( $query ) { global $wpdb; //now we add a left join to actually populate the billing country for our users $query->query_from .= " LEFT JOIN $wpdb->usermeta billing_country ON $wpdb->users.ID = ". "billing_country.user_id and billing_country.meta_key = 'billing_country'"; } |
With this modification, the custom SQL query that is going to be executed behind the scenes will look like this:
|
1 2 3 4 5 6 |
SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.user_login,wp_users.user_email FROM wp_users LEFT JOIN wp_usermeta billing_country ON wp_users.ID = billing_country.user_id AND billing_country.meta_key = 'billing_country' WHERE 1=1 ORDER BY user_login ASC |
As you might have guessed, this code snippet won’t make the billing_country column available right away, as it is not included in the SELECT clause. The query like this would return the same results as the one above:
We’ll see how to add the column to the SELECT statement in the next section.
Custom SELECT statement with query_fields
The user query will by default load the fields from the wp_users table. However, if you need to add additional fields, you will need to specify them in the SELECT statement. You can do this by modifying the query_fields parameter.
Following the example from the previous section, in order to make the billing country field available in the user records, we can just add it to the query_fields parameter.
|
1 2 3 4 5 6 7 8 9 10 |
function my_query_clauses( $query ) { global $wpdb; //let's add the billing_coutry to our meta fields in our query $query->query_fields .= ', billing_country.meta_value AS country'; //now we add a left join to actually populate the billing country for our users $query->query_from .= " LEFT JOIN $wpdb->usermeta billing_country ON $wpdb->users.ID = ". "billing_country.user_id AND billing_country.meta_key = 'billing_country'"; } |
This will modify the database query like this:
|
1 2 3 4 5 6 7 |
SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.user_login,wp_users.user_email, billing_country.meta_value AS country FROM wp_users LEFT JOIN wp_usermeta billing_country ON wp_users.ID = billing_country.user_id AND billing_country.meta_key = 'billing_country' WHERE 1=1 ORDER BY user_login ASC |
And it will produce the following results:
Custom WHERE clause with query_where
To filter the WHERE clause of the query, we can modify the query_where parameter.
As you might have noticed by the produced SQL snippets above, by default, WordPress comes with an empty WHERE clause. It just loads results WHERE 1=1, which is all of them.
This is useful in many ways. You can use the custom fields, for instance, we could filter all users with the country equal to “US” in our previous example:
|
1 2 3 4 5 6 7 8 9 10 |
function my_query_clauses( $query ) { global $wpdb; $query->query_fields .= ', billing_country.meta_value AS country'; $query->query_from .= " LEFT JOIN $wpdb->usermeta billing_country ON $wpdb->users.ID = ". "billing_country.user_id and billing_country.meta_key = 'billing_country'"; //let's filter only the country we want $query->query_where .= " AND meta_value ='US'"; } |
This makes the underlying database query like this:
|
1 2 3 4 5 6 7 |
SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.user_login,wp_users.user_email, billing_country.meta_value AS country FROM wp_users LEFT JOIN wp_usermeta billing_country ON wp_users.ID = billing_country.user_id AND billing_country.meta_key = 'billing_country' WHERE 1=1 AND meta_value ='US' ORDER BY user_login ASC |
If you run the query, you will get a result like this one:
Now let’s change how our users are ordered!
Custom ORDER BY parameters with query_orderby
The query_orderby parameter, as the name suggests, controls the record order. This parameter allows us to modify the database query ORDER BY statement. We can order using different fields, ascending or descending.
This is an important attribute to overcome the limitation of the WordPress user query. When you use custom fields in it, you can’t order by the custom fields and allow empty results. This means that users who don’t have that field won’t be on your list.
But if you use this snippet you can load the custom fields just for the users who have them. And it’s possible to order users based on this field, including users who don’t have it.
|
1 2 3 4 5 6 7 8 9 10 |
function my_query_clauses( $query ) { global $wpdb; $query->query_fields .= ', billing_country.meta_value AS country'; $query->query_from .= " LEFT JOIN $wpdb->usermeta billing_country ON $wpdb->users.ID = ". "billing_country.user_id AND billing_country.meta_key = 'billing_country'"; //and the last step is ordering users based on the billing_country values, when present $query->query_orderby = ' ORDER BY billing_country.meta_value DESC'; } |
Please note how we completely overwrite the query_orderby parameter, instead of appending a new value. In this way, we won’t have the default order by username clause.
With this snippet the database query of the WP_User_Query object will look like this:
|
1 2 3 4 5 6 7 |
SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.user_login,wp_users.user_email, billing_country.meta_value AS country FROM wp_users LEFT JOIN wp_usermeta billing_country ON wp_users.ID = billing_country.user_id AND billing_country.meta_key = 'billing_country' WHERE 1=1 ORDER BY country DESC |
The advantage of this approach is that users with null values are still loaded (which doesn’t happen with other methods of loading users and custom fields).
Custom LIMIT statement with query_limit
The LIMIT statement tells the query how many records to load. You might want to get all your results at once. But often a limit is a good idea when it comes to performance.
Additionally, in that parameter, we can set the offset. We tell WordPress how many rows to skip, then how many rows we want. Although the offset parameter can be omitted.
The following example illustrates how to use the query_limit parameter of the query object, so you can modify the LIMIT statement.
|
1 2 3 4 5 6 |
function my_query_clauses( $query ) { global $wpdb; //skip the first 10 results, and show 20 $query->query_limit = ' LIMIT 10, 20'; } |
With this snippet the database query that will be executed will look like this:
|
1 2 3 4 5 |
SELECT SQL_CALC_FOUND_ROWS wp_users.ID,wp_users.user_login,wp_users.user_email FROM wp_users WHERE 1=1 ORDER BY user_login ASC LIMIT 10, 20 |
More examples of loading data from the WordPress database with SQL
In this section, we’ll show you some more examples of filters to use
Finding the number of posts that each user has created
In the following example, you will learn how you can load the number of posts that each user has created from a selected post type. All we need to do is just join a subquery that counts the number of posts for each user and then load the result in the SELECT statement. For this, we’ll modify the query_from and query_fields parameters of the user query object.
|
1 2 3 4 5 6 7 8 |
function my_query_clauses( $query ) { global $wpdb; $query->query_from .= " LEFT JOIN (SELECT COUNT(*) AS count, post_author". " FROM $wpdb->posts WHERE post_type = 'topic' GROUP BY post_author)". " AS topics ON topics.post_author = $wpdb->users.ID"; $query->query_fields .= ", topics.count"; } |
This can produce a result similar to this one:
Limit user’s list view depending on the current user
Often, we need to restrict how many users other staff members can see. For instance, you may have a list of users, and their orders, and this list is visible to managers. You may not want to allow managers to see other managers’ lists. This could be done if you have a custom field assigning that user to that manager.
We use the WHERE clause, tied to the user ID of the current user.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
function my_query_clauses( $query ) { global $wpdb; //get the current user ID $uid = get_current_user_id(); //let's add the manager ID to our meta fields in our query $query->query_fields .= ', manager_id.meta_value'; //now we add a left join to actually populate the manager for our users $query->query_from .= " LEFT JOIN $wpdb->usermeta manager_id ON $wpdb->users.ID = ". "manager_id.user_id and manager_id.meta_key = 'manager_id'"; //add our where clause to load users whose manager ID is the current user ID $query->query_where = " WHERE meta_value ='$uid'"; } |
Conclusion
Today we saw how you can use the SQL query parameters for the WordPress User query by hooking to the pre_user_query action. We investigated some examples as well as some explanations of how they work.
By the end of the day, you should be able to perform a wide range of user searches and get the results you want.
We hope you enjoyed this article, and see you again next time!