Tally occurrences in a given time range

Question

I have a table for downloads (Download), each tuple in the db represents one download. The shape of the table is:

id
file_id
created_at
updated_at

I have a relation set up between File and Download so that I can do:

$file->downloads

// And then I get:
Illuminate\Database\Eloquent\Collection {#4585
    all: [
      App\Models\Download {#4587
        id: 12,
        file_id: 2,
        created_at: "2022-09-02 12:05:12",
        updated_at: "2022-09-02 12:05:12",
      },
      App\Models\Download {#4597
        id: 13,
        file_id: 1,
        created_at: "2022-09-03 12:05:14",
        updated_at: "2022-09-03 12:05:14",
      },
      App\Models\Download {#4598
        id: 14,
        file_id: 1,
        created_at: "2022-09-03 12:08:33",
        updated_at: "2022-09-03 12:08:33",
      },
    ],
  }

I now want to be able to choose a time range (day, week or month) and get the amount of downloads for the given period. As an example let's take day. I then want something like:

{
  '2022-09-02' => 1,
  '2022-09-03' => 2,
}

I can figure out a few ways to do this but they are all awful. The problem is not how I should compare the dates. It is how I should tally them. Thought I'd see if anyone has a simple answer to this problem?

UPDATE:

Since I got tip in the comments about using groupBy I have done so and now have:

$grouped = $file->downloads->groupBy(function ($item, $key) {
    return $item->created_at->format('Y M d');
});

// giving this:

 Illuminate\Database\Eloquent\Collection {#4648
     all: [
       "2022 Sep 02" => Illuminate\Database\Eloquent\Collection {#4651
         all: [
           App\Models\Download {#4625
             id: 12,
             file_id: 2,
             created_at: "2022-09-02 12:05:12",
             updated_at: "2022-09-02 12:05:12",
           },
         ],
       },
       "2022 Sep 03" => Illuminate\Database\Eloquent\Collection {#4649
         all: [
           App\Models\Download {#4598
             id: 13,
             file_id: 1,
             created_at: "2022-09-03 12:05:14",
             updated_at: "2022-09-03 12:05:14",
           },
           App\Models\Download {#4630
             id: 14,
             file_id: 1,
             created_at: "2022-09-03 12:08:33",
             updated_at: "2022-09-03 12:08:33",
           },
         ],
       },
     ],
   }

It is close, but not exactly what I am after:

{
  '2022-09-02' => 1,
  '2022-09-03' => 2,
}

@Lessmore yeah, I was thinking if there perhaps is a way without raw SQL? — Brainmaniac, Sep 03 '22 at 18:17
@Lessmore of corse, it exists in eloquent. This should work! https://laravel.com/docs/9.x/collections#method-groupby I'll try it tnx — Brainmaniac, Sep 03 '22 at 18:20
@Brainmaniac Makes sense now. So are you looking at comparing with created_at column or updated_at column? — nice_dev, Sep 03 '22 at 18:30
Does this answer your question? [How to compare two dates in php](https://stackoverflow.com/questions/8722806/how-to-compare-two-dates-in-php) — nice_dev, Sep 03 '22 at 18:31
@nice_dev hm, don't know but this did! https://stackoverflow.com/questions/45138724/how-to-extract-month-day-from-created-at-column-in-laravel — Brainmaniac, Sep 03 '22 at 19:11
@nice_dev No, this is not really the question that I am asking. Perhaps my question is a bit broad but this was really never the issue. — Brainmaniac, Sep 03 '22 at 19:20
You looking for counts for a specific file or all files? Do you mind if there are gaps in the dates? — Snapey, Sep 03 '22 at 19:25
@Snapey It is for a specific file. Wow, good question... I had not considered that but if there is no download one day it would be preferable if that day dit return empty so I can plot it as zero...... — Brainmaniac, Sep 03 '22 at 19:31
.... hmmm I think I will have to rethink this a bit. I kind of just wanted a quick and dirty way to get this up and running but the point @Snapey is making with the gaps is making this trickier than I thought.. — Brainmaniac, Sep 03 '22 at 19:53

score 1 · Accepted Answer · answered Sep 03 '22 at 20:54

You may be able to adapt this to your needs

$downloads = Download::query()
->select(
  DB::raw("count(*) as total"),
  DB::raw("(DATE_FORMAT(created_at, '%d-%m-%Y')) as date")
)
->whereBetween('created_at',['2022-08-01', '2022-09-01'])
->groupBy(DB::raw("DATE_FORMAT(created_at, '%d-%m-%Y')"))
->get();


$dates = new CarbonPeriod('2022-08-01', '1 day', '2022-08-31');

$data=[];

foreach($dates as $date) {

  $data[$date->format('d-m-Y')] = $downloads->firstWhere('date', $date->format('d-m-Y'))->total ??0;
}

The SQL query looks complicated because you cannot use the created_at as groupBy without some additional processing because of the time variances.

The second half sets up a Carbon period which acts like an array of dates for the period specified.

Within the foreach loop, we iterate over the dates, and check if there is an entry in the database results for the date, and if not puts an entry of zero into the array.

Dates are in UK format throughout, you may want to change to US

Thank you! This is really good. You even helped me seeing an issue with my initial thought of implementation. — Brainmaniac, Sep 04 '22 at 19:52

Tally occurrences in a given time range

1 Answers1