CODEWITHSUNDEEP
phpmysqllaravelormeloquent
pavon147
pavon147

Reputation: 713

Laravel Eloquent filter relationship by last pivot record

I've got 3 tables: user, role and pivot user_role. There is many-to-many relationship between user and role.

And one more detail, every assignment (role to user) has timestamp in user_role table, so I can find what role has been added recently for user.

Is there a possibility to write an eloquent query to get all users which recently assigned role has e.g id = 5?

I need somehow to get last record from pivot ordered by timestamp and then use it in whereHas statement. But how to get that last record?

Edit

Thank you for your answers. But I would like to make it all a little bit more complicated. What if we add table system and now will be relationship one-to-many between system and user, so user can belong to one system and system can have multiple users.

And now, how to get e.g. id of system which contains users having last role with id = 5 (as at the beginning).

Upvotes: 2

Views: 3254

Answers (3)

Sanzeeb Aryal
Sanzeeb Aryal

Reputation: 3266

This gives the last user ordered by timestamp column from user_role table.

$user->roles()->withPivot('timestamp')->orderBy('user_role.timestamp','desc')->first();

Update:

$users=User::with(['system','role'=>function($query)
{
   $query->orderBy('pivot_timestamps','desc');
}])->get();

then, this gives system id of all the users ordered by timestamps in pivot table.

foreach($users as $user)
{
      $user->system->id;
}

Make sure you have relations in user and role model withPivot. so that you can use pivot_timestamps

return $this->belongsToMany('users')->withPivot('timestamps');

Upvotes: 2

SimonDepelchin
SimonDepelchin

Reputation: 2080

You can add pivot columns to your relation, like this :

/**
 * The roles that belong to the user.
 */
public function roles()
{
    return $this->belongsToMany('App\Role')->withPivot('timestamp');
}

Then if you want to check if user has a specific role recently I'd do this

public function hasRecentRole($roleId, $howRecently = 'last week')
{
     return $this->roles()->where('id', $roleId)->wherePivot('timestamp', '>', new Carbon\Carbon($howRecently))->count();
}

EDIT: Try something like this.

$systems = System::whereHas('users', function ($query) {
    $query->whereHas('roles', function ($query) {
        $query->where('id', 5)
            ->whereRaw('timestamp = (select max(`timestamp`) from user_roles WHERE user_id = role_user.user_id'));
    });
})->get();

If not maybe you ought to work in steps. Like get users with latest role 5 then get system that has at least one of these users.

Upvotes: 2

Armin Sam
Armin Sam

Reputation: 933

First you need to make sure your relationship includes pivot timestamps:

public function users()
{
    return $this->belongsToMany('User')->withTimestamps();
}

Then you can use the following query in your code to get all users who have recently received a specific role (e.g. id=5).

$role = Role::with(['users', function($query) {
    $query->orderBy('pivot_created_at', 'DESC');
}])->find(5);

// all users with (role_id = 5) ordered by (user_role.created_at DESC)
$users = $role->users;

Upvotes: 2

Related Questions