-
Notifications
You must be signed in to change notification settings - Fork 3.4k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
contain()
ing an association with strategy=join
with joins in the query builder affects outer query
#17184
Comments
This is likely due to differences in how the SQL is generated. Containing an association does behave differently because of how the semantics of the ORM have worked for a long time. The SQL generated for the 'wrong' results is roughly SELECT ...
FROM users
LEFT JOIN subscriptions
INNER JOIN products Which ends up implicitly making both joins inner. I think this might be the source of your wrongness. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Description
Hi,
I feel like this may have been already talked about / be a known query builder "gotcha", but I can't seem to find issues or mentions about it in the docs. Using
matching/innerJoinWith
inside acontain
is limiting the results of the base query, if the contained association uses ajoin
strategy (the default forhasOne
/belongsTo
). The provided examples usehasOne
.What I did
A hasOne B
B belongsTo C
$this->A->find()->contain(['B' => fn($q) => $q->innerJoinWith('C')])
What I got
Only items from A that have one associated B
What I expected
Every row from A, regardless of whether or not they have associated Bs
Workarounds
Changing strategy to
select
, lazy loading after query withloadInto
, rewriting into a subquery, etc.None of the workarounds seem ideal to me as they require you to be actively aware of the implementation when containing the association (in the examples the
innerJoinWith
is evident, but in real life scenarios you're likely to use finders).Solutions
select
.Test/reproduce
git-apply
the following patch https://gist.github.com/andres-ml/988d4202d607a63556a842eb0ef79869 into a freshcakephp/app
, then run./vendor/bin/phpunit tests/TestCase/Model/
CakePHP Version
4.4.15
PHP Version
No response
The text was updated successfully, but these errors were encountered: