php - CakePHP 4 - for 2 foreign keys in Table A, get corresponding data from Table B

one text

Solution:

You could for example simply add two belongsTo associations for those fields to your CategoryChanges table, and then comfortably use innerJoinWith() with your query, like:

$this
    ->belongsTo('CategoryFrom')
    ->setClassName('Categories')
    ->setForeignKey('category_id_from');
    
$this
    ->belongsTo('CategoryTo')
    ->setClassName('Categories')
    ->setForeignKey('category_id_to');

$query = $CategoryChanges
    ->find()
    ->select([
        'id',
        'category_from' => 'CategoryFrom.name',
        'category_to' => 'CategoryTo.name',
        'created',
        'user',
        'notes',
    ])
    ->innerJoinWith('CategoryFrom')
    ->innerJoinWith('CategoryTo');

Source