Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
First, the trick:
SELECT DISTINCT c.*,n.title
FROM (
category c
INNER JOIN node n
ON c.cid=n.nid
INNER JOIN category_hierarchy p2
ON c.cid=p2.cid
LEFT OUTER JOIN category_hierarchy p1
ON p1.cid = p2.parent
)
WHERE p2.parent <> 0
AND p1.cid IS NULL;
This code implements a set difference between (all categories in the hierarchy) and (categories in the hierarchy with parents that exist in the hierarchy) to get (categories in the hierarchy with parents that do not exist in the hierarchy.)
- It is db_rewrite_sql compatible. (Tested on mysql 4.1.x with simple_access.)
- It only needs to do a single table scan
This code will not find completely uncategorized cats (as opposed to normal orphans, which are still categorized, but with invalid parents), but it should be possible to do this with another query that finds the set of categories that don't have any mention in the category_hierarchy table.
--Brandon
Comments
Comment #1
bdragon CreditAttribution: bdragon commentedComment #2
bdragon CreditAttribution: bdragon commented