How to create a query that returns data grouped by date without repetition in Laravel

user_6042141 0
Viewed 74

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",
       },
laravel collections mysql php
1 Answers

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:

  • 2 have created_at => '2020-05-05 10:10:10'
  • 2 have created_at => '2020-10-10 10:10:10'

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"
    }
  ]
}
user_34858770
Related