How do i count for a specific data in a column in Laravel?

Question

I have two models, One is Employees and other one is Skill. One Employee can have many skills. Now I'm trying to show number of employees that possesses each consecutive skill in skills view page.

My Skills View Page:

 <tbody>

                    @foreach ($skills as $skill)
                        <tr>
                            <th scope="row">{{ $loop->index+1 }}</th>
                            <td>{{ $skill->skill_name }}</td>
                            <td>{{ $skill->totalEmp($skill->id) }}</td>
                            <td style="width: 25%;">
                                <button class="btn btn-outline-danger" type="button" title="Delete Skill">Delete Skill</button>
                            </td>
                        </tr>
                    @endforeach

                </tbody>

Skills Model:

class Skill extends Model
{



  use HasFactory;

protected $fillable = [
    'skill_name'
];

public function Employee()
{
     return $this->hasMany(Employees::class, 'skill_id', 'id');
}


public function totalEmp($id){
    return Employees::where('status', 1)->where('skill_id','=',$id)->count();
}


}

Employees Model:

class Employees extends Model

{ use HasFactory;

protected $guarded = [];

protected $table = 'employees';

public function Skills(){
    return $this->hasMany(Skill::class);
}
}

Employee table has->skill_id, other irreverent fields || Skill Model has->id and skill_name field.

Answer 1

You can use withCount method for this.

https://laravel.com/docs/9.x/eloquent-relationships#counting-related-models

On your Skill model you have Employee relationship defined.

So you will be able to do something like this:

$skills = Skill::withCount(['Employee'])->get();

// Prints out number of employees that posses first skill
echo $skills[0]->employee_count;

Of course you can iterate through $skills with for loop, or foreach and similar, or even use collection methods.

<tbody>
   @foreach ($skills as $skill)
   <tr>
      <th scope="row">{{ $loop->index+1 }}</th>
      <td>{{ $skill->skill_name }}</td>
      <td>{{ $skill->employee_count }}</td>
      <td style="width: 25%;">
         <button class="btn btn-outline-danger" type="button" title="Delete Skill">Delete Skill</button>
      </td>
   </tr>
   @endforeach
</tbody>

If you still want to use your method totalEmp you can modify it like this:

public function totalEmp(){
    return $this->employee()->count();
}

One other thing that I strongly recommend is to make sure you name your models and relationships properly.

Models should be named like: Skill and Employee Relationships should be named like: employees() and skills()

Please see example here: https://laravel.com/docs/9.x/eloquent-relationships#many-to-many