[FIXED] How do I correctly execute a LEFT join in CakePHP 3?

Issue

I have been attempting to use CakePHP 3's query builder format in order to learn how to better use it. However, at the moment I cannot seem to get a left join working.

There is information available in both tables that I would like to collect in the result:

    $query = $this->find()->where(['userID' => $UID])->hydrate(false)->join([
        'table' => 'authgroups',
        'alias' => 'a',
        'type' => 'LEFT',
        'conditions' => 'a.id = userGroup'
    ]);

    return $query->toArray();

If I'm reading the CookBook correctly, this should work - however, it is being used in a model which differs from the example, and the result only returns from that table, seemingly ignoring the join. Do I need to execute this in the controller instead?

Solution

This works in model:

What I guess is your join query might working well.The important thing in cakephp join is that:

If you are joining table 'A' with table 'B' from table 'A' ,
By default it will select all fields from table A and no fields from table B.

You need to select some fields from another table as well:

$query = $this->find()->where(['userID' => $UID])->hydrate(false)->join([
    'table' => 'authgroups',
    'alias' => 'a',
    'type' => 'LEFT',
    'conditions' => 'a.id = userGroup'
 ])->autoFields(true) // selecting all fields from current table
   ->select(["a.field1","a.field2"]); // selecting some fields from table authgroups

return $query->toArray();
}

And I don't know if these things are provided well in cakephp documentation.

Answered By - Manohar Khadka