Ehsan Maleki Zoeram Ehsan Maleki Zoeram - 1 year ago 86
PHP Question

How to cache and find first match in pivot table withing laravel collections?

I have these tables

  • User

  • role

  • user_roles

In user_roles table there are following fields

  • start_date

  • end_date

  • is_active

How can I read all active and not expired roles if current user and put them in cache for one hour?

Is there any way clean cache on one role deactivation?

Answer Source

The relationship is not defined correctly. This should be like the following:

User model

class User {
    public function roles() {
        return $this->hasMany(App\Role::class);

Role model

class Role {
    public function users() {
        return $this->hasMany(App\User::class);

Now create the appropriate pivot table to deal with this relationship

role_user schema

Schema::create('role_user', function(Blueprint $table){



    $table->integer('is_active')->default(0); //change to 1 if you want always to be active

Now modify our User class and add ->withPivot('start_date', 'end_date', 'is_active'); to our roles() relationship.

Updated User model

class User {
    public function roles() {
        return $this->hasMany('App\Role::class')->withPivot('start_date', 'end_date', 'is_active');

But wait, this doesn't get me active roles for my user?! No problem, let's do that with a query scope.

class User { 

    public function scopeOnlyActiveRoles ($query) {
        return $query->whereHas('roles', function($query){
            return $query->where('start_date', '>=', Carbon::now())
                         ->where('end_date', '<=', Carbon::now())
                         ->where('is_active', 1);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download