Query custom field on user table

Question

How do you query custom fields on the user table? If edit a user in the Admin panel, and hit the DEVEL tab, I see the field I added, 'field_display_name'.

In a node template php file, I want to replace the 'name' field with 'field_display_name'.

When I query, either using:

$result = db_select('users', 'u')
  ->fields('u')
  ->condition('name', 'bubba', '=')
  ->execute()
  ->fetchAssoc();

or

$rst = db_query("select * from {users} where name = :name", array(':name' => 'bubba',));

neither one of these return my custom field on the user table. (DPM does not show field_display_name)

So obviously, i'm missing something fundamental in my understanding of the data structure.

How do i return this added field by querying with uid or name to return and print the 'field_display_name' ?

Answer 1

Entity fields are in their own separate table(s), they don't get attached to the base entity table.

You can use an EntityFieldQuery to get that data:

$query = new \EntityFieldQuery();
$query->entityCondition('entity_type', 'user')
  ->fieldCondition('field_display_name', 'value', 'bubba');

$results = $query->execute();
if (!empty($results['user'])) {
  $users = user_load_multiple(array_keys($results['user']));
}

See Where does Drupal 7 store custom user account fields? for a bit more info

Answer 2

Drupal 8:

$query = \Drupal::service('entity.query')
  ->get('user')
  ->condition('field_custom', $custom_value)
  $entity_ids = $query->execute();

Answer 3

Alternatively, you could use the entity type manager service.

/** @var \Drupal\Core\Entity\Query\QueryInterface $query */
$query = \Drupal::service('entity_type.manager')
  ->getStorage('user')
  ->getQuery();

$query->condition('field_custom', $custom_value)
$entityIds = $query->execute();

Previous code only gives you the user IDs that fulfill the condition, then you need to do a call to 'user_load_multiple' function.

$users = [];
if (!empty($entityIds)) {
  $users = user_load_multiple(array_keys($entityIds));
}

For Drupal 8.7+ and Drupal 9, I strongly recommend using Dependency Injection where it is possible because of code standards and performance.