Add ORM abstraction for "WITH" clauses

[GuySartorelli]

MySQL 8 supports WITH and WITH RECURSIVE statements, which opens up a whole load of opportunities.

The following methods could benefit from recursive queries (though some would have to be behind opt-in config flags for BC):

• SiteTree::get_by_link()
• SiteTree::relativeLink()
• SiteTree::isOrphaned()
• SiteTree::InSection()
• SiteTree::getBreadcrumbItems()
• SiteTree::NestedTitle()
• SiteTree::Level()
• SiteTree::getPageLevel()
• SiteTree::getMenu()
• Hierarchy::getAncestors()

Note that the above list was made after a very quick look at SiteTree and is far from exhaustive. I haven't looked pretty much anywhere else in core code.

Any usage of WITH clauses in core code will either need to be behind a condition checking if the current db driver supports it, or we'll have to explicitly say MySQL5.7 is no longer supported (it does go EOL in October 2023). But regardless of usage of the functionality, we can still provide an API for it, and anyone with an up-to-date db server can use it for their own purposes.

I've started a POC implementation here: GuySartorelli/silverstripe-framework@5...pulls/5/recursive-orm-queries

Acceptance criteria

• An abstract way of using the WITH clause is added to the ORM. This abstraction should be implemented on SQLSelect and surfaced in DataQuery.
• There's a compatible implementation for MySQL 8.
• There's a way to check if that DB doesn't support the WITH clause or hasn't implemented the feature yet in their PHP library.
• There's clear documentation on how to use the WITH abstraction for modules who can't assume what DB their end-project are using.
• Create a follow up card to identify where we can use this in our own codebase.

Note

• We're not sure if there's a sensible way to directly use the WITH clause from DataList directly. We'll discover this in the process of implementing this card and potentially spin up a follow up card if needed. DataList does not need its own abstraction layer at this stage. It can be added as a follow-up in the future if the need arises and a sensible implementation is identified.
• Identify areas of the code that could be improved with Common Table Expressions (aka WITH clauses) #10962 has been created as a follow-up for this.

PRs

• ENH Allow selecting multiple (or no) tables #10953
• NEW Add abstraction for sql RIGHT JOIN #10954
• NEW Add sql UNION abstraction #10956
• ENH Enable allowing collisions for field statements #10957
• NEW Add rightJoin method to DataList #10961
• NEW Add ORM abstraction for "WITH" clauses #10943
• DOC Document ORM changes for CMS 5.2 developer-docs#351

[emteknetnz]

Linked PRs have been merged