mysql - Eloquent ORM 导致 MySQL 选择不返回所有行

Question

我正在使用 Laravel 的 Eloquent ORM 查询数据库中的信息，但由于某种原因，生成的查询并未返回所有行。

Eloquent 查询:

\Permit::selectRaw('CONCAT(lk_departments.dept_id, ".") AS dept_id, '. 
                   'lk_departments.dept_name, '.
                   'COUNT(DISTINCT permits.permit_number) AS total')
       ->leftJoin('lk_departments', 'permits.dept_id', '=', 'lk_departments.dept_id')
       ->where('permits.permit_number', '!=', 'N/A')
       ->whereBetween('permits.valid_date', [$query_info['start_date'], $query_info['end_date']])
       ->groupBy('lk_departments.dept_name')
       ->orderBy('lk_departments.dept_name', 'ASC')
       ->get()

结果查询:

SELECT 
CONCAT(lk_departments.dept_id, ".") AS dept_id, lk_departments.dept_name,
COUNT(DISTINCT permits.permit_number) AS total
FROM permits
LEFT JOIN lk_departments ON permits.dept_id = lk_departments.dept_id
WHERE permits.valid_date BETWEEN '01/01/2000' AND '12/12/2100'
GROUP BY lk_departments.dept_name
ORDER BY lk_departments.dept_name ASC

返回数据:

dept_id             dept_name                       total
+--------------------------------------------------------+
| NULL   NULL                                       0    | 
| 1.     Academic Achievement-(Special Services)    11   | 
| 3.     Academic Clubs                             1    | 
| 4.     Academic Custodial & Grounds Services      1    | 
| 5.     Accounting                                 2    | 
| 6.     Admissions                                 356  | 
| 7.     Advanced Programs Dept (Education Dept)    8    | 
| 9.     Aerospace Studies - Air Force ROTC         1    | 
| 11.    American Federation of Teachers            0    | 
| 13.    Anthropology                               1    | 
| 14.    Art Dept.                                  4    | 
| 17.    Athletic Dept.                             11   | 
| 21.    Biology Dept.                              2    | 
|                                                        |
| ...    ...                                        ...  |
+--------------------------------------------------------+ 

我引用了这个问题来修改 total: Count distinct values

但我仍然收到一个 NULL 行，其中 total 为 0 并且缺少记录。我对 MySQL 不是很好，所以如果我遗漏了什么，请原谅我。理想情况下，我希望计算每个部门在给定时间范围内的许可数量。

注意:
dept_id = 自动递增的主键
permits = 包含链接到部门的 parking 许可信息的表

Answer 1

我终于找到问题了。当我在连接中使用 permits 作为主表时，我忽略了如果没有来自 的相应记录，dept_id 的记录将不会显示>permits.dept_id 与 lk_departments.dept_id 匹配(换句话说，如果没有人为该部门注册许可证)。此外，出于某种原因，我不得不更改查询以包含 permits.permit_number 为 null 的记录。

生成 Eloquent 查询:

LKDepartment::selectRaw('lk_departments.*, COUNT(permits.permit_number) AS total')
            ->leftJoin('permits', 'lk_departments.dept_id', '=', 'permits.dept_id')
            ->whereNull('permits.permit_number')
            ->orWhereRaw("(permits.permit_number != 'N/A' AND permits.valid_date BETWEEN '$query_info[start_date]' AND '$query_info[end_date]')")
            ->groupBy('lk_departments.dept_id')
            ->orderBy('lk_departments.dept_id', 'ASC')
            ->get()

结果查询:

SELECT lk_departments.* , COUNT( permits.permit_number ) AS total
FROM lk_departments
LEFT JOIN permits ON lk_departments.dept_id = permits.dept_id
WHERE permits.permit_number IS NULL
OR (
    permits.permit_number != 'N/A' 
    AND permits.valid_date BETWEEN '01/01/2000' AND '12/12/2100'
)
GROUP BY lk_departments.dept_id
ORDER BY lk_departments.dept_id ASC 

返回数据:

dept_id             dept_name                       total
+--------------------------------------------------------+
| NULL   NULL                                       0    | 
| 1      Academic Achievement-(Special Services)    11   | 
| 2      Academic Advising Center                   0    | 
| 3      Academic Clubs                             1    | 
| 4      Academic Custodial & Grounds Services      1    | 
| 5      Accounting                                 2    | 
| 6      Admissions                                 356  | 
| 7      Advanced Programs Dept (Education Dept)    8    | 
| 8      Advisory Council                           0    | 
| 9      Aerospace Studies - Air Force ROTC         1    | 
| 10     Alternative School                         0    | 
| 11     American Federation of Teachers            0    | 
| 12     Anthropology                               0    | 
|                                                        |
| ...    ...                                        ...  |
+--------------------------------------------------------+