Add CTE strategy for eager loading HasMany/BelongsToMany associations by dereuromark · Pull Request #19318 · cakephp/cakephp

dereuromark · 2026-03-07T18:31:53Z

Summary

This PR adds a new 'cte' strategy for eager loading HasMany and BelongsToMany associations that uses Common Table Expressions instead of WHERE IN clauses.

Refs #19107

Problem

When eager loading with large result sets, the current select strategy generates queries like:

SELECT * FROM articles WHERE author_id IN (1, 2, 3, ... 50000);

This has several issues:

Packet size limits: MySQL's max_allowed_packet (default 64MB) can be exceeded
Parsing overhead: Database must parse thousands of literal values
Memory round-trip: IDs fetched to PHP → converted to string → sent back to DB → parsed again

Solution

The new CTE strategy generates:

WITH _cte_Authors AS (
    SELECT id FROM authors WHERE active = 1
)
SELECT articles.* 
FROM articles
INNER JOIN _cte_Authors ON articles.author_id = _cte_Authors.id;

Performance Comparison (MySQL 8.0)

Benchmark comparing all three eager loading strategies with varying parent record counts:

Parent Records	Strategy	Time (ms)	Memory (MB)
1,000	select	21.88	3.33
	subquery	14.79	0.00
	cte	17.20	0.00
5,000	select	114.48	13.33
	subquery	105.10	0.00
	cte	111.57	0.67
10,000	select	277.05	16.67
	subquery	234.85	0.00
	cte	233.17	0.67
25,000	select	745.30	49.33
	subquery	591.48	0.00
	cte	627.62	0.67

Key findings:

Both subquery and cte are faster than select for large datasets
select uses significantly more PHP memory (holds all IDs in array)
At 10K+ records, subquery and cte perform similarly
CTE advantage: avoids packet size limits that could hit both select and subquery with very large ID sets

Usage

// In Table class
$this->hasMany('Articles', ['strategy' => 'cte']);

// Or at query time
$authors = $this->Authors->find()
    ->contain(['Articles' => ['strategy' => 'cte']])
    ->where(['Authors.active' => true])
    ->all();

When to Use

Large result sets (1,000+ parent records)
Complex parent query conditions
Memory-constrained PHP environments
High-frequency queries
Scenarios where max_allowed_packet limits are a concern

Compatibility

CTEs are supported in MySQL 8.0+, PostgreSQL, SQLite 3.8.3+, and SQL Server. CakePHP 5.x already requires modern DB versions with CTE support.

This adds a new 'cte' strategy for eager loading that uses Common Table Expressions instead of WHERE IN clauses. This is beneficial for large result sets as it: - Avoids packet size limits from large WHERE IN clauses - Reduces PHP memory usage by keeping IDs in the database - Allows the database to optimize the join more effectively Usage: ```php $this->hasMany('Articles', ['strategy' => 'cte']); // Or at query time $query->contain(['Articles' => ['strategy' => 'cte']]); ``` Refs #19107

ADmad · 2026-03-07T19:24:46Z

This should target 5.next.

Copilot

Pull request overview

Adds a new eager-loading strategy ('cte') for HasMany and BelongsToMany associations that uses a Common Table Expression + join instead of WHERE IN (...), aiming to improve performance/memory usage for large parent result sets.

Changes:

Introduces Association::STRATEGY_CTE and enables it for HasMany/BelongsToMany.
Implements CTE-based filtering in SelectLoader via with() + innerJoin() against the CTE.
Adds HasMany test coverage for the new strategy (guarded by driver CTE support).

Reviewed changes

Copilot reviewed 5 out of 5 changed files in this pull request and generated 2 comments.

Show a summary per file

File	Description
`src/ORM/Association.php`	Adds the public `STRATEGY_CTE` constant.
`src/ORM/Association/HasMany.php`	Allows the `cte` strategy for hasMany eager loading.
`src/ORM/Association/BelongsToMany.php`	Allows the `cte` strategy for belongsToMany eager loading.
`src/ORM/Association/Loader/SelectLoader.php`	Implements CTE-based filtering/join for eager loading.
`tests/TestCase/ORM/Association/HasManyTest.php`	Adds tests covering `HasMany` eager loading using `cte`.

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

