I'm trying to use SQL queries generated by views in another application but it appears the SQL queries views displays aren't what views actually runs.

I have a node called "asset" and a field called "asset_url". The SQL returned is:

SELECT node.title AS node_title, node.nid AS nid, 'node' AS field_data_field_asset_url_node_entity_type
FROM 
{node} node
WHERE (( (node.type IN  ('asset')) ))

The first thing I have to do to get this query to run is remove {node} I presume this is some type of token that's getting left in?

But the returned results doesn't include the content in the field. It just returns the word 'node' for some reason. I believe fields are store in a separate table so I'd be expecting a join in the sql that doesn't appear to be there?

+------------+-----+---------------------------------------------+
| node_title | nid | field_data_field_asset_url_node_entity_type |
+------------+-----+---------------------------------------------+
| test1 | 4 | node |
| new2 | 5 | node |
+------------+-----+---------------------------------------------+

Comments

SeanBannister’s picture

SeanBannister CreditAttribution: SeanBannister commented
Status: Active » Closed (works as designed)

Had a chat in #drupal-views

[12:47am] SeanBannister: that's the indented behavior
[12:48am] SeanBannister: the actual content is loaded via an extra entity_load_multiple

SeanBannister’s picture

SeanBannister CreditAttribution: SeanBannister commented
Issue summary: View changes

-

Wolf_22’s picture

Wolf_22 CreditAttribution: Wolf_22 commented

Sean, did you ever figure out a way to get the REAL query? I saw your Drupal StackExchange comment here: http://drupal.stackexchange.com/questions/29116/get-the-actual-query-exe...

...and wanted to see what you found (if anything)?

I'm also trying to use a query that our beloved Views has created and when I preview everything on the Views side, everything outputs as expected, lo-and-behold, when I copy the query and try to execute it anywhere else--even in template.php--it bombs and outputs the word "node" in places that I'm assuming it shouldn't.

Any insight into this might save me from balding. ;.)

SeanBannister’s picture

SeanBannister CreditAttribution: SeanBannister commented

Hey Casey, unfortunately no I never made progress on this. Let me know if you have any luck.

johnnydarkko’s picture

johnnydarkko CreditAttribution: johnnydarkko commented
Issue summary: View changes

Wolf_22/SeanBannister - have either of you ever come up with any new ideas on this?

I tried to spit out the query in pre_execute, post_execute, pre_render, post_render but it all yields the same query using:

<?php
dpq($view->build_info['query']);
?>

Maybe it would be helpful to log/tail all queries temporarily?
Following http://dev.mysql.com/doc/refman/5.1/en/query-log.html, I edited Acquia Dev Desktop's my.cnf file by adding this directive under [mysqld]:
general_log_file=“/tmp/add-mysql.log”
And in mysql, I set the general_log to 1:

mysql> SET global general_log = 1;
Query OK, 0 rows affected (0.00 sec)

Restated ADD, but no luck--the file is not created when a view would be rebuilt. Maybe I'm going about this the wrong way.

Any other ideas/hints/tips/answers are welcome!

SeanBannister’s picture

SeanBannister CreditAttribution: SeanBannister commented

johnnydarkko: Haven't had a chance to look unfortunately and don't really have any advice in this area however it is something I'll be needing to figure out by the end of the year for another project.

N30’s picture

N30 CreditAttribution: N30 commented

I've created a function based on devel method "dpq" to return the query (as string) from view and be able to run it it db_query(), 

function extractQuery($query, $return = FALSE) {

    if (method_exists($query, 'preExecute')) {
        $query->preExecute();
    }
    $sql = (string) $query;
    $quoted = array();
    $connection = Database::getConnection();
    foreach ((array) $query->arguments() as $key => $val) {
        $quoted[$key] = $connection->quote($val);
    }
    $sql = strtr($sql, $quoted);
    if ($return) {
        return $sql;
    }

    return ($return ? NULL : $query);
}

$view = views_get_view('view_name', TRUE);
$view->set_arguments(array());
$view->use_pager();
$view->set_items_per_page(0);
$view->build();

$q = extractQuery($view->build_info['query'], TRUE);
$q = db_query($ress);

// Rest of the code