1

I have a taxonomy vocabulary that contains a large number (5000+) of orphaned taxonomy terms. By orphaned I mean that they refer to parent terms that no longer exist, or refer to parents who are themselves orphans (grand-orphans ?). I am not talking about unused terms.

I do not see these terms in the Drupal interface as they are no longer connected to the main hierarchy. I can however still see them in the database, and I can open them directly using the tid in the Drupal interface.

I can't bulk delete them using any module because modules get the entire tree of the vocab to delete - and they are no longer part of the tree. I think I will have to delete them manually in the database. I can delete the terms from the taxonomy_term_data and taxonomy_term_hierarchy tables:

DELETE FROM taxonomy_term_hierarchy WHERE parent NOT IN (SELECT tid FROM taxonomy_term_hierarchy) // or something like it

Are there any other tables I need to think of though, in order to not end up with a database that's messed up worse than it already is?

4

I wouldn't advise doing that - taxonomy terms are entities and need to be cleaned up with taxonomy_term_delete(). Otherwise you'll potentially leave a lot of orphaned data in field tables/contrib module tables/etc.

However, if you know your DELETE query will get you the correct terms, just convert it to a SELECT and you can do things the proper way:

$tids = db_query('SELECT tid FROM taxonomy_term_hierarchy WHERE parent NOT IN (SELECT tid FROM taxonomy_term_hierarchy)')->fetchCol();

foreach ($tids as $tid) {
  taxonomy_term_delete($tid);
}

Be wary that you don't delete top-level terms that have a parent of 0, since that record will not appear in your sub-query. It'll probably need a bit more thought, but the above code won't be too far off what you end up using.

1

The query I ended up using for selecting the orphaned children tid's was the following:

SELECT T1.tid
FROM 
taxonomy_term_hierarchy AS T1 INNER JOIN
taxonomy_term_data ON
T1.tid = taxonomy_term_data.tid LEFT JOIN
taxonomy_term_hierarchy AS T2 ON
T1.parent = T2.tid LEFT JOIN
taxonomy_term_hierarchy AS T3 ON
T2.parent = T3.tid LEFT JOIN
taxonomy_term_hierarchy AS T4 ON
T3.parent = T4.tid LEFT JOIN
taxonomy_term_hierarchy AS T5 ON
T4.parent = T5.tid
WHERE 
((T1.parent NOT IN (SELECT tid FROM taxonomy_term_hierarchy) AND T1.parent != 0) OR
(T2.parent NOT IN (SELECT tid FROM taxonomy_term_hierarchy) AND T2.parent != 0) OR
(T3.parent NOT IN (SELECT tid FROM taxonomy_term_hierarchy) AND T3.parent != 0) OR
(T4.parent NOT IN (SELECT tid FROM taxonomy_term_hierarchy) AND T4.parent != 0) OR
(T5.parent NOT IN (SELECT tid FROM taxonomy_term_hierarchy) AND T5.parent != 0)) AND
taxonomy_term_data.vid = 5

Because I was sure no hierarchy deeper than 5 levels existed in my list. For deeper hierarchies you would add more joins. Obviously the vid is situation specific as well.

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.