database - Is there a smart way to obtain a list of only some selected user meta data?

admin管理员组

文章数量:1434904

I have a case where, if I had written the DB and queries myself, I would just do something like SELECT name, firstname, email FROM users WHERE .... Returning me a list/collection/array, I mean, not necessarily a single user.

But I learned that to get name and firstname in WordPress, you first need to get the IDs of some users, then use something like this $targetUsers = get_users(['include' => wp_list_pluck($targetUsersIDs,'ID')]);

This means that I get a collection of complete user objects, which is potentially a lot of data for nothing.

For now, the best thing I come up with would be to run this query:

SELECT u.ID, u.user_email, um.meta_key, um.meta_value FROM wp_users u JOIN wp_usermeta um ON u.ID = um.user_id
WHERE um.meta_key = 'last_name' OR um.meta_key = 'first_name'
GROUP BY u.ID, u.user_email, um.meta_key, um.meta_key
ORDER BY u.ID

and then loop over of it to build my own objects.

Or maybe there is something to do with some nested query...

Please note that the question is not about selecting specific fields from the user table but from the user meta table!

I have a case where, if I had written the DB and queries myself, I would just do something like SELECT name, firstname, email FROM users WHERE .... Returning me a list/collection/array, I mean, not necessarily a single user.

But I learned that to get name and firstname in WordPress, you first need to get the IDs of some users, then use something like this $targetUsers = get_users(['include' => wp_list_pluck($targetUsersIDs,'ID')]);

This means that I get a collection of complete user objects, which is potentially a lot of data for nothing.

For now, the best thing I come up with would be to run this query:

SELECT u.ID, u.user_email, um.meta_key, um.meta_value FROM wp_users u JOIN wp_usermeta um ON u.ID = um.user_id
WHERE um.meta_key = 'last_name' OR um.meta_key = 'first_name'
GROUP BY u.ID, u.user_email, um.meta_key, um.meta_key
ORDER BY u.ID

and then loop over of it to build my own objects.

Or maybe there is something to do with some nested query...

Please note that the question is not about selecting specific fields from the user table but from the user meta table!

• database
• users

Share Improve this question edited Apr 10, 2019 at 19:14 TTT asked Apr 10, 2019 at 18:37 TTTTTT 32911 gold badge44 silver badges1717 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I think you should stick to WP_User_Query. There you can simply specify the fields you want to use to select certain users. Also meta fields.

WP_User_Query is a class, defined in wp-includes/user.php, that allows querying WordPress database tables wp_users and wp_usermeta.

But yes(!), this will not return you user meta values. Even when setting 'fields' => 'all_with_meta'.

all_with_meta currently returns the same fields as all which does not include user fields stored in wp_usermeta. You must create a second query to get the user meta fields by ID or use the __get PHP magic method to get the values of these fields.

That said, my conclusion would be to indeed use meta_query in a WP_User_Query to select users by certain meta values and use that result to retrieve additional meta data by calling for example get_user_meta($user->ID, 'meta_key', TRUE).

$user_query = new WP_User_Query([
  'role'       => 'editor', 
  'meta_query' => [
    [
      'key'     => 'meta_key',
      'compare' => 'EXISTS', // Use 'NOT EXISTS' for non-existance of this key.
    ],
  ],
]);

$editors = $user_query->get_results();

foreach ($editors as $editor) {
  $first_name = $editor->display_name;
  $meta_value = get_user_meta($editor->ID, 'meta_key', TRUE);
  // ...
}

That's one hundred times more readable and maintainable than a custom database query, in my opinion.

---

And regarding performance, maybe check out this related answer on the same topic just for post meta data: Can WP_Query return post meta in a single request? which links you to: Custom post meta field effect on the performance on the post.

本文标签： databaseIs there a smart way to obtain a list of only some selected user meta data