SQL query to access a field collection's data? At them moment, I'm only getting field_collection_item returned

Question

In the below example, I am trying to return the value of a field from all the field collections in the database but only get field_collection_item. This example is just the most basic field collection sql possible.

SELECT field_collection_item.item_id AS item_id, 'field_collection_item' AS field_data_field_grade_decimal_field_collection_item_entity_
FROM 
field_collection_item field_collection_item

What do I need to change in this example? My SQL is terrible unfortunately. I just can't return any values from field collections using sql. I need to be able to do this for Forena Reports. I'm getting the above code from views which returns the field collection values.

Answer 1

You can use the field_collection_field_get_entity() function to obtain the desired result without writing SQL, like this:

$node = node_load(12); $items = field_get_items('node', $node, 'field_fc_field'); foreach ($items as $item) { $fc = field_collection_field_get_entity($item); // Do something with $fc dpm($fc); }