0

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

enter image description here

2
  • 2
    Read what the error is telling you Column not found: 1054 Unknown column 'ufd.field_first_name'. Translation, there is no column named "field_first_name" in the "users_field_data" table. If you browse your the "users_field_data" table with PHPMyAdmin or some mysql browsing tool, you'll see that table has no such column.
    – No Sssweat
    Oct 18, 2020 at 1:30
  • 1
    Thank you @NoSssweat, I solved it with by using the mysql browsing tool and the answer below !
    – Darcy
    Oct 18, 2020 at 2:31

1 Answer 1

2
  • 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'];
3
  • Thanks @MacSim, I have tried as suggested but it returns the same error (although now with "Unknown column 'ufd.field_first_name_value'". I have attached a screenshot of the relevant field in the answer above just in case it helps :)
    – Darcy
    Oct 18, 2020 at 1:16
  • I edited my answer ;)
    – MacSim
    Oct 18, 2020 at 1:48
  • Thank you @MacSim! :)
    – Darcy
    Oct 18, 2020 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.