Subselects don't work in DB conditions... [#369314]

1) Trying to write the DB API equivalent of: DELETE FROM {node_revisions} WHERE nid IN (SELECT nid FROM {node} WHERE type = 'my_type')

Tried the following:

  // Create subselect for use in condition.
  $query = db_select('node', 'n')
    ->fields('n', array('nid'))
    ->condition('type', 'my_type');

  // Delete any existing records.
  db_delete('node_revision')
    ->condition('nid', $query, 'IN')
    ->execute();

The error returns with delete query equal to: DELETE FROM {node_revision} WHERE (nid IN ()) - Array ( [:db_condition_placeholder_16] => my_type )

2) Per the comments in 'subselects' section of Conditional clauses, a join would be preferable to a subselect in the where clause. However the API does not currently support such a statement. See issue #369253: Add alias parameter to other db_ functions.

3) Also, I did not notice a subselect in the unit tests. If this is confirmed as a bug, then an issue about adding subselect tests can be added.

4) If this is a bug for delete, it may also exist for the other statement types: select, insert, update, merge.

Comment	File	Size	Author
#4	condition_select.patch	2.52 KB	berdir

Comments

Comment #1

dave reid

he/him

English

Nebraska USA

commented 4 February 2009 at 21:43

You're misusing the condition function. In your example code, it would work if $query was an array, but you are passing an database query object. Also you're using db_select() for a very simple query, so the use of db_query() is preferred. This is the proper way to do it:

$nids = db_query("SELECT nid FROM {node} WHERE type = :type", array(':type' => 'my_type'))->fetchCol();
db_delete('node_revision')
  ->condition('nid', $nids, 'IN')
  ->execute();

Comment #2

solotandem commented 4 February 2009 at 22:05

1) The db_select code was tried per the comments in the documentation at Conditional clauses which indicates placing the db_select object in the $variables position. To quote:

condition() also supports subselects as the $value. To use a subselect, first construct a SelectQuery object created by db_select(). Then, instead of executing the Select query pass it into the value parameter of the condition() method of another query. It will automatically get integrated into the main query when it is executed.

2) If $nids has lots of values, then I would think it preferable to have the database excecute the subselect as part of the delete query instead of sorting through 500 ids inside the IN clause. Hence the reason for trying a subselect.