将用户的时区应用于Laravel中的模型属性

Question

In Laravel 5.3, I've written a collection designed to cycle through a user's bills and determine whether they were paid within a given time frame.

return $dates->map(function ($item, $key) use ($interval) {
    $bills = DB::table('bills')
    ->where('provider_id', Auth::user()->company()->first()->id)
    ->whereBetween(
        'date_paid', [$item, $this->returnRangeFromInterval($item, $interval)]
    )
    ->where('status', 'paid')
    ->get();
    return $bills->pluck('amount_due')->sum();
});

That date_paid attribute is stored as UTC, but to return an accurate sum, I need to shift that date to a user's timezone—which I have stored on the User object. How could I accomplish this in the collection above?

It looks as though I can use a MYSQL method called convert_tz if absolutely necessary, but I'm interested first and foremost in the "Laravel Way".

Answer 1

Collections to the rescue. I was able to use map to modify the attribute in question, and then return a collection of bills with that modified attribute. Also reduced the number of queries I was making, too.

$bills = DB::table('bills')
    ->where('provider_id', Auth::user()->company()->first()->id)
    ->where('status', 'paid')
    ->get();

$adjustedBills = $bills->map(function ($bill, $key) {
    $bill->date_paid = Carbon::parse($bill->date_paid)->timezone('America/New_York')->toDateTimeString();
    return $bill;
});

return $dates->map(function ($item, $key) use ($interval, $adjustedBills) {
    return $adjustedBills->filter(function ($date, $key) use ($item, $interval) {
        return $date->date_paid >= $item && $date->date_paid <= $this->returnRangeFromInterval($item, $interval);
    })->pluck('amount_due')->sum();
})->flatten();