I have two tables i.e. Comments and Ratings. The Comments table has Primary key ['comment_id','source_id']
and the Ratings table has the primary key ['comment_id','source_id','topic_id']
.
The model relations are defined as below :
/* Model comments Table*/
$this->table('comments');
$this->primaryKey(['comment_id','source_id']);
$this->hasMany('Ratings',[
'foreignKey'=>['comment_id','source_id']
]);
/* Model Ratings Table*/
$this->table('ratings');
$this->primaryKey(['comment_id','source_id','topic_id']); // Notice the primary key has 3 columns
$this->belongsTo('Comments',[
'foreignKey'=>['comment_id','source_id']
]);
I want to insert multiple Comments and every comment will have multiple ratings related to it, the different rating under each comment belong to different topic of rating. I am trying to save a comment and its related ratings using the saveMany syntax but, cakePHP saves the comment correctly but both of the ratings are not saved, it saves the last rating only.
/* Inside controller's action */
$comment['comment_id'] = '12';
$comment['source_id'] = 4;
$comment['travel_date'] = '';
$comment['ratings'] = [
[ //This doesn't gets saved
'topic_id' =>5,
'rating' =>4,
'created' =>'2017-02-09 13:06:04'
],
[ //This gets saved
'topic_id' =>6,
'rating' =>5,
'created' =>'2017-02-09 13:06:04'
]];
$multipleRows[0] = $comment;
$newRows = $this->Comments->newEntities($multipleRows,['associated' => ['Ratings']]);
$this->Comments->saveMany($newRows);
I did a SQL log and found this :
Debug: duration=0 rows=0 BEGIN
Debug: duration=1 rows=0 SELECT 1 AS
existing
FROM comments as comments WHERE (comments.comment_id = '12' AND comments.source_id = 4) LIMIT 1Debug: duration=1 rows=1 INSERT INTO comments (comment_id, source_id, travel_date) VALUES ('12', 4, '')
Debug: duration=1 rows=0 SELECT 1 AS
existing
FROM ratings as ratings WHERE (ratings.comment_id = '12' AND ratings.source_id = 4) LIMIT 1Debug: duration=1 rows=1 INSERT INTO ratings (comment_id, source_id, topic_id, rating, created) VALUES ('12', 4, 5, 4, '2017-02-09 13:06:04')
Debug: duration=1 rows=1 SELECT 1 AS
existing
FROM ratings as ratings WHERE (ratings.comment_id = '12' AND ratings.source_id = 4) LIMIT 1Debug: duration=1 rows=1 UPDATE ratings SET topic_id = 6 , rating = 5 , created = '2017-02-09 13:06:04' WHERE (comment_id = '12' AND source_id = 4)
Debug: duration=0 rows=0 COMMIT
If you see the above log CakePHP checks for duplicacy and if the record already exists then it updates the record instead of adding a new row. But, the problem here is that for this checking CakePHP uses the foreign key only not the Primary Key of the target table. In my case the Primary Key contains 3 columns and first 2 columns of them make the Foreign key. How can I fix this issue and insert multipe ratings in such scenario?
I have already searched on SO and googled it also. The solutions found do satisfy my case. My case is different as the ratings table has 3 columns a composite primary key and out of those 3 the first 2 are the foreign key for the comments table.