database – Extracting user field values from dynamic SQL queries


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

DrupalCoreDatabaseDatabaseExceptionWrapper: 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).