Level 1
This is an interesting question. Does anyone have a solution?
1 like
Feb 16, 2017
9
Level 2
How to Join Multiple Tables with Eloquent Relationships
Hello,
I have Six tables in a sequence and I want to join them with Eloquent relationships. While I am trying to do this Laravel throws an exception as Laravel accepts only one intermediate table in its relationships. Is there any possibility to join more than three tables. Here is my scenario.
Tables:
- Countries Table: id, name
- States Table: id, name, country_id
- Cities Table: id, name, state_id
- Companies Table: id, name, city_id
- Users Table: id, name, company_id
- Booking Table: id, name, user_id, status (Status 1 represents successful booking and 0 represents failure)
Every model has a hasMany or hasManyThrough relationship and belongsTo relationship to and fro. Now I want to get failure bookings from a state. I can achieve this through manual join queries. But I need to access it through eloquent.
Booking Model:
public function user() {
return $this->belongsTo('App\User');
}
User Model:
public function company() {
return $this->belongsTo('App\Company');
}
public function bookings() {
return $this->hasMany('App\Booking');
}
Company Model:
public function city() {
return $this->belongsTo('App\City');
}
public function users() {
return $this->hasMany('App\User');
}
public function bookings() {
return $this->hasManyThrough('App\Booking' , 'App\User');
}
City Model:
public function state() {
return $this->belongsTo('App\State');
}
public function companies() {
return $this->hasMany('App\Company');
}
State Model:
public function country() {
return $this->belongsTo('App\Country');
}
public function cities() {
return $this->hasMany('App\City');
}
Country Model:
public function states() {
return $this->hasMany('App\State');
}
I am able to add hasManyThrough upto companies. So I can only able retrieve bookings of a company.
$company_bookings = App\Company::find(id)->users->bookings;
But, how can I retrieve bookings of a city, state, and country.
Thanks in advance.
Level 75
Yes, don't over join. Here is a good read on this sort of thing. http://laravel.io/forum/05-12-2015-has-many-through-relationship-depth
Normally a well designed application only requires one to many relations at most.
1 like
Level 4
Normally a well designed application only requires one to many relations at most.
@jlrdw Well, that's not true. There are cases where you will need to have many to many relationship.
6 likes
Level 6
As I see it you have three choices:
- Live with the limits of eloquent and have to chain your query through the various models. This might perform slowly depending on the quantity of data. Eloquent is there to make most querying easy, but it won't always produce a simple equivalent in code.
- Write your query using the query builder: https://laravel.com/docs/5.5/queries
- Do a
DB::select()and put all your joins in a single manual query.
I would go with option 2 in this case.
5 likes
Level 1
Hi guys, here a simple trait
https://gist.github.com/MatteoOreficeIT/d3f66e90436dd5b9c90fbe144118e667
1 like
Level 4
@ryanooi no no, there is no need to have many to many relationship, you need to make a pivot table
1 like
Level 1
hii @surendrasahi, $company_bookings = App\Company::find(id)->users->bookings; Basically this is not possible because you have hasMany relationship, this will work for hasOne relationship. Now solution , use laravel eager load (its very easy to use) $company_bookings=App\Company::with('users','users.bookings')->where('id', 1)->get()->toArray(); That's it. (you can get halp from https://laravel.com/docs/7.x/eloquent-relationships#constraining-eager-loads) Thanks
1 like
Level 14
If you're wanting to do this through joins, you could do it this way:
Country::joinRelation('states.cities.companies.bookings', function ($join) {
$join->where('bookings.status', '=', 0)
});
This requires using a package I created (reedware/laravel-relation-joins) to join over relationships.
The query builder alternative ends up being this:
Country::query()
->join('states', 'states.country_id', '=', 'countries.id')
->join('cities', 'cities.state_id', '=', 'states.id')
->join('companies', 'companies.city_id', '=', 'cities.id')
->join('users', 'users.company_id', '=', 'companies.id')
->join('bookings', function ($join) {
$join->on('bookings.user_id', '=', 'users.id');
$join->where('bookings.status', '=', 0);
});
Not to mention that the query builder alternative doesn't automatically considered soft deletes.
2 likes
Please or to participate in this conversation.