CODEWITHSUNDEEP
laravellaravel-5.7
Hasan Rajani
Hasan Rajani

Reputation: 3

Laravel Eloquent - Join 2 tables through other table

I build relationship between 2 tables through 3rd table. I have 3 tables in total.

1.) addresses

Schema::create('addresses', function (Blueprint $table) {
    $table->increments('id');
    $table->text('line_1_number_building')->nullable();
    $table->text('line_2_number_street')->nullable();
    $table->text('line_3_area_locality')->nullable();
    $table->text('city')->nullable();
    $table->text('zip_code')->nullable();
    $table->text('state_province_county')->nullable();
    $table->text('country')->nullable();
    $table->longText('other_address_details')->nullable();
    $table->timestamps();
});

2.) staffs

Schema::create('staffs', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('staff_category_id');
    $table->integer('gender_id');
    $table->text('staff_job_title')->nullable();
    $table->text('staff_first_name')->nullable();
    $table->text('staff_middle_name')->nullable();
    $table->text('staff_last_name')->nullable();
    $table->text('staff_qualifications')->nullable();
    $table->date('staff_birth_date');
    $table->longText('other_staff_details')->nullable();
    $table->timestamps();
});

3.) staff_addresses

Schema::create('staff_addresses', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('staff_id');
    $table->integer('address_id');
    $table->date('date_address_from')->nullable();
    $table->date('date_address_to')->nullable();
    $table->timestamps();
});

Now i want to get list of all addresses for any staff entry. I tried below code but it returns addresses which matches ID of Staff ID

What I tried

public function saddresses()
{
    return $this->hasManyThrough('App\addresses','App\staff_addresses','staff_id','id');
}

What i got

Address : [{"id":3,"line_1_number_building":"Hasan 2 line 1","line_2_number_street":"Hasan 2 line 2","line_3_area_locality":"Hasan 2 line 3","city":"Hasan 2 city","zip_code":"Hasan 2 zip code","state_province_county":"Hasan 2 state","country":"Hasan 2 country","other_address_details":"Hasan 2 other details","created_at":null,"updated_at":null,"staff_id":5},{"id":4,"line_1_number_building":"Sadiq 2 line 1","line_2_number_street":"Sadiq 2 line 2","line_3_area_locality":"Sadiq 2 line 3","city":"Sadiq 2 city","zip_code":"Sadiq 2 zip code","state_province_county":"Sadiq 2 state","country":"Sadiq 2 country","other_address_details":"Sadiq 2 other details","created_at":null,"updated_at":null,"staff_id":5}]

Better view

Can you please guide me how can i build proper relationship?

Regards, Hasan Rajani.

Upvotes: 0

Views: 513

Answers (2)

Vandit P. Kotadiya
Vandit P. Kotadiya

Reputation: 36

This solution is perfect @Capt. Teemo. But, if you want an address for particular one staff then here I have provided the solution.

You have to define this function in Staff model.

public function addresses() { return $this->belongsToMany('App\addresses'); }

Query to get all addresses of $staffId (where $staffId is the id of staff table)

$data = Staff::with('addresses')->where('id', $staffId)->first();

And also if you want to get extra attributes in the pivot table, you must specify them when defining the relationship.

public function addresses() { return $this->belongsToMany('App\addresses')->withPivot('date_address_from', 'date_address_to'); }

Also, you can maintain created_at and updated_at column automatically using withTimestamps method in the relationship.

For reference: https://laravel.com/docs/5.7/eloquent-relationships#many-to-many

Upvotes: 1

Kapitan Teemo
Kapitan Teemo

Reputation: 2164

First, try to fix your naming conventions. Things would be a lot easier if you follow laravel's naming convention. Here is the guide.

  • Pivot Tables should be singular model names in alphabetical order (change staff_addresses to address_staff)

Then use belongsToMany relationship.

staff model:

public function addresses()
{
    return $this->belongsToMany('App\addresses');
}

Here's how you query:

$data = Staff::with('addresses')->get();

Upvotes: 0

Related Questions