In CakePHP 3.9.1 I have a table equivalent to CelestialBodiesTable
, which contains stars and planets. Both Planets and Stars are Celestial Bodies, and every Planet belongs to one or more Stars (e.g. binary systems). Similarly, in this universe every Star has one or more Planets. The OrbitsTable
links planets to the stars they orbit, but do so with an identifier specific to stars. The following associations uses the orbits to list all planets that orbit a given star.
$this->belongsToMany('Planets', [
'bindingKey' => 'star_id',
'foreignKey' => 'star_id',
'targetForeignKey' => 'celestial_body_id',
'targetBindingKey ' => 'id',
'joinTable' => 'orbits',
'className' => 'CelestialBodies'
]);
results in
[
{
"id": 432112,
"star_id": "Q000935",
"stars": [],
"planets": [
{
"id": 226637,
"_joinData": {
"id": 11255,
"star_id": "Q000935",
"celestial_body_id": 226637,
}
},
// 70 more
]
}
]
However, when I want to mirror this — listing all the stars a planet is orbitting — things break.
$this->belongsToMany('Stars', [
'bindingKey' => 'id',
'foreignKey' => 'celestial_body_id',
'targetForeignKey' => 'star_id',
'targetBindingKey' => 'star_id',
'joinTable' => 'orbits',
'className' => 'CelestialBodies'
]);
results in
[
{
"id": 226637,
"star_id": "",
"stars": [],
"planets": []
}
]
The association seems to generate, based on logging SelectLoader#buildEagerLoader()
:
SELECT
# ...
FROM
celestial_bodies Stars
INNER JOIN
orbits Orbits
ON Stars.id = # and this should be Stars.star_id
(
Orbits.star_id
)
WHERE
Orbits.celestial_body_id in
(
:c0
)
celestial_bodies
:
id | star_id | etc. |
---|---|---|
432112 | “Q000935” | … |
226637 | “” | … |
orbits
:
id | star_id | celestial_body_id |
---|---|---|
11255 | “Q000935” | 226637 |
This data is adapted and simplified; I would have set up some MCVE if I could think of a way how. I get that this is not the most optimal database structure, but it seems like this should work still?