TL;DR: Here’s how you can use SQL to find WordPress users by metadata. This is useful when you have information such as their first name and last name and want to retrieve the full WP_User.

WordPress provides a number of functions that make it easy to retrieve a user based on certain information. One of the most popular functions (or most useful, maybe?) is get_user_by.

However, what if you’re working on a system that maintains the user’s first name and last name or some other type of data point in the usermeta table and you want to use that to retrieve the user’s information?

Find WordPress Users by Metadata

First, let’s assume that you want to retrieve the user information based on their first name and last name as it’s stored in the usermeta table. To do that, the following SQL can do the job:

SELECT
	ID,
	first_meta.meta_value,
	last_meta.meta_value
FROM
	wp_users AS users
	LEFT JOIN wp_usermeta AS first_meta ON users.ID = first_meta.user_id
	LEFT JOIN wp_usermeta AS last_meta ON users.ID = last_meta.user_id
WHERE
	first_meta.meta_key = 'first_name'
	AND first_meta.meta_value = 'tom'
	AND last_meta.meta_key = 'last_name'
	AND last_meta.meta_value = 'mcfarlin'

This will then return a row of information that contains my user ID and the meta values that contain my first and last name.

Second, if you want to generalize this to retrieve information based on the meta data, the basic structure for the query would look something like this:

SELECT
	ID,
	metadata.meta_value
FROM
	wp_users AS users
	LEFT JOIN wp_usermeta AS metadata ON users.ID = metadata.user_id
WHERE
	first_meta.meta_key = 'your_meta_key'
	AND first_meta.meta_value = 'your_meta_value'

Obviously, you’ll need to customize that values are actually part of your SELECT clause and you’ll need to determine just how many meta keys you’re going to need, but using the above as a basic template is a good start.

I’m Not a Database Administrator

This is nothing more than a short tip for how to look up a user based on a couple of pieces of metadata.

If you end up keying off of a lot of information, it’s likely better to find a more performant query than continuing to join the same tables to together over and over.

I’m not a database administrator, don’t claim to be, and think that if you know of a better way to do something, then please do so (or if you know someone who’s able to to tackle something you need, don’t hesitate to ask them).

🗒️ Note you’ll have to wrap this in the correct $wpdb functions such as get_results and prepare but the gist of what needs to be done is in the query above.