CakePHP 3 is using primary key (setPrimaryKey) to do a JOIN even though I've specified a foreign key

Question

CakePHP 3.7

I have 2 model Table classes as follows:

1. /src/Model/Table/SubstancesTable.php
2. /src/Model/Table/TblOrganisationSubstancesTable.php

The schema for each table in MySQL is as follows:

1.

mysql> describe substances;
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| id          | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| app_id      | varchar(8)            | NO   | UNI | NULL    |                |
| name        | varchar(1500)         | NO   |     | NULL    |                |
| date        | date                  | NO   |     | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+

2.

mysql> describe tbl_organisation_substances;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| o_sub_id    | int(255)     | NO   | PRI | NULL    | auto_increment |
| o_id        | int(255)     | NO   | MUL | NULL    |                |
| app_id      | varchar(15)  | YES  |     | NULL    |                |
| os_name     | varchar(255) | YES  |     | NULL    |                |
| ec          | varchar(35)  | YES  |     | NULL    |                |
| cas         | varchar(255) | YES  |     | NULL    |                |
| upload_id   | int(100)     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

I've written a custom finder which needs to perform a JOIN between these 2 tables. The custom finder looks like this and is in SubstancesTable.php:

public function findDistinctSubstancesByOrganisation(Query $query, array $options)
{
    $o_id = $options['o_id'];

    $query = $this->find()->select('id')->contain('TblOrganisationSubstances')->where(['TblOrganisationSubstances.o_id' => $o_id]);

    return $query;
}

Each table has an app_id column and this is the foreign key that links the 2 tables.

Initially I was getting an error:

The TblOrganisationSubstances association is not defined on Substances.

Which made sense as no definitions had been put in place.

So in SubstancesTable.php I've defined this:

$this->setPrimaryKey('id');

// ...

$this->belongsTo('TblOrganisationSubstances', [
    'foreignKey' => 'app_id',
    'joinType' => 'INNER'
]);

But this produces the following SQL statement:

SELECT Substances.id AS `Substances__id` FROM substances Substances INNER JOIN tbl_organisation_substances TblOrganisationSubstances ON TblOrganisationSubstances.o_sub_id = (Substances.app_id) WHERE TblOrganisationSubstances.o_id = :c0

This isn't going to work because TblOrganisationSubstances.o_sub_id = (Substances.app_id) is wrong. It needs to JOIN based on app_id, in other words it should be:

TblOrganisationSubstances.app_id = (Substances.app_id)

I also tried changing the belongsTo to a hasMany association (not even sure which one is right here!):

$this->hasMany('TblOrganisationSubstances', [
    'foreignKey' => 'app_id',
]);

But the joining doesn't even seem to occur. The SQL produced is:

SELECT Substances.id AS `Substances__id` FROM substances Substances WHERE TblOrganisationSubstances.o_id = :c0

I also tried putting a belongsTo in TblOrganisationSubstances.php to try and define the relationship from both sides:

$this->belongsTo('Substances', [
    'foreignKey' => 'app_id',
    'joinType' => 'INNER'
]);

Again, this doesn't work. It produces the SQL without the join.

Please can someone help in terms of advising which is the correct type of association (hasMany vs belongsTo) and how to perform the join based on app_id, which is the foreign key that links the 2 tables.

Answer 1

In a BelongsTo association, the source table holds the foreign key, hence with your configuration it will use app_id on Substances, and compare against the primary key of TblOrganisationSubstances.

In order to get the query that you're looking for, you also have to define the bindingKey, ie the foreign key of the target table, so that it doesn't use its primary key:

$this->belongsTo('TblOrganisationSubstances', [
    'foreignKey' => 'app_id', // Substances.app_id
    'bindingKey' => 'app_id', // TblOrganisationSubstances.app_id
    'joinType' => 'INNER'
]);

Whether it's HasMany or BelongsTo is dictated by your data, and what and how you want to retrieve it. Generally if one Substances has 0 or 1 TblOrganisationSubstances (1:1), then it's BelongsTo or HasOne. If one Substances can have more than 1 TblOrganisationSubstances (1:n), then it's HasMany.

And if you want to filter by a HasMany association, then you need to use either matching or a join (matching(), innerJoinWith(), leftJoinWith()), as HasMany associated records will be retrieved in a separate query when using contain().

See also

• Cookbook > Database Access & ORM > Associations - Linking Tables Together
• Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data

Answer 2

SELECT * FROM sample LEFT JOIN user ON sample.rac_id=user.userrac_id UNION ALL SELECT * FROM sample RIGHT JOIN user ON sample.rac_id=user.userrac_id

Answer 3

    public $hasAndBelongsToMany = array(
        'Ingredient' =>
            array(
                'className' => 'Ingredient',
                'joinTable' => 'ingredients_recipes',
                'foreignKey' => 'recipe_id',
                'associationForeignKey' => 'ingredient_id',
                'unique' => true,
                'conditions' => '',
                'fields' => '',
                'order' => '',
                'limit' => '',
                'offset' => '',
                'finderQuery' => '',
                'with' => ''
            )
    );
}````