Laravel-如何使用Laravel语法使用count语句编写两个内部联接? [英] Laravel - How to write two inner joins with a count statement in Laravel syntax?

问题描述

我有三个表:

1. Users(列id，dept_id)
2. Departments(列id，deptStringName)
3. Absences(列id，user_id)

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

我正在尝试找出每个部门的缺勤情况.我知道我需要两个内部联接，但此刻我对如何使用Laravel Query Builder语法在两个内部联接中插入计数感到困惑.

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.

最终结果可能是:

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

我也不介意SQL语法.

I don't mind the SQL syntax either.

推荐答案

所以基本上，以下内容应该可以工作:

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();

注意:按分组时，应按行的唯一值(例如标识符)分组.如果保证您的部门名称是唯一的，则可以完全省略按department.id选择和分组的信息.

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.

这篇关于Laravel-如何使用Laravel语法使用count语句编写两个内部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！