如何在laravel集合中的数据透视表中缓存和查找第一个匹配项？

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?

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->increments('id');

    $table->integer('role_id')->unsigned();
        $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');

    $table->integer('role_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');

    $table->timestamp('start_date');
    $table->timestamp('end_date');
    $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);
        });
    }
}