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
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.