CODEWITHSUNDEEP
phpmysqlentity-frameworkcakephpcakephp-3.0
Yetispapa
Yetispapa

Reputation: 2306

CakePhp 3 - link and save associations with a table which holds two foreign keys of same model

i have the following two tables:

recipes

table-a

similiar_recipes

table-b

As you can see similar_recipes has two foreign keys which both point to recipes. Now I want two things. First of the linking. I read on stackoverflow some similar stuff and come up with the following configuration:

RecipesTable.php

$this->hasMany('Recipes', [
    'foreignKey' => 'recipe_id',
    'className' => 'SimilarRecipes'
]);

$this->hasMany('SimilarRecipes', [
    'foreignKey' => 'similar_recipe_id',
    'className' => 'SimilarRecipes'
]);

SimilarRecipesTable.php

$this->belongsTo('Recipes', [
    'foreignKey' => 'recipe_id',
    'joinType' => 'INNER',
    'className' => 'Recipes'
]);
$this->belongsTo('SimilarRecipes', [
    'foreignKey' => 'similar_recipe_id',
    'joinType' => 'INNER',
    'className' => 'Recipes'
]);

The configuration should be correct. Now the other question is the correct associated saving or lets ask better is it possible to do the following:

Recipes Data

table-a-entries

Now in Cake I want to add a recipe and the associated recipes which are delivered in the request->data as an id-array

$newRecipe = $this->Recipes->newEntity();
$newRecipe = $this->Recipes->patchEntity($newRecipe, $this->request->data);

$this->Recipes->save($newRecipe, ['associated' => ['SimilarRecipes']])

This should be the result:

table-b-entities

In conclusion I saved a new recipe which gets the id 3. In the request->data I select the similar recipes 1 and 2.

Could someone give me an advice. Is my configuration wrong. Or what do I have to pass to the save method? By the way I don't get any errors.

Upvotes: 1

Views: 1438

Answers (1)

ndm
ndm

Reputation: 60473

Use belongsToMany associations instead

I'd say your association approach is wrong (or at least makes things unnecessarily complicated), I'd suggest to use belongsToMany associations instead, as what you seem to create there is a self-joining many-to-many relation.

Name the table recipes_similar_recipes, that's the convention CakePHP uses, it helps to avoid association name conflicts/confusion, and allows relying on magic configuration. Your tables associations/configuration should then look something like:

RecipesTable.php

$this->belongsToMany('SimilarRecipes', [
    'foreignKey' => 'recipe_id',
    'targetForeignKey' => 'similar_recipe_id',
    'joinTable' => 'recipes_similar_recipes'
]);

SimilarRecipesTable.php

$this->table('recipes');
$this->primaryKey('id');

$this->belongsToMany('Recipes', [
    'foreignKey' => 'similar_recipe_id',
    'targetForeignKey' => 'recipe_id',
    'joinTable' => 'recipes_similar_recipes'
]);

With such a setup you could then use the array of IDs variant, eg use the _ids key to define the existing (similar) recipies that should be associated with your new recipe, ie the request data should look something like:

[
    // there is no title column in your example,
    // this should just clarify the data structure
    'title' => 'New recipe that will get the ID 3',
    'similar_recipes' => [
        '_ids' => [
            1, 2
        ]
    ]
]

which should populate the recipes_similar_recipes table like:

+----+-----------+-------------------+
| id | recipe_id | similar_recipe_id |
+----+-----------+-------------------+
| 1  | 3         | 1                 |
| 2  | 3         | 2                 |
+----+-----------+-------------------+

You should then also consider making recipe_id and similar_recipe_id a compound primary key, or at least create a unique index with them.

See also

Upvotes: 2

Related Questions