By orgnsm on

Hello, I am looking to obtain the values of field_collection fields without using entity_load or entityFieldQuery. I'd like to use db_select or db_query for speed and flexibility (not limited to inner joins). Perhaps there is no way to do this but any information is helpful.

⋅ Categories: Drupal 7.x

Comments

sam moore’s picture

sam moore
he/him
Location Sandy Hook, CT
commented

Well you can get a sense of the structure of the Field Collection schema by looking in the database, or reading the module's code - is that the info you're missing for your DB query?

Just curious - there are Drupal functions that will return the items from a field collection - why not use those?

============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics

orgnsm’s picture

orgnsm commented

thank you for your response. i wasn't able to discover a query based on reviewing the DB, so i suppose this question is more of a SQL beginner question than anything. the next poster was able to give me enough clues to get it working.

i wanted to not use things like Views because I already have a full set of specific custom templates and eventually want to make everything CMS-independent... however inappropriate that statement may be on here....

cheers

sandeep.kumbhatil’s picture

sandeep.kumbhatil commented

For e.g if there is field collection call price and it have collection of field like currency, price and country, so you can execute a join query on node table by the price field like below:

$query->leftJoin('field_data_field_price_new', 'fpn', 'fpn.entity_id = n.nid');

Then after the join of field you can add join to fetch field collection from the other tables consist currency, price and country. for e.g. 

//price field collection
$query->leftJoin('field_data_field_currency', 'fdfcur', 'fdfcur.entity_id = fpn.field_price_new_value');
$query->leftJoin('field_data_field_price_value', 'fdfpv', 'fdfpv.entity_id = fpn.field_price_new_value');
$query->leftJoin('field_data_field_country', 'fdfcoun', 'fdfcoun.entity_id = fpn.field_price_new_value');
orgnsm’s picture

orgnsm commented

thank you so much! i would not have been able to figure this out without your help. it works great (and fast)... and allowed me to check if values are not set (which is not possible with EntityFieldQuery).. here is my full code for reference:

<?php
    timer_start('db_select');
    $query = db_select('node', 'n');
    $query->fields('n', array('nid', 'uid', 'status', 'created'));

    $query->leftJoin('field_data_field_context', 'fcs', 'fcs.entity_id = n.nid');
    $query->leftJoin('field_data_field_connection', 'fdfcon', 'fdfcon.entity_id = fcs.field_context_value');
    $query->leftJoin('field_data_field_weight', 'fdfwei', 'fdfwei.entity_id = fcs.field_context_value');
    $query->leftJoin('field_data_field_response', 'fdfres', 'fdfres.entity_id = fcs.field_context_value');
    $query->fields('fdfcon');
    $query->fields('fdfwei');
    $query->fields('fdfres');

    $query->condition('field_connection_target_id', $query_nid);

    $query->orderBy('nid');
    $results=$query->execute();
    debug(timer_read('db_select') . ' ms');
    return $results;

?>
shashwat purav’s picture

shashwat purav commented

Thank you very much. This is very helpful.

Thank You,
Shashwat Purav