19

I'm using the flag module to let users mark their questions as solved, similar to how it works on this site. I therefore want to let users filter posts to show only unsolved questions. Additionally, some posts might not be questions at all. Therefore, they can't have a "solved" flag, but they also should NOT appear in the results when filtering only by unsolved questions. Therefore, I need to join the node table with two others: flag_content and field_data_field_question (this latter table is what tells whether or not a post is a question).

Here's the current code I'm trying:

$query->join('flag_content', 'f', 'f.content_id = n.nid AND f.content_type = "node" AND f.fid = 5');
$query->join('field_data_field_question', 'q', 'q.entity_id = n.nid AND q.field_question_value = 1');
$query->condition('f.fid', 'NULL', 'IS');
$query->condition('q.field_question_value', 'NULL', 'IS NOT');

However, this results in the following error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''NULL') AND (q.field_question_value IS NOT 'NULL')
LIMIT 21 OFFSET 0' at line 4: SELECT n.nid AS nid
FROM
{node} n
INNER JOIN {field_data_field_category} t ON t.entity_id = n.nid
INNER JOIN {flag_content} f ON f.content_id = n.nid AND f.content_type = "node" AND f.fid = 5 
INNER JOIN {field_data_field_question} q ON q.entity_id = n.nid AND q.field_question_value = 1
WHERE (t.field_category_tid = :db_condition_placeholder_0) AND (f.fid IS :db_condition_placeholder_1) AND (q.field_question_value IS NOT :db_condition_placeholder_2)
LIMIT 21 OFFSET 0; Array 
( [:db_condition_placeholder_0] => 464 [:db_condition_placeholder_1] => NULL [:db_condition_placeholder_2] => NULL
)
in queryExecuteRender_recentActivity() (line 57 of someFile.php).

Is this not the correct way to pass NULL values into a query?

31

The database API actually has a preferred way to add expressions of this type, namely the SelectQuery::isNull() and SelectQuery::isNotNull() functions. You can use them like this:

$query->isNull('f.fid');

$query->isNotNull('q.field_question_value');

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.