Murlidhar Fichadia
Reputation: 2609
Laravel Many to Many query relationship with where clause
Problem
I want to make use of eloquent rather than relying in joins
Queries
$relationship = DB::table('tournament_user')
->join('tournaments','tournament_user.tournament_id' , '=', 'tournaments.id')
->join('users', 'tournament_user.user_id', '=', 'users.id')
->select('tournament_user.user_id','tournament_user.tournament_id')
->where('tournament_user.user_id','=',$request->user()->id)
->get();
This query returns list of all tournaments id for a loggedin user.
How can I avoid joins?
I tried this : $relations = User::with('tournaments')->where('id',$request->user()->id)->get();
It returns only one row. whereas there are 3 tournaments that belong to user with id = 2.
I want to simply convert the above Joins query to use eloquent model.
User Model
public function tournaments()
{
return $this->belongsToMany('App\Tournament', 'tournament_user','tournament_id','user_id');
}
Tournament Model
public function users()
{
return $this->belongsToMany('App\User','tournament_user','tournament_id','user_id');
}
Var_Dump
object(Illuminate\Database\Eloquent\Collection)#350 (1){
[
"items":protected
] => array(1) {
[
0
] => object(App\User)#345 (23) {
[
"table":protected
] => string(5) "users" [
"fillable":protected
] => array(5) {
[
0
] => string(4) "name" [
1
] => string(5) "email" [
2
] => string(3) "dob" [
3
] => string(6) "gender" [
4
] => string(8) "password"
} [
"hidden":protected
] => array(2) {
[
0
] => string(8) "password" [
1
] => string(14) "remember_token"
} [
"connection":protected
] => NULL [
"primaryKey":protected
] => string(2) "id" [
"perPage":protected
] => int(15) [
"incrementing"
] => bool(true) [
"timestamps"
] => bool(true) [
"attributes":protected
] => array(12) {
[
"id"
] => int(2) [
"name"
] => string(4) "john" [
"email"
] => string(14) "[email protected]" [
"role"
] => string(4) "user" [
"gender"
] => string(4) "male" [
"status"
] => string(6) "active" [
"dob"
] => string(10) "2001-01-01" [
"password"
] => string(60) "$2y$10$QCtSuNroLftEm.xFLiAbheCt32dSp24rXfn9aJX8pvfbVNVMKyZ.6" [
"remember_token"
] => string(60) "3CPQZ1GSFjTV4qBkCoxt30fOSMrKHsPCkgeMb3uJwKz2nyKUqsDABizIVssH" [
"created_at"
] => string(19) "2016-03-16 10:49:29 " [" updated_at"
] => string(19) "2016-07-23 10:49:35 " [" image"
] => string(13) "/img/user.png"
} [
"original":protected
] => array(12) {
[
"id"
] => int(2) [
"name"
] => string(4) "john" [
"email"
] => string(14) "[email protected]" [
"role"
] => string(4) "user" [
"gender"
] => string(4) "male" [
"status"
] => string(6) "active" [
"dob"
] => string(10) "2001-01-01" [
"password"
] => string(60) "$2y$10$QCtSuNroLftEm.xFLiAbheCt32dSp24rXfn9aJX8pvfbVNVMKyZ.6" [
"remember_token"
] => string(60) "3CPQZ1GSFjTV4qBkCoxt30fOSMrKHsPCkgeMb3uJwKz2nyKUqsDABizIVssH" [
"created_at"
] => string(19) "2016-03-16 10:49:29 " [" updated_at"
] => string(19) "2016-07-23 10:49:35 " [" image"
]=> string(13) "/img/user.png"
}[
"relations":protected
]=> array(1){
[
"tournaments"
]=> object(Illuminate\Database\Eloquent\Collection)#353 (1){
[
"items":protected
] => array(1) {
[
0
] => object(App\Tournament)#352 (23) {
[
"table":protected
] => string(11) "tournaments" [
"fillable":protected
] => array(7) {
[
0
] => string(6) "t_name" [
1
] => string(6) "t_desc" [
2
] => string(6) "t_club" [
3
] => string(10) "t_location" [
4
] => string(6) "t_date" [
5
] => string(11) "t_starttime" [
6
] => string(9) "t_endtime"
} [
"connection":protected
] => NULL [
"primaryKey":protected
] => string(2) "id" [
"perPage":protected
] => int(15) [
"incrementing"
] => bool(true) [
"timestamps"
] => bool(true) [
"attributes":protected
] => array(12) {
[
"id"
] => int(2) [
"t_name"
] => string(22) "Flag Stroke Play Event" [
"t_desc"
] => string(499) "It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout. The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters,
as opposed to using 'Content here. Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s,
when an unknown printer took a galley of type and scrambled it to make a type specimen book." [
"t_club"
] => string(21) "Hermitage golf course" [
"t_location"
] => string(7) "Sharjah" [
"t_date"
] => string(10) "2016-07-20" [
"t_starttime"
] => string(8) "10:00:00 " [" t_endtime"
] => string(8) "01:00:00 " [" lat"
] => float(25.2) [
"lng"
] => float(55.27) [
"created_at"
] => string(19) "2016-02-22 16:00:00 " [" updated_at"
] => string(19) "2016-06-07 12:36:06 " } [" original":protected
]=> array(14){
[
"id"
] => int(2) [
"t_name"
] => string(22) "Flag Stroke Play Event" [
"t_desc"
] => string(499) "It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout. The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters,
as opposed to using 'Content here. Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s,
when an unknown printer took a galley of type and scrambled it to make a type specimen book." [
"t_club"
] => string(21) "Hermitage golf course" [
"t_location"
] => string(7) "Sharjah" [
"t_date"
] => string(10) "2016-07-20" [
"t_starttime"
] => string(8) "10:00:00 " [" t_endtime"
]=> string(8) "01:00:00" ["lat"
]=> float(25.2)[
"lng"
]=> float(55.27)[
"created_at"
]=> string(19) "2016-02-22 16:00:00" ["updated_at"
]=> string(19) "2016-06-07 12:36:06" ["pivot_tournament_id"
]=> int(2)[
"pivot_user_id"
]=> int(2)
}[
"relations":protected
]=> array(1){
[
"pivot"
]=> object(Illuminate\Database\Eloquent\Relations\Pivot)#351 (26){
[
"parent":protected
]=> object(App\User)#331 (23){
[
"table":protected
]=> string(5) "users"[
"fillable":protected
]=> array(5){
[
0
]=> string(4) "name"[
1
]=> string(5) "email"[
2
]=> string(3) "dob"[
3
]=> string(6) "gender"[
4
]=> string(8) "password"
}[
"hidden":protected
]=> array(2){
[
0
]=> string(8) "password"[
1
]=> string(14) "remember_token"
}[
"connection":protected
]=> NULL[
"primaryKey":protected
]=> string(2) "id"[
"perPage":protected
]=> int(15)[
"incrementing"
]=> bool(true)[
"timestamps"
]=> bool(true)[
"attributes":protected
]=> array(0){
}[
"original":protected
]=> array(0){
}[
"relations":protected
]=> array(0){
}[
"visible":protected
]=> array(0){
}[
"appends":protected
]=> array(0){
}[
"guarded":protected
]=> array(1){
[
0
]=> string(1) "*"
}[
"dates":protected
]=> array(0){
}[
"dateFormat":protected
]=> NULL[
"casts":protected
]=> array(0){
}[
"touches":protected
]=> array(0){
}[
"observables":protected
]=> array(0){
}[
"with":protected
]=> array(0){
}[
"morphClass":protected
]=> NULL[
"exists"
]=> bool(false)[
"wasRecentlyCreated"
]=> bool(false)
}[
"foreignKey":protected
]=> string(13) "tournament_id"[
"otherKey":protected
]=> string(7) "user_id"[
"guarded":protected
]=> array(0){
}[
"connection":protected
]=> NULL[
"table":protected
]=> string(15) "tournament_user"[
"primaryKey":protected
]=> string(2) "id"[
"perPage":protected
]=> int(15)[
"incrementing"
]=> bool(true)[
"timestamps"
]=> bool(false)[
"attributes":protected
]=> array(2){
[
"tournament_id"
]=> int(2)[
"user_id"
]=> int(2)
}[
"original":protected
]=> array(2){
[
"tournament_id"
]=> int(2)[
"user_id"
]=> int(2)
}[
"relations":protected
]=> array(0){
}[
"hidden":protected
]=> array(0){
}[
"visible":protected
]=> array(0){
}[
"appends":protected
]=> array(0){
}[
"fillable":protected
]=> array(0){
}[
"dates":protected
]=> array(0){
}[
"dateFormat":protected
]=> NULL[
"casts":protected
]=> array(0){
}[
"touches":protected
]=> array(0){
}[
"observables":protected
]=> array(0){
}[
"with":protected
]=> array(0){
}[
"morphClass":protected
]=> NULL[
"exists"
]=> bool(true)[
"wasRecentlyCreated"
]=> bool(false)
}
}[
"hidden":protected
]=> array(0){
}[
"visible":protected
]=> array(0){
}[
"appends":protected
]=> array(0){
}[
"guarded":protected
]=> array(1){
[
0
]=> string(1) "*"
}[
"dates":protected
]=> array(0){
}[
"dateFormat":protected
]=> NULL[
"casts":protected
]=> array(0){
}[
"touches":protected
]=> array(0){
}[
"observables":protected
]=> array(0){
}[
"with":protected
]=> array(0){
}[
"morphClass":protected
]=> NULL[
"exists"
]=> bool(true)[
"wasRecentlyCreated"
]=> bool(false)
}
}
}
}[
"visible":protected
]=> array(0){
}[
"appends":protected
]=> array(0){
}[
"guarded":protected
]=> array(1){
[
0
]=> string(1) "*"
}[
"dates":protected
]=> array(0){
}[
"dateFormat":protected
]=> NULL[
"casts":protected
]=> array(0){
}[
"touches":protected
]=> array(0){
}[
"observables":protected
]=> array(0){
}[
"with":protected
]=> array(0){
}[
"morphClass":protected
]=> NULL[
"exists"
]=> bool(true)[
"wasRecentlyCreated"
]=> bool(false)
}
}
}
Upvotes: 1
Views: 1017
Answers (2)
Ariful Haque
Reputation: 3750
I guess your relationships in model is wrong.
Your Relationship
public function tournaments()
{
return $this->belongsToMany('App\Tournament', 'tournament_user','tournament_id','user_id');
}
But as per Laravel Documentation,
The third argument is the foreign key name of the model on which you are defining the relationship, while the fourth argument is the foreign key name of the model that you are joining to:
Which mean your relationship in User Model should be like
public function tournaments()
{
return $this->belongsToMany('App\Tournament', 'tournament_user', 'user_id', 'tournament_id');
}
Upvotes: 1
Leon Vismer
Reputation: 5105
It looks correct for a many-to-many relationship.
The following should give you all the users and then for each user it returns the tournaments using eager loading on the tournaments relationship.
$users = User::with('tournaments')->get();
foreach ($users as $user) {
foreach ($user->tournaments as $tournament) {
// Do what you want todo with the tournament
}
}
Then for your case it will return a single user with its tournaments.
$user = User::with('tournaments')->where('id', $request->user()->id)->get();
foreach ($user->tournaments as $tournament) {
// Do what you want todo with the tournament
}
Upvotes: 2
Related Questions
- Laravel Eloquent Many-to-Many query whereIn
- Using multi where queries Laravel relationships
- Laravel 5.1 Where Condition In Many To Many relationships
- $query->where in many to many relationship
- laravel querying many to many
- mysql query in a form of laravel eloquent many to many relationship
- Querying many to many relation laravel
- Laravel eloquent one to many relation where statement
- Many to Many relationship query. Laravel
- Many to many relationship and where in Laravel 4