• I read the Generative AI policy and my contribution is either not created with the help of AI or is compatible with the policy and GNU/GPL 2 or later.

Summary of Changes

The Nested table class has been the foundation for the database-stored tree structures in Joomla since version 1.6 and I've encountered several issues since then, which resulted in additional issues later on. There are reports of broken URLs because the menu table or category table got corrupted or broken permissions because the usergroup table or asset table got corrupted. Besides that, ordering often enough got mangled as well and the solution so far was to run the Rebuild task in the different components, which called Nested::rebuild(). While that method did solve a lot of issues, there are a few cases where the method fails and might even make it worse:

• The code right now is recursive, going through the whole table each time, writing each row, even when the data is actually correct. This means that the runtime grows with the total number of rows in the table, regardless of each row being broken or not.
• It clears the lft column in the database and then rebuilds it. If the process runs into a timeout (for example because the dataset is so large) this can leave the table in a worse state than before.
• The code does not fix the path in the same step as well.
• The code does not discover cycles in the tree (a child in the tree is somewhere a parent of its own parent)
• Orphans are silently ignored. (An item which doesn't have a parent going back to the root node)

This PR creates an alternative where the changes are done iterative. Instead of writing each row each time, it first checks the integrity and only modifies those which are broken. This can still mean a large change when there is a hole in the tree, but especially in the normal case, where the table is fine, the rebuild() process would finish a lot quicker.

The benefit is also, that the lft and rgt values are not cleared first and thus the table does not get worse than it was. Most importantly however, they process can recover gracefully when the process aborts somewhere in the middle. When 20k rows are broken and the current process can only fix 5k of those in this run, it will only have to fix the 15k broken rows left from last time. Especially for very large sites, this would allow the process to fix the whole tree eventually.

This proposal here also checks if the table has certain columns like level, alias, path and ordering and adaptively adds the features if present, unlike the current code which requires some of these fields, which is why the Assets table class needed its own version of rebuild().

Important part!!
In terms of performance, this new approach runs a lot faster. While a tree with 20k corrupted rows might take ~20s in the original implementation, this proposal would take ~5s in worst case. However, at the same time the memory consumption rises significantly. Where the recursive approach takes less than 600k of memory regardless of table size, the iterative approach takes about 1k per row it has to process. A table with 150k rows might in worst case be entirely corrupted and thus result in a memory requirement of 150 Megabytes to rebuild it. Then again, in most cases rebuild() would only touch a few rows and most likely run in under 1s. This is something that we should take into consideration.

This class comes with a set of unit- and integration-tests which I separately handed in as PR in #48227 in order to see that they pass or not on the current class and would still pass on the new class. #48227 can be closed when everyone is happy that the tests test what they should do and then we would see that they work correctly on this new implementation.

I think this would also be fully backwards compatible. At least I can't come up with a situation where this would generate issues.

The code and analysis has been written with the help of AI, but reviewed by me and thus should adhere to the AI policy we have.

Testing Instructions

Manual test instructions — Nested::rebuild()

These instructions test Joomla\CMS\Table\Nested::rebuild(), the method that rebuilds the nested set
columns (lft, rgt, level, path) of a tree table from its parent_id links.

rebuild() is not called directly by a user. It sits behind the Rebuild toolbar button in three
places, behind every save and reorder of a nested item, and behind component installation. The tests
below drive it through all of those.

⚠️ Before you start

Use a test site and take a database dump first.

rebuild() writes to #__menu, #__categories and #__tags. If it goes wrong on #__menu you can
lock yourself out of the administrator, because the admin menu is built from that table. Several tests
below deliberately corrupt a tree, so a restore point is not optional.

mysqldump -u USER -p DATABASE > before-test.sql

Replace #__ with your real table prefix (for example jos_) in every SQL snippet.

Prerequisites

• A Joomla test site with sample data installed — you need a tree that is more than two levels
  deep. "Blog sample data" or "Testing sample data" both work.
• A Super User account. The Rebuild button is only rendered for users with the core.admin
  permission.
• Access to the database (phpMyAdmin, Adminer, or a CLI client).
• Ideally two runs: one on MySQL/MariaDB and one on PostgreSQL.

The integrity checks

You will run this set of queries repeatedly. Save them somewhere. Substitute the table under test for
#__categories — the same six queries work for #__menu and #__tags.

1. Exactly one root, numbered from zero

SELECT id, parent_id, lft, rgt, level, path FROM `#__categories` WHERE parent_id = 0;

Expected: exactly one row, with lft = 0, level = 0 and an empty path.

2. The numbering is dense

SELECT COUNT(*) AS nodes, MIN(lft) AS min_lft, MAX(rgt) AS max_rgt FROM `#__categories`;

Expected: min_lft = 0 and max_rgt = 2 * nodes - 1. A tree of 30 rows must end at 59.

3. Every node has lft < rgt

SELECT id, title, lft, rgt FROM `#__categories` WHERE lft >= rgt;

Expected: no rows.

4. No number is used twice

SELECT v, COUNT(*) AS uses FROM (
    SELECT lft AS v FROM `#__categories`
    UNION ALL
    SELECT rgt AS v FROM `#__categories`
) x GROUP BY v HAVING COUNT(*) > 1;

Expected: no rows.

5. Every child sits strictly inside its parent, one level below it

SELECT c.id, c.title, c.lft, c.rgt, c.level, p.lft AS p_lft, p.rgt AS p_rgt, p.level AS p_level
FROM `#__categories` c
INNER JOIN `#__categories` p ON c.parent_id = p.id
WHERE c.lft <= p.lft OR c.rgt >= p.rgt OR c.level <> p.level + 1;

Expected: no rows.

6. The path is the parent path plus the alias

SELECT c.id, c.title, c.path, p.path AS parent_path, c.alias
FROM `#__categories` c
INNER JOIN `#__categories` p ON c.parent_id = p.id
WHERE c.path <> CASE WHEN p.path = '' THEN c.alias ELSE CONCAT(p.path, '/', c.alias) END;

Expected: no rows. On PostgreSQL use || instead of CONCAT().

7. No orphans (informational — rebuild() does not repair these)

SELECT c.id, c.title, c.parent_id
FROM `#__categories` c
LEFT JOIN `#__categories` p ON c.parent_id = p.id
WHERE c.parent_id <> 0 AND p.id IS NULL;

Expected on a healthy site: no rows. If there are rows, note them — they are invisible to
rebuild() and will still be wrong afterwards. That is existing behaviour, not a regression.

Test 1 — Rebuild categories

1. Go to Content → Categories.
2. Run all seven checks above against #__categories and note the results. This is your baseline.
3. Click Actions → Rebuild in the toolbar.
4. Expected: the message "Rebuild completed." and no error.
5. Run all seven checks again.

Expected result: every check still passes and the category list looks exactly as it did before —
same order, same indentation, same nesting.

Note: the Rebuild button rebuilds the whole #__categories table, not just the extension you
are currently filtering on. Categories belonging to Contacts, Banners and News Feeds are renumbered
too. Check those list views as well.

Test 2 — Rebuild menu items

This is the highest-risk table. Do it after Test 1, and keep your dump handy.

1. Go to Menus → All Menu Items (or any single menu).
2. Run the checks against #__menu.
3. Click Rebuild in the toolbar.
4. Expected: "Rebuild completed."
5. Run the checks again.
6. Reload the administrator. The admin sidebar menu must still render correctly.
7. Open the site front end. All menu modules must still show the same items in the same order.

Expected result: checks pass, both menus render, no item has moved between menus.

#__menu holds every menu in one tree — the site menus, the administrator menu and the hidden
"Menu_Item_Root". A rebuild renumbers all of them together. Verify at least two different menutypes.

Test 3 — Rebuild tags

1. Create a small nested tag tree under Components → Tags if you do not already have one:
   Sport → Football → Rules, plus a second top-level tag Culture.
2. Run the checks against #__tags.
3. Click Rebuild in the toolbar.
4. Run the checks again.
5. Open Components → Tags and confirm the tree still shows the same nesting and indentation.

Expected result: checks pass, tree unchanged.

Test 4 — Saving a nested item (subtree rebuild)

Saving an item calls rebuild() on that item's subtree only, with an explicit start node.

1. Content → Categories, open a category that has children and grandchildren.
2. Change its Alias to something new and click Save & Close.
3. Run check 6 against #__categories.

Expected result: the changed category and every descendant now carry the new alias in their
path. A grandchild that was sport/football/rules becomes athletics/football/rules if you renamed
sport to athletics. No other branch changed.

4. Now move that category to a different parent (change Parent and save).
5. Run all seven checks.

Expected result: all checks pass, and the whole moved subtree appears under the new parent in the
list view with the correct indentation.

Repeat both steps for a menu item and for a tag.

Test 5 — Reordering

1. Content → Categories, sort the list by Ordering (the ⋮⋮ column header).
2. Drag a category with children to a different position among its siblings.
3. Run all seven checks.

Expected result: checks pass, the category keeps its children, and the new order survives a page
reload.

Repeat for Menus → All Menu Items.

Test 6 — Repairing a broken tree

This is what rebuild() exists for. Only parent_id is trusted; everything else must be recomputed.

1. Break the category tree on purpose:

UPDATE `#__categories` SET lft = 0, rgt = 0, level = 0, path = '';

2. Reload Content → Categories. The list will be wrong — flat, mis-ordered, or empty.
3. Click Actions → Rebuild.
4. Run all seven checks.

Expected result: every check passes again and the list view is restored to the correct tree. The
path column is rebuilt from the aliases, so it must match check 6 exactly.

5. Break it a second way — scramble the numbering rather than clearing it:

UPDATE `#__categories` SET lft = lft + 1000, rgt = rgt + 1000 WHERE level > 1;

6. Rebuild again and re-run the checks.

Expected result: identical to step 4.

Test 7 — Component install and uninstall

Installing an extension inserts administrator menu items and rebuilds #__menu.

1. Install any small extension that adds an administrator menu entry.
2. Confirm the new entry appears in the admin sidebar.
3. Run the checks against #__menu.
4. Uninstall the extension.
5. Run the checks again.

Expected result: checks pass both times, the sidebar is correct both times, and no leftover menu
row is orphaned (check 7).

Test 8 — Smart Search taxonomy

The indexer rebuilds the taxonomy tree.

1. Go to Components → Smart Search → Index.
2. Click Index and let it finish.
3. Run the checks against #__finder_taxonomy.
4. Open Components → Smart Search → Search Filters → New and confirm the taxonomy branches and
   their nodes are listed correctly.

Expected result: checks pass, branches show their nodes.

Test 9 — Large tree (optional, MySQL 8 / MariaDB 10.2+)

Only worth doing if the PR claims a performance or memory change.

1. Create 2 000 test categories under the root:

INSERT INTO `#__categories`
    (parent_id, lft, rgt, level, path, extension, title, alias, note, description,
     published, checked_out, checked_out_time, access, params, metadesc, metakey, metadata,
     created_user_id, created_time, modified_user_id, modified_time, hits, language, version)
WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 2000)
SELECT 1, 0, 0, 0, '', 'com_content', CONCAT('Perf test ', n), CONCAT('perf-test-', n), '', '',
       1, NULL, NULL, 1, '{}', '', '', '{}', 42, NOW(), 42, NOW(), 0, '*', 1
FROM seq;

2. Click Actions → Rebuild and note how long the page takes.
3. Run all seven checks.
4. Clean up:

DELETE FROM `#__categories` WHERE alias LIKE 'perf-test-%';

5. Click Rebuild once more and re-run the checks.

Expected result: the rebuild completes without a PHP memory or execution-time error, all checks
pass, and after the cleanup rebuild the numbering is dense again (check 2).

If you want to record numbers for the PR, set error_reporting to show fatals and watch for
Allowed memory size ... exhausted and Maximum execution time — those are the two failure modes
worth reporting, together with your memory_limit, max_execution_time and the node count.

What to report back

Please state:

• Which of tests 1–10 you ran, and the result of each.
• For any failing check: the number of the check, the table, and the rows it returned.
• Whether the administrator and the front end still rendered correctly after Test 2.

A "before" and "after" screenshot of the Categories or Menu Items list view is helpful when the tree
looks wrong, since a broken lft/rgt usually shows up as wrong indentation or a wrong order rather
than as an error message.

Link to documentations

Please select:

• Documentation link for guide.joomla.org:

• No documentation changes for guide.joomla.org needed

• Pull Request link for manual.joomla.org:

• No documentation changes for manual.joomla.org needed