[FIXED] Left Join CakePHP3

Issue

I'm trying to do a LEFT JOIN in CakePHP3. But all I get is a "is not associated"-Error.

I've two tables BORROWERS and IDENTITIES. In SQL this is what I want:

SELECT
 identities.id
FROM
 identities
LEFT JOIN borrowers ON borrowers.id = identities.id
WHERE
 borrowers.id IS NULL;

I guess this is what I need:

$var = $identities->find()->select(['identities.id'])->leftJoinWith('Borrowers',
        function ($q) {
               return $q->where(['borrowers.id' => 'identities.id']);
        });

But I'm getting "Identities is not associated with Borrowers".

I also added this to my Identities Table:

$this->belongsTo('Borrowers', [
  'foreignKey' => 'id'
]);

What else do I need? Thanx!

Solution

Yup. It was an instance of \Cake\ORM\Table, due to my not well chosen table name (Identity/Identities). I guess it's always better not to choose those obstacles, for now I renamed it to Label/Labels.

This query now works perfectly:

$var = $identities 
  ->find('list')
  ->select(['labels.id'])
  ->from('labels')
  ->leftJoinWith('Borrowers')
  ->where(function ($q) {
      return $q->isNull('borrowers.id'); 
});

Answered By - DeVolt