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:

FROM `users` 
WHERE `vid` > (
  SELECT vid 
  FROM `user_revision` 
  WHERE `timestamp` > 1379494886 
  ORDER BY `timestamp` ASC 

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?

  • SelectQuery::condition() doesn't mention that it supports sub queries for the value parameter (in fact it only mentions scalars and array). That's probably the problem
    – Clive
    Oct 28, 2013 at 16:32
  • Yes, I would not expect subqueries to be supported by the PDO, from my experience of how queries are built. You will need to use db_query instead of db_select. Oct 28, 2013 at 17:01
  • Thanks for the comments. I was sure that I did read about subqueries could be used in db_select. Oct 28, 2013 at 17:16
  • drupal.org/node/310086 On this page, under Subselects section, I've read about that, but seemingly it doesn't work well. Oct 28, 2013 at 17:25

2 Answers 2


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();
  • Not sure how relevant this is, but I noticed you remove the LIMIT from the subquery. Interestingly, the Restrictions on Subqueries would've caused that to fail even if PDO had got the syntax right. Not something I've come across before
    – Clive
    Oct 28, 2013 at 18:02
  • I removed the LIMIT to be able to use IN instead of >, since I needed all values returned by the subquery, not only the smallest one. Oct 28, 2013 at 18:07
  • 1
    Ok, it was not directly relevant. To test it I just added the LIMIT back and got another error this time: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery':... Oct 28, 2013 at 18:13

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)

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

$result = $query->execute()->fetchAssoc();
  • This way is no better than the solution I posted. It is almost the same, but using 2 queries instead of just one, so it is worse for performance. I see no point why did you post almost the same answer, splitting it into two queries. Dec 28, 2015 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.