0

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.

  • 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. – Niall Murphy Mar 30 '16 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. – kiamlaluno Mar 30 '16 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. – Niall Murphy Mar 30 '16 at 13:17
0

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

|improve this answer|||||
  • It's for Forena reports so I need it as SQL unfortunately. – Niall Murphy Apr 4 '16 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. Apr 4 '16 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. – Niall Murphy Apr 4 '16 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.