Laravel Date Group By date and count

Question

i have table 'country_visit' like this

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

I need result like this

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

I was success when use SQL like this

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')

How that query equivalent with Laravel queries ? Btw i still using Laravel 5.4

is laravel a data entity framework? laravel seems php based which is independent of backend data access and thus you should keep separate from or at least modularize. you have sql that works, create a class that passes that data to the front — interesting-name-here
– interesting-name-here, Commented Dec 1, 2018 at 2:38
i was tried using db raw, like this DB::select("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')"); . but still doesnt works — Artron
– Artron, Commented Dec 1, 2018 at 2:54

Artron · Accepted Answer · 2018-12-01 04:20:04Z

2

I solve my self

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

the key is in

config/database.php

some setting need to change from

'strict' => true,

to

'strict' => false,

answered Dec 1, 2018 at 4:20

Artron

2661 gold badge5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aiden Kaiser · Accepted Answer · 2018-12-01 03:20:40Z

0

$table = DB::table('country_visit')
        ->select('city', DB::raw('count(*) as total'))
        ->groupBy('dateis')
        ->get();

answered Dec 1, 2018 at 3:20

Aiden Kaiser

1691 silver badge10 bronze badges

3 Comments

interesting-name-here Over a year ago

looks close but they want date too

Artron Over a year ago

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dateis' in 'group statement' (SQL: select city, count(*) as total from country_visit group by dateis)

Aiden Kaiser Over a year ago

@Artron Don't you have a dateis column in your table?

Collectives™ on Stack Overflow

Laravel Date Group By date and count

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related