UPDATE
This is the join from the SQL after all the selects,
FROM users Users
LEFT JOIN userinfos Userinfos ON Userinfos.id = (Users.userinfo_id)
INNER JOIN offices Offices ON Offices.id = (Userinfos.office_id)
ok, I have a database (MySQL) setup with CakePHP 3 the users table has extended information held within another table.
This other table is also extended with an office / address information however this is set to NULL by default, so I want to include it in a 'contain' call but dose not return the user data when its empty?
So this is what I use currently,
$UsersTable->find('all')
->contain(['Userinfos','Userinfos.Offices'])
->toArray();
But, Offices (office_id field in table Usersinfos) is not always set, however I still want it to return all the users even if they don't have an office set.
So I have tried,
$UsersTable->find('all')
->contain([
'Userinfos',
'Userinfos.Offices'
])
->where(['Userinfos.office_id IS' => NULL])
->toArray();
Also,
$UsersTable->find('all')
->contain([
'Userinfos',
'Userinfos.Offices' =>
function($q) {
return $q->where(['Userinfos.office_id IS' => NULL]);
}
])
->toArray();
The $UsersTable
var is set to
$UsersTable = TableRegistry::get('Users');
If I remove the Userinfos.Offices
from the contain condition, then it returns all my current users. However, how can I call in this extra data/information so I have access to the Office information, e.g location name if that is set?
*I may not have explain myself clearly, please let me know if there is anything I can explain better, thanks.
INNER
join... as @drmonkeyninja said, check your associations, by default this would be aLEFT
join. – ndm