CODEWITHSUNDEEP
sqllaravel
Artron
Artron

Reputation: 266

Laravel Date Group By date and count

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

Upvotes: 1

Views: 513

Answers (2)

Artron
Artron

Reputation: 266

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,

Upvotes: 2

Aiden Kaiser
Aiden Kaiser

Reputation: 169

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

Upvotes: 0

Related Questions