1

How do I get complete SQL query from Views, the one I could run in mysql directly.For example biggest stumbling block is fields part. Like below

SELECT users_node.uid AS users_node_uid, node.created AS node_created, node.nid AS nid, users_node.mail AS users_node_mail, 'user' AS field_data_field_first_name_user_entity_type, 'user' AS field_data_field_last_name_user_entity_type, 'node' AS field_data_field_purchase_date_node_entity_type, 'node' AS field_data_field_serial_number_node_entity_type
FROM 
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
WHERE (( (node.status = '1') AND (node.type IN  ('product_registration')) ))
ORDER BY node_created DESC
LIMIT 50 OFFSET 0

this lines are problematicenter image description here field_data_field_first_name_user_entity_type, field_data_field_last_name_user_entity_type etc

They are not real fields in DB, they somehow get rewritten somewhere, but I can't find where and how. How to get the FINAL query to DB in drupal when using views?

p.s. in image, you can see what I get for those fields in phpmyadmin if I run query. Not really useful.

  • these are just alias nothing else. You could execute the query in phpmyadmin or any other tool without any modification – Shabir A. Dec 29 '15 at 12:36
  • I can execute, but this aliases dont lead to nowhere, they just print out name of the entity, instead of values. – Marko Blazekovic Dec 30 '15 at 9:50
0

Try the Views Show Query module

This module shows real SQL query in Views UI during live preview.

'Real query' means it is rewritten by query_alter hooks. For instance, node access checks are added, so this requires selecting the user on behalf of which the query is supposed to be executed. So the module adds 'User ID to show final query' field in views preview form. You must enter numeric user ID in this field to view the real SQL query.

This module makes it possible to show SQL query not only during live preview, but when the view is executed normally. You can add footer to the view with the following PHP code:

<?php
$rows = array();
$view->exposed_input['views_show_query_uid'] = 1;
views_show_query_views_preview_info_alter($rows, $view);
drupal_set_message($rows['query'][0]);
?>
  • No this doesnt help. I tried this module and query is still the same, this aliases don't change in this case. – Marko Blazekovic Dec 30 '15 at 9:53

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.