How can I get records created_by user with role

Question

Hi I have a table "fiches" with the user_id who created the record. All my users have a role_id.

How can I get all "fiches" where user_id has the role of 'admin' or something else.

 User.php
 class User extends Authenticatable{

     public function role()
     {
         return $this->belongsTo(Role::class);
     }

     // Fiches of User
     public function fiches()
     {
         return $this->hasMany(Fiche::class);
     }

  }


 Fiche.php
 class Fiche extends Model{

     public function user()
     {
         return $this->belongsTo('App\User');
     }

 }

My Query

 $fiches = Fiche::whereDay('created_at', '=', date('d'))->where('status', '=', 'A Ecouter')->pluck('id')->count();

Fiches table (id, user_id, name, status, created_at)

Users table (id, role_id, name, created_at)

I want to have the Fiches that was created by role == Admin

Answer 1

You need to use whereHas with nested relationship. Assuming your roles table has column type the code will look like:

$fiches = Fiche::whereHas('user.role', function ($query) {
    $query->where('type', 'admin');
})->get();

This query will retrieve all fiches that have users with role that has column 'type' of value 'admin'.

Edit:

For your specific case you provided in the comment the query should look like this:

$fichesAEcouterJours = Fiche::whereDay('created_at', '=', date('d'))
    ->where(function ($query) {
        $query->where('status', '=', 'A Ecouter')
            ->orWhere('status', '=', 'A Reporter');
    })
    ->whereHas('user.role', function($query){ 
        $query->where('name', 'agent'); 
    })
    ->count();

Or you can use ->whereIn('status', ['A Ecouter', 'A Reporter']) instead. Note that there is no need to call ->get() before ->count() in this case - it's faster to let Eloquent generate SELECT COUNT query than it is to ->get() collection of all rows and call ->count() of that collection.