CakePHP double model association

Question

My accounts model: id, name

My entries model: id, account_id1, account_id2, amount

So my entries has 2 foreign keys to the account model.

I defined the model associations like this.

$this->belongsTo(
  'Acc1',
  [
    'className' => 'Accounts',
    'foreignKey' => 'account_id1',
    'joinType' => 'INNER',
  ]
);

$this->belongsTo(
  'Acc2',
  [
    'className' => 'Accounts',
    'foreignKey' => 'account_id2',
    'joinType' => 'INNER',
  ]
);

Now I would need to find entries where the related account's name starts with "ab".

In my entries model file:

$this->find()
  ->matching(
    'Acc1',
    function ($q) {
      return $q->where(['LEFT(Acc1.name, 2) =' => 'ab']);
    }
  );

This will give me all entries where the related account (by account_id1) name starts with "ab"

$this->find()
  ->matching(
    'Acc1',
    function ($q) {
      return $q->where(['LEFT(Acc1.name, 2) =' => 'ab']);
    }
  )
  ->matching(
    'Acc2',
    function ($q) {
      return $q->where(['LEFT(Acc2.name, 2) =' => 'ab']);
    }
  );

The result of this where both Account names starts with "ab".

How can I get entries where Acc1.name or Acc2.name starts with "ab"?

Answer 1

Try building the query without ->matching. You could use joins, or contain Acc1 and Acc2. Then cake has some OR methods to help you out.

->where(['LEFT(Acc1.name, 2) =' => 'ab'])
->orWhere(['LEFT(Acc2.name, 2) =' => 'ab']);

http://book.cakephp.org/3.0/en/orm/query-builder.html#advanced-conditions