Ehsan Maleki Zoeram Ehsan Maleki Zoeram - 2 months ago 15
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

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