-1

From a view, I get the following in the Auto preview Preview with contextual filters:

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.field_it_ac_value AS field_data_field_it_ac_field_it_ac_value, field_data_field_clon.field_clon_nid AS field_data_field_clon_field_clon_nid, field_data_field_ts_year.field_ts_year_value AS field_data_field_ts_year_field_ts_year_value, field_data_field_it_volume.field_it_volume_value AS field_data_field_it_volume_field_it_volume_value, field_data_field_it_cl.field_it_cl_value AS field_data_field_it_cl_field_it_cl_value, 'node' AS field_data_field_it_ac_node_entity_type, 'node' AS field_data_field_clon_node_entity_type, 'node' AS field_data_field_taksithetiko_node_entity_type
FROM 
{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_membership} og_membership_node ON node.nid = og_membership_node.etid AND og_membership_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} field_data_field_it_ac ON node.nid = field_data_field_it_ac.entity_id AND field_data_field_it_ac.entity_type = 'node'
LEFT JOIN {field_data_field_clon} field_data_field_clon ON node.nid = field_data_field_clon.entity_id AND field_data_field_clon.entity_type = 'node'
LEFT JOIN {field_data_field_ts_year} field_data_field_ts_year ON node.nid = field_data_field_ts_year.entity_id AND field_data_field_ts_year.entity_type = 'node'
LEFT JOIN {field_data_field_it_volume} field_data_field_it_volume ON node.nid = field_data_field_it_volume.entity_id AND field_data_field_it_volume.entity_type = 'node'
LEFT JOIN {field_data_field_it_cl} field_data_field_it_cl ON node.nid = field_data_field_it_cl.entity_id AND field_data_field_it_cl.entity_type = 'node'
WHERE (( (og_membership_node.gid = '20' ) )AND(( (node.status = '1') AND (node.type IN  ('item')) AND (field_data_field_deleted.field_deleted_value IS NULL ) )))
ORDER BY field_data_field_it_ac_field_it_ac_value ASC, field_data_field_clon_field_clon_nid ASC, field_data_field_ts_year_field_ts_year_value ASC, field_data_field_it_volume_field_it_volume_value ASC, field_data_field_it_cl_field_it_cl_value ASC
LIMIT 20 OFFSET 0

Which brings in the preview the results I need.

In order to get the same results in MySQL, I run the same query without the brackets {}, and I get irrelevant results... How should the brackets be replaced, how should I alter the above query in order to get the same result I get from the view?

  • Ignoring the syntax error, your first and second queries are completely different. Why would you expect them to return the same results? – Clive Sep 12 at 14:00
  • could you please tell me what {node} stands for? the first query is with the default view settings, and the second with the use of function MYMODULE_views_pre_execute(&$view) { dpq($view->build_info['query']); } – howo Sep 12 at 15:14
  • It doesn't really "stand" for anything, it means the node table. The brackets are used to supplement the table name with an optional global prefix that can be enabled when the $databases are declared – Clive Sep 12 at 15:16
  • yes, but how then the mysql query should be in order to get the proper results from the db say how this should be changed: {og_membership} my problem is i get node ids, insttead of the values i get when the view runs in drupal – howo Sep 12 at 15:19
  • If you don’t have a database prefix, then {og_membership} would simply be replaced with og_membership. I’m afraid I don’t really understand the rest of the question. You said you replaced the curly brackets and didn’t get the same results, but from the code you’ve posted, you’ve changed a lot more than just removing the brackets. Hence my earlier question about why you’d expect the same results when you’ve completely changed the query – Clive Sep 12 at 15:37
0

You should remove the square brackets, but also do some other modifications. As this wuold take me too long to debug, you can use hook_views_pre_execute() to view the exact query:

function MYMODULE_views_pre_execute(&$view) {
  dpq($view->build_info['query']);
}

Make sure the Devel module is enabled for this.

  • could you please elaborate a bit more, since i have already found this solution to many previous answers, but how exactly should i run it, where should i give my view name, what MYMODULE has to do here, i do not write a custom module.... Yes i have devel module enabled – howo Sep 12 at 10:59
  • If you are not used to creating custom modules yourself, place the above code at the bottom of the devel.module file (inside the "devel" module). Change the word "MYMODULE" by "devel". Then clear caches and go to the page where your view is located. Then, the query should appear. Make sure you remove the code back afterwards. – Stef Van Looveren Sep 12 at 11:31
  • please see my edit, did not work – howo Sep 12 at 12:04

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.