Sometimes you might need to change the member’s list in BuddyPress to display them by some other criteria. That might be something that can’t be done through classic filters and WordPress API. In this tutorial, I’ll show you how to change the SQL for the BuddyPress User Query to show only members with membership from MemberPress.
In the previous article on filtering the BuddyPress Member List, I have shown you how to Display Members in BuddyPress by Roles. This article will now show you how to use SQL to do similar filtering.
BuddyPress User Query
The BuddyPress User Query can be found in the class
BP_User_Query. If you spend enough time reading the code as I was, you’ll find a filter that allows us to change the query through SQL.
That filter is
bp_user_query_uid_clauses and what there are two parameters that are passed in that filter:
$sql– array with SQL statements. Array keys are: select, where, orderby, order & limit.
$query– this is the actual
BP_User_Queryobject on which the SQL is performed.
Even BuddyPress is using that filter to change the SQL with XProfile Fields. Feel free to search the BuddyPress plugin for the function
bp_xprofile_bp_user_query_search to see how they did it.
Of course, it could be useful to know what are we actually getting in that
$sql array, right? If you go and search for the method
prepare_user_ids_query inside the
BP_User_Query you would see a lot of those.
Based on the type of our member list which can be changed through the member list filter, we have different queries. Here are the types:
- online – table:
- active, newest, random – table:
- popular – table:
- alphabetical – if xProfile is not active table:
users, but if it’s active then table:
- other types – table:
I have also written on which tables the query is performed based on the types. The where clause is done differently. The
$sql is a multidimensional array thus each item is another array that holds where clauses. Each clause should begin with
Other than that, all others are simple strings, for example:
$sql = "ORDER BY u.value"; and
$sql = "ORDER BY u.value"; $sql = "ASC";.
Filtering only Active MemberPress Members
We will now filter the
$sql array so that we can retrieve only users who have an active membership in MemberPress.
To do that, we need to JOIN the table from MemberPress where such info is saved. Once that is done, we must also write a new WHERE clause that will exclude any users who do not have any active memberships.
Here is how it’s done:
First, we check if MemberPress is active by checking if their class exists. After that, we are writing a JOIN statement so that we can add the MemberPress table to our query.
We are also writing a new WHERE clause where we require only users who have an active membership.
The WordPress Plugin API was helpful again. And that is only because BuddyPress developers have made the plugin really extensible. To filter the BuddyPress User Query with SQL, you should know a little of SQL to begin with. Be sure to create good and non-vulnerable queries.
Have you ever creating custom member lists in BuddyPress? How did you extend the BuddyPress plugin?
If you want to up your WordPress development knowledge, I have a course, you might like. Building Themes, Plugins, optimizing them & selling is something you might want to know? Check it out: Become a WordPress Developer.