Hello!
I faced a problem trying to use SQL query (via db_query) generated by Views (latest -dev version). Error message:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node' in 'on clause': SELECT node.nid AS nid FROM {node} node LEFT JOIN {field_data_field_solving_status} field_data_field_solving_status ON node.nid = field_data_field_solving_status.entity_id AND (field_data_field_solving_status.entity_type = node AND field_data_field_solving_status.deleted = 0) WHERE (( (node.type IN ('dsopic')) AND (field_data_field_solving_status.field_solving_status_value = '0') )); Array ( ) in include_once()

This is because there were no quotation marks around node in Views-generated query:
SELECT node.nid AS nid
FROM
{node} node
LEFT JOIN {field_data_field_solving_status} field_data_field_solving_status ON node.nid = field_data_field_solving_status.entity_id AND (field_data_field_solving_status.entity_type = node AND field_data_field_solving_status.deleted = 0)
WHERE (( (node.type IN ('dsopic')) AND (field_data_field_solving_status.field_solving_status_value = '0') ))

If we'll use such a query instead, error will gone:
SELECT node.nid AS nid
FROM
{node} node
LEFT JOIN {field_data_field_solving_status} field_data_field_solving_status ON node.nid = field_data_field_solving_status.entity_id AND (field_data_field_solving_status.entity_type = 'node' AND field_data_field_solving_status.deleted = 0)
WHERE (( (node.type IN ('dsopic')) AND (field_data_field_solving_status.field_solving_status_value = '0') ))

By the way, View itself is absolutely OK, it shows appropriate nodes. That's why I marked this as the interface problem.

CommentFileSizeAuthor
#3 1185914-fix.patch651 bytesdawehner

Comments

dawehner’s picture

Solving this seems to be pretty hard, because this is somehow a problem of the database system in drupal7

renat’s picture

Well, maybe we should document this somehow? For example, small inscription under the query can save a lot of time for users, who do not fully understand SQL. Can't you say, missed quotes is the only possible problem, or D7 can give us more related issues with the query in some case?

dawehner’s picture

Status: Active » Needs review
StatusFileSize
new651 bytes

Here is a patch

renat’s picture

Dereine, thank you!
Your patch helped me against 7.x-3.0-rc1 (though it crashed site with dev version of Views from 2011-Jun-17, but I think it is by design, because this dev is older than rc1). Now we have this quotes at appropriate place.

dawehner’s picture

Status: Needs review » Fixed

Thanks for testing the patch!

Commited to 7.x-3.x

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.