sql - Laravel 日期分组按日期和计数

Question

我有这样的表'country_visit'

City     | Created_at
-------------------------------
Jakarta  | 2018-11-15 06:27:22
Jakarta  | 2018-11-12 05:25:24
Jakarta  | 2018-11-11 05:27:23
Jakarta  | 2018-11-15 07:27:22
Denpasar | 2018-11-15 05:27:22
Denpasar | 2018-11-12 05:27:22
Makassar | 2018-11-15 05:27:22
Makassar | 2018-11-11 05:27:22
Makassar | 2018-11-12 05:27:22

我需要这样的结果

City     | dateis    | total
-------------------------------
Jakarta  | 15 Nov    | 2
Jakarta  | 12 Nov    | 1
Jakarta  | 11 Nov    | 1

像这样使用 SQL 时我成功了

SELECT city,DATE_FORMAT(created_at,"%d %b") AS dateis, COUNT(city) AS total
FROM country_visit
WHERE city = 'Jakarta'
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d')

该查询如何与 Laravel 查询等效？顺便说一句，我仍在使用 Laravel 5.4

标签： sqllaravel

Answer 1

我解决我自己

select("city",
DB::raw("DATE_FORMAT(created_at,'%d %b') AS dateis"),
DB::raw("COUNT(city) AS total"))
->where("city","Jakarta")
->groupby(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d')"))
->get(); 

关键在

config/database.php

一些设置需要从

'strict' => true,

至

'strict' => false,