convert to db_query and addExpression

Question

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();

Answer 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();