2

Is there a way convert this static query in a dynamic query?

$sql = "SELECT n.nid, n.title, n.created, n.type, n.status, IF((SELECT nid FROM `newsletter_lists` WHERE cid=$cid AND nid=n.nid), TRUE, FALSE) AS included FROM `node` AS n
  LEFT JOIN `newsletter_lists` AS nl ON n.nid = nl.nid";

This is what I have, but it throws errors.

$query = db_select('node', 'n')
  ->fields('n', array('nid', 'title', 'created', 'type', 'status'))
  ->extend('PagerDefault')
  ->limit(5);

$subquery = db_select('newsletter_lists', 'snl')
  ->fields('snl', array('nid'))
  ->condition('cid', $cid)->condition('nid', `n.nid`)
  ->execute();

$query->addExpression($subquery, 'included');

return $query->execute();
0
1

The problem is that addExpression() doesn't take a SelectQueryInterface as a parameter, but only a string:

$expression: The expression string. May contain placeholders.

So the best you're going to be able to do is add the expression as a manual string. As the argument description suggests placeholders are fine so you can at least keep a bit of structure.

$query = db_select('node', 'n')
    ->fields('n', array('nid', 'title', 'created', 'type', 'status'))
    ->extend('PagerDefault')
    ->limit(5);

$expression = 'IF((SELECT nid FROM `newsletter_lists` WHERE cid= :cid AND nid = n.nid), TRUE, FALSE)';

$query->addExpression($expression, 'included', array(':cid' => $cid));

return $query->execute();
1
  • nice, that works – inv Dec 7 '12 at 10:17

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.