Query both first_name and last_name from wp_usermeta at the same time

Question

I'm trying to fetch both the first_name and last_name of all users at the same time, using the wpdb-object and the wp_usermeta table. I can't seem to figure out how to get both in the same query. Below is what I've got so far.

global $wpdb;
    $ansatte = $wpdb->get_results("SELECT user_id, meta_value AS first_name FROM wp_usermeta WHERE meta_key='first_name'");

    foreach ($ansatte as $ansatte) {
        echo $ansatte->first_name;
    }

Using the above code I'm able to echo out the first names of all users, but i would like for the last_name to be available aswell, like so;

foreach ($ansatte as $ansatte) {
        echo $ansatte->first_name . ' ' $ansatte->last_name;
    }

Any ideas?

Answer 1

I can't find a clean, native way to pull this data. There are a couple of ways I can think of to do this: First, something like:

$sql = "
  SELECT user_id,meta_key,meta_value
  FROM {$wpdb->usermeta} 
  WHERE ({$wpdb->usermeta}.meta_key = 'first_name' OR {$wpdb->usermeta}.meta_key = 'last_name')";
$ansatte = $wpdb->get_results($sql);
var_dump($sql);
$users = array();
foreach ($ansatte as $a) {
  $users[$a->user_id][$a->meta_key] = $a->meta_value;
}
var_dump($users);

You can then do:

foreach ($users as $u) {
    echo $u['first_name'].' '.$u['last_name'];
}

... to echo your user names.

The second way, a more pure SQL way, is what you were attempting:

$sql = "
  SELECT {$wpdb->usermeta}.user_id,{$wpdb->usermeta}.meta_value as first_name,m2.meta_value as last_name
  FROM {$wpdb->usermeta} 
  INNER JOIN {$wpdb->usermeta} as m2 ON {$wpdb->usermeta}.user_id = m2.user_id
  WHERE ({$wpdb->usermeta}.meta_key = 'first_name' 
  AND m2.meta_key = 'last_name')";
$ansatte = $wpdb->get_results($sql);

foreach ($ansatte as $ansatte) {
  echo $ansatte->first_name . ' ' . $ansatte->last_name;
}

You could also use get_users() or WP_User_Query to pull users but the meta_data you want isn't in the data returned and it would take more work to retrieve it.

Answer 2

This is a straight MySQL pivot answer for phpMyAdmin assuming your Wordpress table prefix is "wp_";

SELECT
  t1.id, t1.user_email,
  MAX(CASE WHEN t2.meta_key = 'first_name' THEN meta_value END) AS first_name,
  MAX(CASE WHEN t2.meta_key = 'last_name' THEN meta_value END) AS last_name 
FROM wp_users AS t1 
INNER JOIN wp_usermeta AS t2 ON t1.id = t2.user_id
GROUP BY t1.id, t1.user_email;

Answer 3

This is the correct approach using just SQL:

SELECT id
     , (SELECT MAX(meta_value) FROM wp_usermeta WHERE ID = wp_usermeta.user_id AND meta_key = 'first_name') first_name
     , (SELECT MAX(meta_value) FROM wp_usermeta WHERE ID = wp_usermeta.user_id AND meta_key = 'last_name') last_name
  FROM wp_users

Here are approaches that don't work:

• JOIN once for each metadata field does not work because there is no UNIQUE key on wp_usermeta(user_id, meta_key)
• Using subquery SELECT without MAX fails because there is no UNIQUE key on wp_usermeta(user_id, meta_key)

And for some reason this is faster than using a single select and join.