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.

I did but I did not see anything wrong with it, but I look at it in a bit more detail — user4458505
What I don't understand is that this works, when the 'office_id' is set to something? that is what I can not figure out — user4458505
What type of join is Cake using for the associates? Perhaps this needs changing for your requirement. — drmonkeyninja
That was to be expected, an INNER join... as @drmonkeyninja said, check your associations, by default this would be a LEFT join. — ndm

Fury Fury · Accepted Answer · 2015-11-12T10:43:48

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'
      ]);
   }
}

CakePHP 3 Find All Contain NULL data, displays nothing?

UPDATE

3 Answers