php - Laravel - How to write two inner joins with a count statement in Laravel syntax?

Question

I have three tables:

1. Users (columns id, dept_id)
2. Departments (columns id, deptStringName)
3. Absences (columns id, user_id)

I am trying to figure out Absences per Department. I know I need two inner joins, but at the moment I am confused about how to insert a count in two inner joins using the Laravel Query Builder syntax.

The eventual result could be like:

+-------+---+
| DeptA | 3 |
| DeptF | 7 |
| DeptH | 3 |
| DeptT | 7 |
| DeptZ | 5 |
+-------+---+

EDIT: I don't mind the SQL syntax either.

Answer 1

So basically something like the following should work:

DB::table('departments')
    ->join('users','users.dept_id','=','departments.id')
    ->join('absences','users.id','=','absences.user_id')
    ->select('departments.id','departments.deptStringName', DB::raw("COUNT(*)"))
    ->groupBy('departments.id','departments.deptStringName')
    ->get();

Note: When grouping by you should group by the unique values of the row (e.g. the identifier). If your department names are guaranteed to be unique then you can omit selecting and grouping by department.id completely.