Copilot · 2026-03-08T05:56:31Z

        if ($useSubquery) {
            $filter = $this->_buildSubquery($selectQuery);
            $fetchQuery = $this->_addFilteringJoin($fetchQuery, $key, $filter);
+        } elseif ($options['strategy'] === Association::STRATEGY_CTE) {
+            $cteQuery = $this->_buildSubquery($selectQuery);
+            $fetchQuery = $this->_addFilteringCTE($fetchQuery, $key, $cteQuery);
        } else {


The new cte eager-loading branch doesn't validate that the current driver supports CTEs. If users set strategy => 'cte' on an unsupported DB/version, the generated SQL will include a WITH clause and fail at runtime with a database error. Consider failing fast (eg. throw DatabaseException with a clear message) or transparently falling back to subquery/select when !$fetchQuery->getDriver()->supports(DriverFeatureEnum::CTE).

This is an opt-in configuration - developers explicitly choose this strategy. A reasonable counterargument is that developers should know their database capabilities.

ADmad · 2026-03-08T06:11:47Z

@dereuromark Could add performance comparison when using the subquery strategy too?

Addresses review feedback to add test coverage for the CTE eager loading strategy with BelongsToMany associations, including: - Basic CTE strategy test with junction table - Multiple parent records test - Limit on parent query test - Conditions on parent query test - Junction table conditions test (using SpecialTags) Also updates testRequiresKeys() to verify CTE strategy does not require keys.

ADmad · 2026-03-08T06:13:26Z

Also makes me wonder why we have select as default instead of subquery. Which modern db does not support it?

dereuromark · 2026-03-08T06:16:40Z

@ADmad Updated the PR description with a full benchmark comparing all three strategies.

Regarding why select is the default instead of subquery:

Historical reasons:

SQLite limitations: Older SQLite versions had quirks with correlated subqueries
Simplicity: WHERE IN (1,2,3) is the most straightforward query to debug
Small dataset assumption: Most typical use cases fetch <100 parent records where the difference is negligible

Modern reality:

MySQL 5.6+ has semi-join optimizations for subqueries
MySQL 8.0+, PostgreSQL, modern SQLite all handle subqueries efficiently
CakePHP 5.x requires modern DB versions anyway

The benchmark shows subquery is actually slightly faster than both select and cte for large datasets. Changing the default to subquery could be considered for 6.x (or 5.next?), though it would be a behavioral change worth discussing separately.

ADmad · 2026-03-08T07:57:44Z

The benchmark shows subquery is actually slightly faster than both select and cte for large datasets.

In that case is adding the cte strategy necessary, if there's already a better alternative available? The issue poster has not clarified whether they tried using subquery strategy.

Changing the default to subquery could be considered for 6.x (or 5.next?), though it would be a behavioral change worth discussing separately.

I would vote to change the default to subquery in 5.next. The chances of it affecting someone use older database is minuscule.

dereuromark · 2026-03-08T08:10:34Z

Yes, still needed, see above
Specifically: Scenarios where max_allowed_packet limits are a concern

ADmad · 2026-03-08T08:41:08Z

Specifically: Scenarios where max_allowed_packet limits are a concern

Not sure how subquery strategy would be worse in comparison to the cte strategy in that regard.

max_allowed_packet limit is usually a concern for write operations not read operations.

max_allowed_packet limits the size of individual packets sent between the MySQL client and server. While it primarily affects WRITE operations like INSERT, UPDATE, or LOAD DATA, it can also impact SELECT queries when the result set is extremely large.

However, SELECT queries are not blocked by max_allowed_packet in the same way as INSERT. Instead, MySQL splits large result sets into multiple packets and sends them sequentially. This allows the query to succeed even if the total result size exceeds the packet limit, as long as no single packet exceeds the limit.

dereuromark · 2026-03-14T03:48:47Z

Fair.
Possible remaining CTE use cases maybe:

Better query plan visibility/debugging (CTEs are named, explicit) - More explicit/debuggable query structure
Specific database optimizers that handle CTEs better than correlated subqueries
Reusability when the same set is needed multiple times

Either way we should switch the default then to subquery in 5.next as follow-up.

justindenick · 2026-03-16T20:09:57Z

I took a look at this and there are a couple issues, first 1 is pretty trivial.

I think the proper DB limit to consider is max_parameter count
for postgres, this is Int16. No function can accept more that 65535 parameters.
the cte strategy is actually far worse than IN.
under the hood it's still has to produce the _joinData to match the associated data. I think this is how the tests are actually able to pass. Without the article_id being provided by the article_tags, there isn't a way to match any tags to an article.

in the test testCTEStrategyMultipleParents you can see this by dumping the results of the select loader.
ln(204) \Cake\Core\pr($fetchQuery->all()->toArray());
for CTE, every Tag is associated with every record from article_tags

WITH _cte_Articles AS (
SELECT (Articles.id) FROM articles Articles WHERE Articles.id <> :c0 GROUP BY Articles.id
) 
SELECT Tags.id AS Tags__id, Tags.name AS Tags__name, Tags.description AS Tags__description, Tags.created AS Tags__created FROM tags Tags INNER JOIN articles_tags ArticlesTags ON 1 = 1

_cte_Articles is never used

the cte query here, has no affect since it's not being joined or used for filtering.
There are 3 tags in the fixture and 4 article_tags. Each Tag is joined ON 1=1 to each of the article_tags where it should be something like RIGHT JOIN _cte_Articles ON (ArticleTags.article_id = _cte_Articles.id). As it is, 12 records are returned here. You can easily see this after adding a 4th Tag without and associated article_tag.

the cte itself, could be omitted in favor of EXISTS(...)

Where Tags are pulled by SELECT ArticlTags.,Tags. FROM tags Tags RIGHT JOIN article_tags ArticleTags ON(ArticleTags.tag_id = Tags.id) WHERE EXISTS(SELECT FROM articles JOIN article_tags ON(tag_id = Tags.id AND (the conditions used to find the Articles go here)

This would allow reuse the initial WHERE conditions instead of parsing the related ids and using them in IN.
The cost to that could be high if the initial conditions are complicated.

With BelongsToMany, we already have a good map of what belongs to what, using through. I haven't yet looked at a plain HasMany. (eg. comments might belong to articles with comments.articles_id as the foreign key)

Here we can still use EXISTS(...) SELECT * FROM comments Comments WHERE EXISTS(SELECT FROM articles WHERE id = Comments.article_id AND (the conditions used to find the Articles go here)

To be fair, I haven't yet looked at the subquery strategy to determine if my EXISTS theory wouldn't be better positioned there.

dereuromark · 2026-03-16T20:26:52Z

@justindenick Thanks for the detailed analysis, you're right.

Looking at the SQL output you captured, the CTE is defined but never actually joined to filter the results - the _cte_Articles appears in the WITH clause but isn't referenced in any JOIN. Combined with the ON 1 = 1 junction table join, this creates a cartesian product instead of proper filtering.

The tests passing is indeed coincidental - the ORM's result marshalling uses _joinData to match records back to parents after the query returns, which happens to produce correct-looking results with the current fixture data. Adding a tag without an associated article_tag would expose this.

Good catch on the max_allowed_packet point too - that limit primarily affects writes and large result sets, not query string length for IN clauses.

Given that:

The subquery strategy already handles the large dataset use case well (and benchmarks slightly faster)
The CTE implementation has fundamental issues for BelongsToMany
ADmad's earlier suggestion to change the default to subquery in 5.next addresses the original issue

I'll probably close this PR. A separate PR for changing the default strategy to subquery makes more sense.

@celsowm What do you think?

celsowm · 2026-03-16T23:17:43Z

I agree

dereuromark added this to the 5.4.0 milestone Mar 7, 2026

dereuromark added the enhancement label Mar 7, 2026

Skip CTE tests on databases that don't support CTEs

b873969

dereuromark force-pushed the feature/cte-eager-loading-strategy branch from cffd230 to b873969 Compare March 7, 2026 19:02

dereuromark mentioned this pull request Mar 7, 2026

ORM: Optimize eager loading 1:N associations using CTEs to replace WHERE IN (...) #19107

Closed

dereuromark changed the base branch from 5.x to 5.next March 7, 2026 19:46

ADmad requested review from Copilot and markstory March 8, 2026 05:50

Copilot started reviewing on behalf of ADmad March 8, 2026 05:50 View session

Copilot AI reviewed Mar 8, 2026

View reviewed changes

dereuromark marked this pull request as draft March 16, 2026 20:26

dereuromark mentioned this pull request Mar 16, 2026

Change default eager loading strategy to subquery for HasMany/BelongsToMany #19345

Merged

1 task

dereuromark closed this Mar 16, 2026

dereuromark deleted the feature/cte-eager-loading-strategy branch March 16, 2026 23:24

dereuromark mentioned this pull request Apr 11, 2026

Preserve SELECT aliases referenced by HAVING in eager loader subquery #19393

Merged

Conversation

dereuromark commented Mar 7, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Problem

Solution

Performance Comparison (MySQL 8.0)

Usage

When to Use

Compatibility

Uh oh!

ADmad commented Mar 7, 2026

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

Copilot AI Mar 8, 2026

Choose a reason for hiding this comment

Uh oh!

dereuromark Mar 8, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

ADmad commented Mar 8, 2026

Uh oh!

ADmad commented Mar 8, 2026

Uh oh!

dereuromark commented Mar 8, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

ADmad commented Mar 8, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

dereuromark commented Mar 8, 2026

Uh oh!

ADmad commented Mar 8, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

dereuromark commented Mar 14, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

justindenick commented Mar 16, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

dereuromark commented Mar 16, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

celsowm commented Mar 16, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

dereuromark commented Mar 7, 2026 •

edited

Loading

dereuromark commented Mar 8, 2026 •

edited

Loading

ADmad commented Mar 8, 2026 •

edited

Loading

ADmad commented Mar 8, 2026 •

edited

Loading

dereuromark commented Mar 14, 2026 •

edited

Loading

justindenick commented Mar 16, 2026 •

edited

Loading

dereuromark commented Mar 16, 2026 •

edited

Loading