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

Question

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 1

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);
        });
    }
}