db_select condition with subquery causes PDOexception

Question

I want to build a relative simple select query with a subquery in its condition. What I want to achieve is to get the list of users updated after a given timestamp.

The working SQL-Query which I want to build as dynamic query is:

SELECT uid 
FROM `users` 
WHERE `vid` > (
  SELECT vid 
  FROM `user_revision` 
  WHERE `timestamp` > 1379494886 
  ORDER BY `timestamp` ASC 
  LIMIT 1
);

I've written the following code to build above query with Drupal's Database API:

$subquery = db_select('user_revision', 'ur')
  ->fields('ur', array('vid'))
  ->where('ur.timestamp > 1379494886')
  ->orderBy('ur.timestamp', 'ASC')
  ->range(0, 1);

$query = db_select('users', 'u')
  ->fields('u', array('uid'))
  ->condition('u.vid', $subquery, '>');

$result = $query->execute()->fetchAssoc();

But, I'm getting the following PDOexception 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 'SELECT ur.vid AS vid FROM user_revision ur WHERE (ur.timestamp > 137949488' at line 1: SELECT u.uid AS uid FROM {users} u WHERE (u.vid > SELECT ur.vid AS vid FROM {user_revision} ur WHERE (ur.timestamp > 1379494886) ORDER BY ur.timestamp ASC LIMIT 1 OFFSET 0) ; Array ( ) in MYMODULE_page_callback() (line 22 of /path/to/the/module/includes/page_callback_file.inc).

What am I doing wrong building the subquery?

Answer 1

Thanks to @Clive's comment, I checked the documentation about the subqueries on https://drupal.org/node/310086 and I noticed the note about:

Note: Currently it is only possible to use subselect conditions with the IN operator because with the other operators the sub-query is not wrapped in parentheses and so results in a syntax error. See #1267508: Subselects don't work in DBTNG conditions, except when used as value for IN.

I've changed my code to use IN instead of > and it worked like a charm. Here's the working code for future reference if someone falls into the same situation:

$subquery = db_select('user_revision', 'ur')
  ->fields('ur', array('vid'))
  ->where('ur.timestamp > 1379494886')
  ->orderBy('ur.timestamp', 'ASC');

$query = db_select('users', 'u')
  ->fields('u', array('uid', 'vid'))
  ->condition('u.vid', $subquery, 'IN');

$result = $query->execute()->fetchAll();

Answer 2

You could separate your subquery into another query to return just the value, see below:

$vid = db_select('user_revision', 'ur')
  ->fields('ur', array('vid'))
  ->where('ur.timestamp > 1379494886')
  ->orderBy('ur.timestamp', 'ASC')
  ->range(0, 1)
  ->execute()
  ->fetchField();

$query = db_select('users', 'u')
  ->fields('u', array('uid'))
  ->condition('u.vid', $vid, '>');

$result = $query->execute()->fetchAssoc();