| Warning |
|---|
This will destroy your system completely.. not for the faint hearted. |
Find out about categories in the categories table but NOT in the nested tree model. This is probably pretty harmless (unless you appear to be mmissing categories all of a sudden) but can cause 3.x versions of FarCry to fail when trying to render the category picker.
...
| Code Block |
|---|
| sql |
|---|
| sql |
|---|
| title | SQL to see missing categories |
|---|
|
DELETE
FROM categories c
WHERE
categoryid NOT IN (SELECT objectid FROM nested_tree_objects)
|
However, you may have some orphaned categories in the nested tree model. ie those category references that have lost their parent (maybe through a deletion that didn't finish properly). You can grab a list of orphaned nested tree references using the following:
| Code Block |
|---|
| sql |
|---|
| sql |
|---|
| title | SQL to check for orphan tree references |
|---|
|
SELECT *
FROM nested_tree_objects
WHERE
parentid NOT IN (SELECT objectid FROM nested_tree_objects)
AND parentid <> ''
|
Rather than deleting everything you could just find the object references a new parent!
| Code Block |
|---|
| sql |
|---|
| sql |
|---|
| title | SQL to update orphans with a new parent |
|---|
|
UPDATE nested_tree_objects
SET
parentid='--some UUID of valid parent--'
WHERE
objectid IN (select objectid from nested_tree_objects
where
parentid NOT IN (select objectid from nested_tree_objects))
AND (parentid is not null or parentid <> '')
|