Orphaned revisions

[murphy1484]

Describe the bug

It seems that supertable blocks nested inside a matrix field are not being deleted when the element revisions are pruned.
When running php craft utils/prune-revisions I get following results for the supertable elements.

- SuperTable Block 29354 (23 revisions) ... done (found 0)
- SuperTable Block 29355 (23 revisions) ... done (found 0)
- SuperTable Block 29356 (23 revisions) ... done (found 0)
- SuperTable Block 29560 (22 revisions) ... done (found 0)
- SuperTable Block 29561 (22 revisions) ... done (found 0)
- SuperTable Block 29562 (22 revisions) ... done (found 0)
- SuperTable Block 29563 (22 revisions) ... done (found 0)
- SuperTable Block 30665 (9635 revisions) ... done (found 0)
- SuperTable Block 30666 (9635 revisions) ... done (found 0)
- SuperTable Block 30667 (9635 revisions) ... done (found 0)

Steps to reproduce

1. Supertable blocks inside matrix field
2. Run php craft utils/prune-revisions --max-revisions=5

Craft CMS version

4.4.15

Plugin version

3.0.12

Multi-site?

Yes

Additional context

No response

[jlawrence-yellostudio]

@engram-design What is the recommended way of cleaning these up? I have over 12000 of them

[engram-design]

Still investigating this, as the built-in pruning utility doesn't seem to cater for Super Table in some instances.

[jlawrence-yellostudio]

@engram-design Thanks for replying. I am not sure this is isolated to Super Table blocks, the following issue could suggest the native matrix does it too:

craftcms/cms#15843

My output is very similar to reported there.

If I query the owner elements (primaryOwnerId) of 775 of these Super Table Block Elements (all ones with 200+ revisions), only 83 of them exist. I believe they are all likely children of MatrixBlocks.

Overall I have 97,217 non orphaned SuperTableBlockElement rows.
When i check my DB for:

SELECT * FROM elements WHERE type = 'verbb\\supertable\\elements\\SuperTableBlockElement' AND id NOT IN(SELECT id FROM supertableblocks)

I get 460k orphaned rows returned with dates ranging from Jan 2021 to a few weeks ago, i am running the latest version of Craft 4.12.5.

As you can imagine, that and the associated data is taking up a fair bit of space across all environments.

[engram-design]

It does seem to include Matrix as that linked issue mentions. I’m chatting to Brandon about it.

[jlawrence-yellostudio]

@engram-design Any movement on this? i would have thought the orphan cleanup utility should remove elements with type verbb\supertable\elements\SuperTableBlockElement that are present in the elements table but not in the supertableblocks table?

Whilst i could delete all these elements, I'm wary of the fact i'll have to keep deleting them as the issue is still current and not a previously fixed bug.