how to use if exists inside db_query

Question

I try to create a condition that checks if user has already created a node.

I use this code which returns the last created node by the user. Adding simple if statement I can return true/false.

$xxx = db_query("SELECT nid FROM {node} WHERE (type = :type1) AND (uid = '123')", array("type1" => 'nodetype_x'))->fetchField();
return $xxx;

I tried to do it more efficient to check only for first result like this.

$xxx = db_query("
IF EXISTS(
      SELECT nid FROM {node} WHERE (type = :type1) AND (uid = '123')
)
SELECT 1
ELSE
SELECT 0", array("type1" => 'nodetype_x'))->fetchField();

But get syntax error. What would be the correct syntax? Thank you

Answer 1

There is no need to use IF EXISTS() with db_query(). Drupal core code never use it.

// comment_num_new()
return db_query('SELECT COUNT(cid) FROM {comment} WHERE nid = :nid AND created > :timestamp AND status = :status', array(
  ':nid' => $nid,
  ':timestamp' => $timestamp,
  ':status' => COMMENT_PUBLISHED,
))->fetchField();

  // aggregrator_cron()
  $result = db_query('SELECT * FROM {aggregator_feed} WHERE queued = 0 AND checked + refresh < :time AND refresh <> :never', array(
    ':time' => REQUEST_TIME,
    ':never' => AGGREGATOR_CLEAR_NEVER,
  ));
  $queue = DrupalQueue::get('aggregator_feeds');
  foreach ($result as $feed) {
    if ($queue->createItem($feed)) {
      // Add timestamp to avoid queueing item more than once.
      db_update('aggregator_feed')->fields(array('queued' => REQUEST_TIME))->condition('fid', $feed->fid)->execute();
    }
  }

  // aggregrator_feed_load()
  $feeds = &drupal_static(__FUNCTION__);
  if (!isset($feeds[$fid])) {
    $feeds[$fid] = db_query('SELECT * FROM {aggregator_feed} WHERE fid = :fid', array(':fid' => $fid))->fetchObject();
  }

  return $feeds[$fid];

// aggregrator_expire()
$iids = db_query('SELECT iid FROM {aggregator_item} WHERE fid = :fid AND timestamp < :timestamp', array(
  ':fid' => $feed->fid,
  ':timestamp' => $age,
))->fetchCol();
if ($iids) {
  db_delete('aggregator_category_item')->condition('iid', $iids, 'IN')->execute();
  db_delete('aggregator_item')->condition('iid', $iids, 'IN')->execute();
}

In your case, the following code is enough.

if ($nid = db_query("SELECT nid FROM {node} WHERE type = :type1 AND uid = '123'", array("type1" => 'nodetype_x'))->fetchField()) {
  // There is such a node.
}
else {
  // There isn't such a node.
}

Answer 2

This is really not the ideal way to do this. Instead you may want to try something like:

$query = db_select('node', 'n')
  ->condition('n.type', $type)
  ->condition('n.uid', $uid)
  ->fields('n', array('nid'));

$count = $query->countQuery()->execute()->fetchField();

if ($count) {
  return true;
}

return false;