Cannot create multiple many to many relations in pivot table

Question

I'm trying to create an App in Laravel 5 which tracks medical exams for patients so i can create a patient history, i'm using Eloquent as my model abstraction recommendation.

I have created three tables and a pivot one for this purpose using laravel migration, those are shown in the diagram below:

Users can be either Doctors or Administrators, they enter the Patient's score results for an specific Exam they make, for instance I have a pivot table which include three foreign keys to relate the three entities:

• exam_id
• patient_id
• user_id

In Eloquent I made these Many to many Models following Laravel Documentation Many To Many to support the proposed business logic:

<?php
    // Patient.php
    class Patient extends Model
    {
        public function exams() {

            return $this->belongsToMany('App\Exam', 'exam_patient');
        }
    }

    // Exam.php
    class Exam extends Model
    {
        public function patients()
        {

            return $this->belongsToMany('App\Patient', 'exam_patient');
        }

        public function users()
        {

            return $this->belongstoMany('App\User', 'exam_patient');
        }
    }

    // User.php
    class User extends Model
    {
        public function exams() {

            return $this->belongsToMany('App\Exam', 'exam_patient');
        }
    }

In tinker I try to simulate a common use case before doing major changes: A Doctor makes an exam to a Patient:

 >>> $user= App\User::first()
=> App\User {#671
     id: "1",
     name: "Victor",
     email: "",
     created_at: "2015-10-10 18:33:54",
     updated_at: "2015-10-10 18:33:54",
   }

>>> $patient= App\Patient::first()
=> App\Patient {#669
     id: "1",
     username: "",
     name: "Tony",
     lastname: "",
     birthday: "0000-00-00",
     created_at: "2015-10-10 18:32:56",
     updated_at: "2015-10-10 18:32:56",
   }

>>> $exam= App\Exam::first()
=> App\Exam {#680
     id: "1",
     name: "das28",
     title: "Das28",
     created_at: "2015-10-10 18:31:31",
     updated_at: "2015-10-10 18:31:31",
   }

>>> $user->save()
=> true
>>> $patient->save()
=> true
>>> $exam->save()
=> true

The problem is when I try to link (or attach) at least two models I get the following error:

   >>> $patient->exams()->attach(1) 
Illuminate\Database\QueryException with message
    'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a
    child row: a foreign key constraint fails (`cliniapp`.`exam_patient`, CONSTRAINT
    `exam_patient_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE) (SQL: insert into `exam_patient` (`exam_id`, `patient_id`)
    values (1, 1))'

How can I link the three models if I can't even attach two of them? Is there any way in tinker to mass attach them so i can relate them in my pivot table (exam_patient) and MySql doesn't show that error OR if I'm mistaken in my approach? Any help it's greatly appreciated.

Answer 1

When there are extra fields on your Many to Many relationships, you have to "register" them when you declare the relationship, as seen in the Retrieving Intermediate Table Columns section of the above link.

So you should always declare your "extra" pivot tables, which are different depending on where you are defining the relationship:

class Patient extends Model
{
    public function exams()
    {
        return $this->belongsToMany('App\Exam', 'exam_patient')->withPivot('user_id');
    }
}

class Exam extends Model
{
    public function patients()
    {
        return $this->belongsToMany('App\Patient', 'exam_patient')->withPivot('user_id');
    }

    public function users()
    {
        return $this->belongstoMany('App\User', 'exam_patient')->withPivot('patient_id');
    }
}

class User extends Model
{
    public function exams()
    {
        return $this->belongsToMany('App\Exam', 'exam_patient')->withPivot('patient_id');
    }
}

In the Attaching / Detaching section, you can see that you can set those extra fields like so:

$patient->exams()->attach($exam->id, ['user_id' => $user->id]);

Your problem was that the user_id Foreign Key constraint on your exam_patient table was failing when you where creating the patient/exam relationship without passing data for the user_id column, so the above will work.

If you don't want to always pass the user_id like that for whatever reason, you could also make that user_id column nullable on the database.

Of course, it can be argued that using two pivot tables (exam_patient and exam_user), or using many-to-many polymorphic relationships would be a better approach here.

---

As we can see from the Laravel docs on polymorphic relations, to implement them here we'd drop the exam_patient table for an examable table, which would have 3 columns: exam_id, examable_id and examable_type.

The Models would look like:

class Patient extends Model
{
    public function exams()
    {
        return $this->morphToMany('App\Exam', 'examable');
    }
}

class Exam extends Model
{
    public function patients()
    {
        return $this->morphedByMany('App\Patient', 'examable');
    }

    public function users()
    {
        return $this->morphedByMany('App\User', 'examable');
    }
}

class User extends Model
{
    public function exams()
    {
        return $this->morphToMany('App\Exam', 'examable');
    }
}

To update the examable table fully, you'd do something like:

$exam->patients()->save($patient);
$exam->users()->save($user);

So it's two queries instead of one, you lose the foreign key relationships and "examables" is an awkward name.

The argument for would be that the relationships are a bit more declarative and it's easier to handle a single exam for a single patient belonging to multiple users/doctors.