Delete all nodes where the taxonomy term matches a given value

Question

I have a content type (example_content_type) with 3 3 fields: title, body, and pks_type. pks_type is a taxonomy term select list field. and 'pks_type'has 2 terms:

• Type1, tid = 1
• Type2, tid = 2

I have 150 nodes using those taxonomy terms. Now I need to delete all nodes from 'example_content_type' except last 5 nodes, where the taxonomy term ID is 1.

How can I achieve this with db_delete()?

Answer 1

I would suggest using EntityFieldQuery as it allows you to pick exactly the nodes you want based on a wide range of criterias (check the documentation here and a tutorial here) and you can exclude the last 5 nodes by adjusting the range of the query itself.

$query = new EntityFieldQuery();

$query->entityCondition('entity_type', 'node')
      ->propertyCondition('bundle', 'example_content_type')
      ->fieldCondition('field_pks_type', 'tid', '1')
      ->propertyOrderBy('created', 'DESC')
      ->range(5);

$result = $query->execute();

if (isset($result['node'])) {
  $nids = array_keys($result['node']);
  node_delete_multiple($nids);
}

In this case the query will find all entities of type node and bundle example_content_type, with the pks_type tid 1 and retrieve them newest first starting with the 6th entry (and thus ignoring the 5 last/most recently entered nodes).

(I added the code for deleting the nodes, though it might be good to print a list of nodes to be deleted or back up the db before actually using the code.)

---

Edit 1:

Just now saw you asked about using db_delete(). In this case you can still use the code above to retrieve the ids of the nodes you need to delete and delete them with db_delete() rather than node_delete_multiple() - however unless you have a specific use case where you need to use db_delete() it is better using the appropriate delete hooks as those will also clear corresponding field tables and allow other modules to react to the node's deletion.

if (isset($result['node'])) {
    $nids = array_keys($result['node']);

    foreach($nids as $nid) {
        db_delete('node')
        ->condition('nid', $nid)
        ->execute();
    }
}

Answer 2

The major problem here is the "except last 5" constraint. I suggest the following solution:

db_select the nodes' nids that you don't want to delete in the first step, like

$savenids = select nid from node where -whatever- limit 5.

Then db_delete the rest of the nodes like

delete from node where -whatever- and nid not in $savenids.

The -whatever- terms strongly depend on your field setup, take a look at your tables for that.