Counting from Relationships

If you only want the count and not the employee data, laravel has a nice helper.

$brs = BadRunSheet::withCount('Employee')->get();

foreach ($brs as $br) {
    echo $br->Employee_count;
}

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

I am trying to get the count of how many reports are per each station. So the mysql for it would be

SELECT COUNT(*) FROM badrunsheets AS b
LEFT JOIN employees AS e ON b.employee = e.user_id
where b.status = 5
GROUP BY e.primary_station

@joshblevins Did you check the link I posted? You can pass a callback to the withCount method and supply your additional criteria, like status = 5 so you only get a count of records with that status instead of a count of all.

@CRONIX - Sorry I did neglect to look at the link.

So is it even possible to do this without having a station column in bad run sheets to link it to the station table?

I've tried this below.

$station = Station::withCount(['Employees', 'Employees.BadRunSheets' => function ($q){
            $q->where('status', 5);
        }])->get();

I get this error.

BadMethodCallException thrown with message "Method Illuminate\Database\Query\Builder::Employees.BadRunSheets does not exist."

My thought would be I would have to get each station and then employee and calculate the number a bad run sheets they have in a for each loop after the station query is ran.

I think something like this should get you the results

BadRunSheet::query()
    ->leftJoin('employees', 'employees.user_id', '=', 'badrunsheets.employee')
    ->where('badrunsheets.status', 5)
    ->groupBy('employees.primary_station')
    ->selectRaw('
        count(*) as count,
        employees.primary_station as primary_station
    ')->get();

Of course if you need something off the stations table then just add a join and the columns you want to select.

edit: it doesn't really make a lot of sense to use the model if your not selecting all the columns tho so maybe use:

DB::table('badrunsheets')
    ->leftJoin('employees', 'employees.user_id', '=', 'badrunsheets.employee')
    ->where('badrunsheets.status', 5)
    ->groupBy('employees.primary_station')
    ->selectRaw('
        count(*) as count,
        employees.primary_station as primary_station
    ')->get();

@RDELORIER - Out of curiosity... Doesn't using the DB facade compromise security..... "I could be way off base just going off of something I recently read and cant place where I read it at the moment."

I don't see how it possibly could, it's doing the exact same thing. Maybe your thinking of raw inserts or updates but I don't see any way this could have security implications.