1

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 1

Debug: 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 1

Debug: 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 1

Debug: 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.

ndm
  • 59,784
  • 9
  • 71
  • 110
Ravendra
  • 21
  • 2
  • 1
    Works fine for me **http://pastebin.com/i43xQCGr**. I guess you'll have to do a little more debugging, and show some more context, as it doesn't seem possible to reproduce the problem with the shown code. – ndm Feb 10 '17 at 15:45
  • Thanks for you response. yes it works fine, there is some other issue which I found. – Ravendra Feb 11 '17 at 11:42

0 Answers0