Getting the result of a trailing pivot table

Question

Basically, I have three tables which are users, position, user_position and I want to get all users with their respective positions
Note that a single user can have multiple positions

    The `user_position` table consists of: 
    - user_id
    - position_id

To illustrate... it goes something like this:

users-->user_position<--position

What I got so far is this:

User Model

public function user_positions() {
    return $this->hasMany('App\Models\UserPosition', 'user_id', 'id');
}

UserPosition Model

public function positions() {
    return $this->hasMany('App\Models\Position', 'position_id', 'id');
}

UserController

public function getallusers() {
    $data = User::with('user_positions')->get();
    return response()->json($data);
}

So far it gives me the correct result set. For example:

[{id:1,
  name:'John',
  user_positions:
    [{id:1, 
      user_id:1, 
      position_id: 5
     },
     {id:2, 
      user_id:1, 
      position_id: 9
     }
    ]
 }]

However, it is incomplete, I also wanted the positions array inside the user_positions array but I don't know or I got lost on how to associate/merge/attach (i dont know the right term) the positions function to my User Model.

Answer 1

What you are looking for is a BelongsToMany relation.

You could add the following relation to your User model.

public function positions()
{
    return $this->belongsToMany(Position::class);
}

You could eager load them with:

User::with('positions')->get();

Result:

[{
    ...
    "positions": [{ ...The data of the position... }]
}]