Murlidhar Fichadia Murlidhar Fichadia - 4 months ago 25
MySQL Question

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) "john@gmail.com" [
"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) "john@gmail.com" [
"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)
}
}
}

Answer

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');
}