Extracting Views SQL

Question

I would like to create a MySQL view for another application looking at the Drupal Database. I thought I would get a jump start on figuring out the select statement needed by creating a Drupal View, then copying the generated SQL output. After doing this however, it doesn't seem that the actual SQL statement is outputted. From Drupal views it is showing:

SELECT node.title AS node_title, node.nid AS nid, node.uid AS node_uid, node.created AS node_created, 'node' AS field_data_field_ticket_category_node_entity_type, 'node' AS field_data_field_client_username_node_entity_type, 'node' AS field_data_field_ticket_hostname_node_entity_type, 'node' AS field_data_field_ticket_priority_node_entity_type, 'node' AS field_data_field_ticket_source_node_entity_type, 'node' AS field_data_field_ticket_type_node_entity_type, GREATEST(node.changed, node_comment_statistics.last_comment_timestamp) AS node_comment_statistics_last_updated, 'node' AS field_data_field_ticket_owner_node_entity_type, 'node' AS field_data_field_vis_group_node_entity_type, 'node' AS field_data_field_ticket_status_node_entity_type, 'normalizedticket:page' AS view_name
FROM 
{node} node
INNER JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE (( (node.status = '1') AND (node.type IN  ('ticket')) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

I don't understand what it is doing here: 'node' AS field_data_field_vis_group_node_entity_type Is views doing some additional processing after this to get the data? Is there a way to get a query that when run against MySQL directly, it will output similar results to that of views?

Answer 1

I believe your primary question is:

Is there a way to get a query that when run against MySQL directly, it will output similar results to that of views?

You're probably getting an error when you try to run the query when copied directly from Views UI. Change the {node} to myprefix_node, changing myprefix_ to the prefix you gave your tables. If you didn't supply a prefix than simply replace {node} with node.

To respond to your other question:

I don't understand what it is doing here: 'node' AS field_data_field_vis_group_node_entity_type Is views doing some additional processing after this to get the data?

Views creates aliases for the columns that are returned by the query so that it can more easily reference it later on. In this case it is creating a hard-coded value of "node" for each result, which is interesting. And the hard-coded value has an alias of field_data_field_vis_group_node_entity_type.