i go to admin/structure/views/settings, check Show information and statistics about the view during live preview and then Show the SQL query checkbox that will appear below.

I have a field which is field_col the field type is Node reference and the widget is a Select list.

this field can have only one value, and in its settings, it is set as to reference a content type named col. Also in the field settings, there is the following:

VIEWS - NODES THAT CAN BE REFERENCED The list of nodes that can be referenced can provided by a view (Views module) using the "References" display type. There a view is set, whose sql query is the below code:

SELECT node.title AS node_title, node.nid AS nid
{node} node
INNER JOIN {og_mm} og_mm_node ON node.nid = og_mm_node.etid AND og_mm_node.entity_type = 'node'
WHERE (( (og_mm_node.gid = '20' ) )AND(( (node.status = '1') AND (node.type IN  ('col')) )))
ORDER BY node_title ASC

In the view, where in the live preview i get the results i want, the sql query looks like:

SELECT node.nid AS nid, node.created AS node_created, node_field_data_field_title.title AS node_field_data_field_title_title, field_data_field_it_ac_nr.field_it_ac_nr_value AS field_data_field_it_ac_nr_field_it_ac_nr_value, field_data_field_col.field_col_nid AS field_data_field_col_field_col_nid, field_data_field_t_year.field_t_year_value AS field_data_field_t_year_field_t_year_value, field_data_field_it_vlm.field_it_vlm_value AS field_data_field_it_vlm_field_it_vlm_value, field_data_field_it_cn.field_it_cn_value AS field_data_field_it_cn_field_it_cn_value, 'node' AS field_data_field_it_ac_nr_node_entity_type, 'node' AS field_data_field_col_node_entity_type, 'node' AS field_data_field_tak_node_entity_type
node node
LEFT JOIN field_data_field_title field_data_field_title ON node.nid = field_data_field_title.entity_id AND field_data_field_title.entity_type = 'node'
LEFT JOIN node node_field_data_field_title ON field_data_field_title.field_title_nid = node_field_data_field_title.nid
LEFT JOIN og_mm og_mm_node ON node.nid = og_mm_node.etid AND og_mm_node.entity_type = 'node'
LEFT JOIN field_data_field_deleted field_data_field_deleted ON node.nid = field_data_field_deleted.entity_id AND field_data_field_deleted.field_deleted_value = '1'
LEFT JOIN field_data_field_it_ac_nr field_data_field_it_ac_nr ON node.nid = field_data_field_it_ac_nr.entity_id AND field_data_field_it_ac_nr.entity_type = 'node'
LEFT JOIN field_data_field_col field_data_field_col ON node.nid = field_data_field_col.entity_id AND field_data_field_col.entity_type = 'node'
LEFT JOIN field_data_field_t_year field_data_field_t_year ON node.nid = field_data_field_t_year.entity_id AND field_data_field_t_year.entity_type = 'node'
LEFT JOIN field_data_field_it_vlm field_data_field_it_vlm ON node.nid = field_data_field_it_vlm.entity_id AND field_data_field_it_vlm.entity_type = 'node'
LEFT JOIN field_data_field_it_cn field_data_field_it_cn ON node.nid = field_data_field_it_cn.entity_id AND field_data_field_it_cn.entity_type = 'node'
WHERE (( (og_mm_node.gid = '20' ) )AND(( (node.status = '1') AND (node.type IN  ('product')) AND (field_data_field_deleted.field_deleted_value IS NULL ) )))
ORDER BY field_data_field_it_ac_nr_field_it_ac_nr_value ASC, field_data_field_col_field_col_nid ASC, field_data_field_t_year_field_t_year_value ASC, field_data_field_it_vlm_field_it_vlm_value ASC, field_data_field_it_cn_field_it_cn_value ASC

but it returns the node id of the node that is referenced, and not the value i need. How the two above should be made, combined, in order to get the results i want?


Always use the API to load entity data; getting it directly from the database is not recommended.

Once you have your node IDs from the initial query:

$nodes = node_load_multiple($nids);
foreach ($nodes as $node) {
  foreach (field_get_items('node', $node, 'field_col') as $item) {
    // Do something with $item['value'], for example
|improve this answer|||||
  • could you please elaborate on why getting it directly from the database is not recommended? I would like to see how a complete mysql query should look like in order to get the results the view returns also... – tajovake Sep 13 '19 at 9:57
  • It's a standard development principle - program to the interface, not the implementation. If you tie your code to the underlying implementation, it'll break when that implementation changes. I would personally ignore what Views is doing, and make sure you're simply writing code that does what you want it to – Clive Sep 13 '19 at 10:10

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.