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_
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.

  • What I'm trying to do is return field values from a field collection using sql. The above code doesn't return the ID of the field collection; it returns a decimal field in the field collection when used in views. Commented Mar 30, 2016 at 6:21
  • Notice that you don't need 'field_collection_item' AS field_data_field_grade_decimal_field_collection_item_entity_ and FROM field_collection_item field_collection_item can simply be FROM field_collection_item.
    – apaderno
    Commented Mar 30, 2016 at 8:34
  • But I need to return the value of the field_data_field_grade_decimal_ fields. Views does it with the above output but it doesn't work if I query the database. Commented Mar 30, 2016 at 13:17

1 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); }

  • It's for Forena reports so I need it as SQL unfortunately. Commented Apr 4, 2016 at 15:50
  • You simply need the field collection item id associated with the node and then you have to make inner join with the tables containing the values. I will post a sample code here later tonight.
    – W.M.
    Commented Apr 4, 2016 at 16:01
  • Thanks, but could you wait until tomorrow. I want to put my final code query in the question as I feel that I won't be able to do it myself. I'm going to put a bounty on it as well. Commented Apr 4, 2016 at 16:13

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.