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 a SQL is. And that’s fine, you don’t need to know it too.

But there’s a high cost on missed opportunities. Even if you don’t master some aspects of a new are, knowledge is power. Once you know what is possible, you find new uses for this tool. And that takes your business to the next level.

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 meta data. With it, you can load users based on their names, id, custom fields.

This is all really great, but sometimes we need more.

As we saw in our article, there are some things that 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.

wordpress user sql query

That’s because the 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.

Now let’s get a quick recap of the basic syntax, debugging and dive into the parameters.

The pre_user_query action

In order 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.

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.

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:

and this is the result that the query is going to give us:

WordPress user query results

In the following sections we are going to show you how to modify the clauses of this query. In order 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 a another database table to the users table, so you can load additional user data. For instance, you could load additional meta data 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 field. In the way we have setup the query, this data can be available as a separate column in our query:

With this modification, the query that is going to be executed behind the scenes will look like this:

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:

WordPress user query results

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.

This will modify the database query like this:

And it will produce the following results:

WordPress user query select statements

Custom WHERE clause with query_where

In order 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:

This makes the underlying database query like this:

If you run the query, you will get a result like this one:

WordPress user query filter where clause

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 a limitation of the WordPress user query. When you use custom fields in it, you can’t really order by the custom fields and allow empty results. This means that users who don’t have that field won’t be in your list.

But if you use this snippet you are able to 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.

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:

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).

WordPress user query custom order by

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.

With this snippet the database query that will be executed will look like this:

 

More examples

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.

This can produce a result similar to this one:

WordPress user query show number of posts

Limit users 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, 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.

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!