0

In Drupal (v7.22) I'm trying to modify a query in a module so that it checks the value of an additional field from another table while looking for a result. I've been going through the documentation but cannot seem to stop my version causing an error. The original working code was:

$result = db_query("SELECT nid, type FROM {node}
WHERE type IN (:type) AND LOWER(title) = LOWER(:title)",
array(':type' => $node_types, 'title' => $page_name,));

I've then added in a left join:

$result = db_query("SELECT nid, type FROM {node} AS entity_id
LEFT JOIN {field_data_field_test}
AS nid ON node.nid = field_data_field_test.entity_id
WHERE node.type IN (:type) AND LOWER(node.title) = LOWER(:title)
AND field_data_field_test.field_test_value = :test_value",
array(':type' => $node_types, 'title' => $page_name, 'test_value' => $tvalue,));

But it gives the error:

PDOException: SQLSTATE[42S22]:
Column not found: 1054 Unknown column 'node.type' in 'where clause': ...

Any help would be appreciated.

3

You made 2 mistakes:

  1. For complicated queries, use db_select(), not db_query(). That way you will use enough of built-in failsafes to know what exactly went wrong.

  2. If you insist on db_query(), or need an UNION or something db_select() simply cannot create, never use unbracketed full table name. That's why aliasing exists.

Your query should look about like that:

$result = db_query("SELECT nid, type FROM {node} n
LEFT JOIN {field_data_field_test} f
ON n.nid = f.entity_id
WHERE n.type IN (:type) AND LOWER(n.title) = LOWER(:title)
AND f.field_test_value = :test_value",
array(':type' => $node_types, 'title' => $page_name, 'test_value' => $tvalue,));
0

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.