CakePHP 3 Find All Contain NULL data, displays nothing?

Question

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.

Answer 1

In cakePhp3.x in default when you bake a Model the join table is set to INNER JOIN. You would need to modify the model associations. You need to set the association to LEFT JOIN.

In your case if you look at 'UserinfosTable' in src/model/UserinfosTable make sure you are 'LEFT JOIN' ing the 'User' table or you can completely remove the 'joinType' because in default cakephp sets the Contain to 'LEFT JOIN'

class UserinfosTable extends Table
{

   public function initialize(array $config)
    {

      $this->belongsTo('Userinfos', [
            'foreignKey' => 'office_id',
            'joinType' => 'LEFT'
      ]);
   }
}

Answer 2

Try adding the IS NULL clause as a value instead of a key/value pair :

ex: $UsersTable->find('all')->where(['Userinfos.office_id IS NULL'])