I have a table scans in my db, where a user's scan activity is recorded and a user can have multiple activities in a day. I am looking for the best way to fetch the sum of all the scans for a particular user over a period of days.
I have tried the following in my code, but I guess I'm missing something as I would like to aggregate the scans for a user that was made same day.
DB::table('scans')->select(DB::raw('count(*) as total, scanner, created_at'))->where('scanner', 'Jon Snow')->groupBy('created_at')->get()
=> Illuminate\Support\Collection {#3313
all: [
{#3304
+"total": 1,
+"scanner": "Jon Snow",
+"created_at": "2020-07-26 19:43:58",
},
{#3321
+"total": 1,
+"scanner": "Jon Snow",
+"created_at": "2020-07-26 19:32:40",
},
{#3327
+"total": 1,
+"scanner": "Jon Snow",
+"created_at": "2020-07-28 19:44:18",
},
{#3323
+"total": 1,
+"scanner": "Jon Snow",
+"created_at": "2020-07-28 20:30:30",
},
So instead of this:
{#3304
+"total": 1,
+"scanner": "Jon Snow",
+"created_at": "2020-07-26 19:32:58",
},
{#3321
+"total": 1,
+"scanner": "Jon Snow",
+"created_at": "2020-07-26 19:43:40",
},
I would have this:
{#3304
+"total": 2,
+"scanner": "Jon Snow",
+"created_at": "2020-07-26",
},
{#3304
+"total": 5,
+"scanner": "Jon Snow",
+"created_at": "2020-07-28",
},
Problem:
At the moment you are doing groupBy(created_at). Since almost all Scans have a diffrent datetime the groupBy will not work as excpected.
Solutions:
If I understood correctly, you want the groupBy by date of created_at without the time. For that you will have to select only the date part of your datetime column. There are 2 ways to do this:
DB::raw('count(*) as total, DATE_FORMAT(created_at, "%Y-%m-%d") as someDate, scanner')
->where('scanner', 'Jon Snow')
->groupBy('someDate')
->get()
@edit
You could also do DATE(created_at) as someDate
Example:
I have 4 Users inserted into my Database:
Result:
Illuminate\Support\Collection {#1945
#items: array:2 [
0 => {#1952
+"count": 2
+"new_date": "2020-05-05"
}
1 => {#2092
+"count": 2
+"new_date": "2020-10-10"
}
]
}