Extracting user field values from dynamic SQL queries

Question

Aim

I have successfully written a fairly long dynamic sql query, however am struggling with a seemingly simple part at the end.

Although, I am able to successfully extract mail and name from the users table, when I try to extract field_first_name it returns the error below.

The users table has a column with the machine name: field_first_name

Code

    $database = \Drupal::service('database');

    $select = $database->select('flagging', 'f');
    $select->fields('f', array('uid', 'entity_id'));
    $select->leftJoin('node__field_start_datetime', 'nfds', 'nfds.entity_id = f.entity_id');
    $select->fields('nfds', array('field_start_datetime_value'));
    $select->leftJoin('node_field_data', 'nfd', 'nfd.nid = f.entity_id');
    $select->fields('nfd', array('title'));
    $select->leftJoin('users_field_data', 'ufd', 'ufd.uid = f.uid');
    // TODO extract first name
    $select->fields('ufd', ['mail', 'name', 'field_first_name']);

    $executed = $select->execute();
    $results = $executed->fetchAll(\PDO::FETCH_ASSOC);

    $username = $result['name'];
    $email = $result['mail'];
    $first_name = $result['field_first_name'];

Error

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ufd.field_first_name' in 'field list': SELECT f.uid AS uid, f.entity_id AS entity_id, nfds.field_start_datetime_value AS field_start_datetime_value, nfd.title AS title, ufd.mail AS mail, ufd.name AS name, ufd.field_first_name AS field_first_name FROM {flagging} f LEFT OUTER JOIN {node__field_start_datetime} nfds ON nfds.entity_id = f.entity_id LEFT OUTER JOIN {node_field_data} nfd ON nfd.nid = f.entity_id LEFT OUTER JOIN {users_field_data} ufd ON ufd.uid = f.uid; Array ( ) in event_notification_cron() (line 63 of /app/modules/custom/event_notification/event_notification.module).

Field

Answer 1

• Assuming your field is a string, the field in the database will be field_first_name_value
• Assuming the field is attached to a user entity, the table for that field should be user__field_first_name (2 underscores between user and the field name) your need one more join to get that field value, it's not stored in users_field_data

$database = \Drupal::service('database');

$select = $database->select('flagging', 'f');
$select->fields('f', array('uid', 'entity_id'));
$select->leftJoin('node__field_start_datetime', 'nfds', 'nfds.entity_id = f.entity_id');
$select->fields('nfds', array('field_start_datetime_value'));
$select->leftJoin('node_field_data', 'nfd', 'nfd.nid = f.entity_id');
$select->fields('nfd', array('title'));
$select->leftJoin('users_field_data', 'ufd', 'ufd.uid = f.uid');
$select->leftJoin('user__field_first_name', 'uffn', 'uffn.uid = f.uid');
// TODO extract first name
$select->fields('ufd', ['mail', 'name']);
$select->fields('uffn', ['field_first_name_value']);

$executed = $select->execute();
$results = $executed->fetchAll(\PDO::FETCH_ASSOC);

$username = $result['name'];
$email = $result['mail'];
$first_name = $result['field_first_name_value'];