Tally occurrences in a given time range-CodePudding

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,
}

CodePudding user response：

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