I'd like to use db_select() to extract values from a field collection attached to the user profile, to help speed up some complex Ajax forms (rather than loading the entire field collection, using standard entity queries, etc, etc).

Assuming I have the following:

Field Collection: field_my_address
Field 1: field_street_number (integer field)
Field 2: field_street_name (text field)
Field 3: field_city (taxonomy term reference field)

How would I go about doing this?

  • This is slightly too broad. Revisions necessary? But basically, my first stop when doing Drupal 7 db stuff is to look at the Dynamic Queries as well as understanding the field schema (field/revision).
    – mradcliffe
    Jul 9, 2018 at 0:26
  • Not interested in revisions; however, I'll browse through that reference and see what I can find... Standard queries, I've been ok with, but FC's are causing me pain!
    – Simo
    Jul 9, 2018 at 21:50
  • Without revisions, then you can join in field tables via field_data_[FIELD_NAME] e.g. field_data_field_street_name and make sure that entity_id and entity_type are set. My one gotcha there is that if you use a placeholder for entity_type, there's a bug (or hidden feature?) where you can only set the same placeholder name once. (Edit) You'll also need to look up the field storage schema (hook_field_schema) for those fields, but I think the columns would be FIELD_NAME_value and FIELD_NAME_tid respectively.
    – mradcliffe
    Jul 10, 2018 at 18:19

1 Answer 1


I got it to work like this:

$pid = $profile->pid;

$query = db_query('SELECT nn.field_street_number_value AS number,
                          ss.field_field_street_name_value AS street, 
                          cc.field_city_tid AS city, 
         FROM field_data_field_my_address AS aa
         LEFT JOIN field_data_field_street_number AS nn ON aa.field_my_address_value = nn.entity_id 
         LEFT JOIN field_data_field_street_name AS ss ON aa.field_my_address_value = ss.entity_id 
         LEFT JOIN field_data_field_city AS cc ON aa.field_my_address_value = cc.entity_id 
         WHERE aa.entity_id = '.$pid);

$records = $query->fetchAll();

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.