3

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?

  • 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 '13 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. – Alfred Armstrong Oct 28 '13 at 17:01
  • Thanks for the comments. I was sure that I did read about subqueries could be used in db_select. – Елин Й. Oct 28 '13 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 '13 at 17:25
7

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 '13 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 '13 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 '13 at 18:13
-1

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();
  • 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 '15 at 9:19

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.