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

Issue

Using CakePHP 4.1.6

I have two tables in my database called Categories and CategoryChanges. The Categories table is simple in that it contains a list with ID's and names of several categories, e.g.

id | name
------------
1  | Foo
------------
2  | Bar
------------
3  | Baz
------------

My other table, CategoryChanges, is a log of when categories (as in Categories) in the application have been changed, as well as some other data such as who changed them and when.

In CategoryChanges there are two columns category_id_from and category_id_to which correspond to Categories.id.

So as an example CategoryChanges may include the following:

id | category_id_from | category_id_to | created    | user | notes 
--------------------------------------------------------------------
80 | 2                | 3              | 2021-02-03 | John | abcdef
--------------------------------------------------------------------
81 | 3                | 3              | 2021-03-15 | Jack | ghi
--------------------------------------------------------------------
82 | 1                | 2              | 2021-03-18 | Dave | zzz
--------------------------------------------------------------------

What I'm trying to do is output a table on a webpage which shows the above information but instead of having category_id_from and category_id_to as the numerical ID, I want Categories.name to appear. Note that it is also possible for some rows that the "from" and "to" values are the same (as an example see CategoryChanges.id = 81).

So the output I want would be this:

id | category_from | category_to | created    | user | notes 
--------------------------------------------------------------------
80 | Bar           | Baz         | 2021-02-03 | John | abcdef
--------------------------------------------------------------------
81 | Baz           | Baz         | 2021-03-15 | Jack | ghi
--------------------------------------------------------------------
82 | Foo           | Bar         | 2021-03-18 | Dave | zzz
--------------------------------------------------------------------

After reading the Query Builder docs I can't see how to do this.

My assumption is that I need to read from the CategoryChanges table which is straightforward:

// in a Controller method
$CategoryChanges = $this->getTableLocator->get('CategoryChanges');
$query = $CategoryChanges->find();

If I execute $query->toArray() I would get the second table above, which contains the numerical ID's for category_id_from and category_id_to.

I assume that $query needs additional conditions - or possibly virtual fields - for "category from" and "category to". I can't see how to join these two columns to Categories such that I can get Categories.name as per table 3.

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

Answered By - ndm