Extract data from Field Collection using db_select()

Question

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?

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();