CODEWITHSUNDEEP
phpmysqlsqllaravel
Sohel0415
Sohel0415

Reputation: 9853

Foreign key relationships with the same table for two different foreign key columns

Lets say I have a user and appointment table.

users - id, name, email, type(doctor/patient)

appointment- id, doctor_user_id, patient_user_id

My user table data-

enter image description here

And my appointment table data-

enter image description here

I could make two belongs to relationships from appointment with user table.

As you can see my appointment table, I want to store only user that type is doctor to doctor_user_id and patient to patient_user_id. But in this case i can add any user id to doctor_user_id field either it is doctor or patient but i want to add only user id as doctor_user_id only if its type is doctor.

I know how to achieve this with two different table but I was wondering is there any way to achieve this with single user table, Thanks.

Upvotes: 3

Views: 179

Answers (2)

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

You can use two belongsToMany() relationships and use the table as I pivot:

public function doctors()
{
    return $this->belongsToMany(User::class, 'appointment', 'patient_user_id', 'doctor_user_id');
}

public function patients()
{
    return $this->belongsToMany(User::class, 'appointment', 'doctor_user_id', 'patient_user_id');
}

If you'll use the appointment table a lot, you can also add two hasMany() relationships to the Appointment model and two belongsTo() relationships to the User model. So, you could use belongsToMany(), hasMany() and belongsTo() relationships simultaneously for in this case.

Upvotes: 3

Mittal Patel
Mittal Patel

Reputation: 2762

Yes, you can achieve this by creating one user table only.

Create 2 foreign key for the "doctor_user_id" and "patient_user_id" of appointment table which references to User table.

Upvotes: 3

Related Questions