CakePHP 4 query: where clause with nested table not working?

Question

I'm working for my first time with CakePHP and I'm trying to do a (really simple) query but I'm not managing to get it done. I have set up all the tables of my database, and that is working well. Now, I have this query:

$CompaniesTable = $this->fetchTable('Companies'); 
$open_rounds = $CompaniesTable->find()->contain('Fundings' => ['FundingsFundingrounds']])->where(['Companies.fif_status_id IN' => [4, 5, 7], 'FundingsFundingrounds.round' => true])->limit(10);

In my mind this should work, but when I execute I get:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'FundingsFundingrounds.round' in 'where clause'

If I delete 'FundingsFundingrounds.round' => true the query works well. I have created another query to see if 'FundingsFundingrounds.round' really exists and yes, it does!

Probably I'm missing a step to use the where clause in a case like this, I hope someone can help!

Answer 1

Check if Fundings and FundingsFundingrounds are selected in separate SQL queries. If that is true (which I assume) than you have effectively 3 queries which are executed. I guess you have the relationship: Companies hasMany Fundings hasMany FundingsFundingrounds and FundingsFundingrounds belongsTo Fundings belongsTo Companies belongsTo relationship in 'contain' -> left join (default) hasMany relationship in 'contain' -> separate query (default)

try:

$open_rounds = $this->fetchTable('FundingsFundingrounds')
->find()
->contain('Fundings' => ['Companies']])
->where([
  'Companies.fif_status_id IN' => [4, 5, 7],
  'FundingsFundingrounds.round' => true,
])
->limit(10);

You have always to elegant option of a sub query which could also meet your needs.