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... }]
}]

Answer 2

When you want to m:m relation, it's advised to use BelongsToMany, there is no need for an intermediate model for the "pivoted" table.

If you have non-eloquent standard naming conventions. Then you have to specify the table name in the 2nd parameter as followed:

return $this->belongsToMany('App\Models\Positions', 'tbl_positions_assigned');

In the 3rd and 4th parameter you specify the foreign key names.

I'd like to refer to you to the documentation Eloquent-relationships as it's very thoroughly explained over there.

One very strong thing you can do with belongsToMany is to append extra columns in the pivot table. Those are also easily obtained from the pivot table. This is useful if you wish to store extra data values specific to the model.

return $this->belongsToMany('App\Models\Positions')->wherePivot('active', 1);

With above example you could exclude certain values, that are not active in this example, when you retrieve the results from the query.