Laravel:从 mysql 获取通过和失败结果的总数

Question

我陷入了这个问题。 我在数据库中有一个表，os_flash_result可以是 PASS 或 FAIl。 我必须以带有日期的图形形式显示失败和通过结果的数量。

我的表结构和数据如下：

mysql> select * from mobile_prod_pograms;
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
| id | device_id | os_flash_result | action                 | station | build_id | created_at          | updated_at          |
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
|  2 | 1         | FAIL            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
| 19 | 1         | PASS            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
| 20 | 1         | PASS            | Mobile Production Prog |       1 | mp1      | 2018-09-27 00:00:00 | 2018-09-27 00:00:00 |
+----+-----------+-----------------+------------------------+---------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)

我正在使用 Laravel，所以我使用了以下查询来获取 PASS 结果的计数。

$mobile_chart_query =  DB::table('mobile_prod_pograms')
->select(DB::raw('DATE(created_at) as date'), DB::raw('count(id) as total'))
->where('os_flash_result','=','PASS')
->where('created_at','>=','2018-09-17')
->groupBy('date')
->get();

上述查询工作正常。 但是我想在带有日期的单个查询中获取失败并传递结果计数。我怎样才能做到这一点？ 任何帮助将不胜感激...

Answer 1

您可以使用条件聚合：

$mobile_chart_query =  DB::table("mobile_prod_pograms")
->select(DB::raw("DATE(created_at) AS date"),
DB::raw("COUNT(CASE WHEN os_flash_result = 'PASS' THEN 1 END) AS pass_count"),
DB::raw("COUNT(CASE WHEN os_flash_result = 'FAIL' THEN 1 END) AS fail_count"))
->where("created_at", ">=", "2018-09-17")
->groupBy("date")
->get();

这对应于以下原始 MySQL 查询：

SELECT
DATE(created_at) AS date,
COUNT(CASE WHEN os_flash_result = 'PASS' THEN 1 END) AS pass_count,
COUNT(CASE WHEN os_flash_result = 'FAIL' THEN 1 END) AS fail_count
FROM mobile_prod_pograms    -- should be called mobile_prod_programs
WHERE
created_at >= '2018-09-17'
GROUP BY
date;