Laravel Eloquent multiple relations of many-to-many between models

Question

I have a few models that should relate, in a many-to-many relationship (I presume), for the following:

My Clinic has many practice areas (ex. odontology, psychiatry, general medicine, internal medicine, etc. and operates in several global continents/world regions, in many countries of each region and in many cities of each country. Not all clinic branches have the same Practice Areas.

Where my difficulty comes is because of the following:

The Clinic is in many World Regions/Continents;
It is also, in many Countries of each Region
It is also in may Cities of each Country;
Each Clinic branch will have it's own Practice Areas;

My first approach was too clunky and would't work for sure, as I tried to build a pivot table with more that two ids and the other was to do it in one table (which I presume is absolutely incorrect).

What would be the best approach to set the database for all these relationships to work together, with the following models I have:

Models\Clinic::class;
Models\PracticeArea::class;
Models\WorldRegion::class;
Models\Country::class;
Models\City::class;

Models\Clinic::class;

public function areas() {
    return $this->belongsToMny(PracticeArea::class, 'area_clinic_counrty', 'area_id', 'country_id', 'clinic_id');
}

To simplify, the form has a multiple-select for each region, and each Country (let's discar the Cities here). I would like to add/update/delete by using the sync() method for the pivots table when the json response is posted. Ex:

data: {
clinic_id: 2,
practices: { 
    1: ["12","31], // the keys correspond to world region in this ex.
    3: ["7", "12", "42"] // the values to practice areas ids
}}

Thanks in advance on any insights on how to best set this, because in fact I'm quite new to the Eloquent relationships at this advanced level.

What is a clinic branch? The part of a clinic that is operating in a certain country? — Jonas Staudenmeir
Hi Jonas, yes, The Clinic has it's headquarter address (Address::class), then it has several "branches" or addresses throughout the world and each one will have its specific practice areas. Ex. In Hong Kong it may have General Medicine and Oriental Medicine, while in NY it may have different practices areas, Thank you — McRui
Is there a separate model for the branches? Or is a branch a combination of clinic and city? — Jonas Staudenmeir
Yes, I call it the Address::model, which has the firm_id, city_id, country_id and region_id among the normal address fields — McRui
So shouldn't you define a BelongsToMany relationship between Address and PracticeArea? — Jonas Staudenmeir

McRui McRui · Accepted Answer · 2018-08-15T10:57:19

Following Jonas Staudenmeir suggestion and help, I ended up solving the problem with one simple pivot table and the respective methods for the relationship. Thus the following, in case someone has the same issue:

Created a migration for the pivot table

public function up()
{
    Schema::create('address_area', function (Blueprint $table) {
            $table->integer('address_id')->unsigned()->index();
            $table->integer('area_id')->unsigned()->index();
            $table->primary(['address_id', 'area_id']);
    });
}

Now, on the App\Models\Address::class added the method for the relationship:

/**
 * Method to establish the relationship between the address and the practice areas
 *
 * @access public
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 * @since  1.0.1
 */
public function areas()
{
    return $this->belongsToMany(PracticeArea::class);
}

On the other end, the App\Models\PracticeArea::class added the method for the relationship:

/**
 * Method to establish the relationship between the practice areas and the address
 *
 * @access public
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 * @since  1.0.1
 */
public function address()
{
    return $this->belongsToMany(Address::class);
}

Now, every time it's added or removed a practice area on the clinic branch, which has the city_id, country_id and region_id columns, the pivot table is then synced:

// synchronize (add/delete) entries on the pivot table
// $practices array of practice areas
$address->areas()->sync($practices);

This way, multiple queries can be done on both sides – Clinic branches or Practice areas by city, country or region.

Laravel Eloquent multiple relations of many-to-many between models

1 Answers