We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Bug when saving elements with unique key constraint on many-to-many relationships.

I'm experiencing an odd behaviour when working with models with many-to-many relations.

Here's a mock of my Models:

class Job{
   public $id;
   public $name;

   public function initialize(){
      $this->hasMany('id', 'JobBenefit', 'job_id', ['alias'=>'jobBenefits']);
      $this->hasManyToMany('id', 'JobBenefit', 'job_id', 'benefit_id', 'Benefit', 'id', ['alias'=>'benefits']);
   }
}

class Benefit{
   public $id;

   public function initialize(){
      $this->hasMany('id', 'JobBenefit', 'benefit_id', ['alias'=>'jobBenefits']);
   }
}

class JobBenefit{
   public $job_id;
   public $benefit_id;

   public function initialize(){
      $this->belongsTo('job_id', 'Job', 'id');
      $this->belongsTo('benefit_id', 'Benefit', 'id');
   }
}

And here's whats happening:

//This works:
$job = Job::findFirstById(1);
$job->name = 'Hi there';
$job->update(); //OK
$job->name = 'Something new';
//[... some more changes ...]
$job->update(); //OK

//This DOESN'T
$benefits = Benefits::find(['id > 10']);

$job = Job::findFirstById(1);
$job->name = 'Hi there';
$job->jobBenefits->delete(); //OK, removes the relation between this job and all hits benefits
$job->benefits = $benefits;
$job->update(); //OK, the new relations [JobBenefit] are saved on the database
$job->name = 'Something new';
//[... some more changes ...]
$job->update(); //ERROR. PDOException: duplicate entry for key job_id_benefit_id

I've checked the dirty state of the job, the benefits and the relations (jobbenefit), and all of them are 0

So, my question is: Why is it trying to save the relations again?

1


32.2k

Hi @Lucas this line is ok? $benefits = Benefits::find(['id > 10']); class name is Benefit

Always is good check the result of update/create/save. Can you add this

// replace line $job->update() with this
if($job->update() === false) {
    var_dump($job->getMessages());
}

Id's in your tables are auto_increment?

Tell us how you were



3.0k
in reply to Emilio Degiovanni
edited Dec '17

I mistyped my sample code here, it should read Benefit:: as you pointed. Yes, my ids are set as auto-increment.

Let me break down my code further:

$benefits = Benefit::find(['id > 10']); // [ Benefit(11), Benefit(12) ]

$job = Job::findFirstById(1);

Job Table

id name
1 DevOps

Benefits Table

id name
1 A
11 Foo
12 Bar

JobBenefits Table

opening_id benefit_id
1 1

Then we run this:

$job->name = 'Hi there';
$job->jobBenefits->delete(); //Breakpoint after this: JobBenefits Table is empty, as expected
$job->benefits = $benefits;
$job->update();
var_dump($job->getMessages()); // => empty()

And my database looks like this (as expected)

Job Table

id name
1 Hi there

Benefits Table

id name
1 A
11 Foo
12 Bar

JobBenefits Table

opening_id benefit_id
1 11
1 12

Then we run the code below:

$job->name = 'Something new';
//[... some more changes ...]
$job->update(); // !! ERROR !! PDOException: duplicate entry for key job_id_benefit_id

And I get a PDOException as soon as update() is called.

If I remove the UniqueKey constraint from the table and execute the code, here's what I get:

JobBenefits Table

opening_id benefit_id
1 11
1 12
1 11
1 12

It duplicates the relationships (JobBenefits). And my big question is: Why?

If the DirtyState of all models (Job, Benefits, and JobBenefits) is 0 (Persistent), why is the ORM trying to save the relationships again?

Hi @Lucas this line is ok? $benefits = Benefits::find(['id > 10']); class name is Benefit

Always is good check the result of update/create/save. Can you add this

// replace line $job->update() with this
if($job->update() === false) {
  var_dump($job->getMessages());
}

Id's in your tables are auto_increment?

Tell us how you were



3.0k

We can boil down the sample code to this

//Good Results:
$job = Job::findFirstById(1);
$job->name = 'Hi there';
$job->update(); //OK
$job->update(); //OK

//Bad Results
$job = Job::findFirstById(1);
$job->jobBenefits->delete();
$job->benefits = $benefits;
$job->update(); //OK
$job->update(); //PDOException