laravel - Laravel year, sum, count, group by eloquent 查询

Question

有人可以告诉我如何用 eloquent 构建这个查询，或者这需要用 DB:Raw 查询编写，还是最好是一个原始的 sql 查询？

select `pay_week`,
       year(`start_timestamp`) as year,
       sum(`total_duration`) as 'duration',
       count(`pay_week`) as 'entries'
from `time_clock`
group by year(`start_timestamp`),`pay_week`;

任何有助于更好理解的帮助将不胜感激。

谢谢你。

$entries = TimeClock::select(
   'pay_week', 
   DB::raw('count(pay_week) as entries'), 
   DB::raw('YEAR(start_timestamp) as year'), 
   DB::raw('sum(total_duration) as duration')
)
->where('user_id', $user->record_id)
->sum('total_duration')
->groupBy('pay_week')
->get();

结果是

Call to a member function groupBy() on float

我现在拥有的；

刀;

<table class="table table-hover dataTable table-striped width-full" data-plugin="dataTable">
                                <thead>
                                    <th>Pay Week</th>
                                    <th>Date Range</th>
                                    <th>Year</th>
                                    <th>Total Hours</th>
                                    <th>Gross Pay</th>
                                    <th>Entries</th>
                                    <th>Action</th>
                                </thead>
                                <tbody>
                                @foreach($entries as $group)
                                    @foreach($group->entries as $entry)
                                        @php
                                            $week_start = (new DateTime())->setISODate(date("Y"),$entry->pay_week)->format("m/d");

                                            $start = \Carbon\Carbon::createFromFormat("m/d", $week_start);
                                            $end = $start->copy()->endOfWeek()->format('m/d');
                                            $year = \Carbon\Carbon::parse($entry->start_timestamp)->format('Y');

                                        @endphp
                                        <tr>
                                            <td>{{ $entry->pay_week }}</td>
                                            <td>{{ $start->format('m/d') . ' - ' . $end }}</td>
                                            <td>{{ $year }}</td>
                                            <td>0</td>
                                            <td>$ 0.00</td>
                                            <td>0</td>
                                            <td>[ Btn ]</td>
                                        </tr>
                                    @endforeach
                                @endforeach
                                </tbody>
                            </table>

这就是我在控制器方法中所拥有的。

$user = Auth::guard('user')->user();

        $entries = TimeClock::select(
            'pay_week',
            'start_timestamp',
            'total_duration'
        )
            ->where('user_id', $user->record_id)
            ->get()
            ->groupBy('pay_week')
            ->map(function($entry) {
                return (object)[
                    'count' => $entry->count(),
                    'duration' => $entry->sum('total_duration'),
                    //'year' => $entry->sum('start_timestamp')->year,
                    'entries' => $entry
                ];
            });

        return view('user.timeclock.reports.all', [
            'entries' => $entries
        ]);

标签： laraveleloquent

Answer 1

鉴于，您在模型中设置了演员表

class TimeClock extends Model
{
    protected $cast = [
        'start_timestamp' => 'datetime'
    ];
}

您可以执行以下操作：

$entries = TimeClock::select(
   'pay_week', 
   'start_timestamp', 
   'total_duration'
)
->where('user_id', $user->record_id)
//->groupBy('pay_week')
->get()
->groupBy([ 'pay_week', function($entry) {
    return $entry->start_timestamp->year
})
/*->map(function($entry) {
    return (object)[
        'count' => $entry->count(),
        'duration' => $entry->sum('total_duration'),
        'year' => $entry->start_timestamp->year,
        'entries' => $entry
    ];
})*/;

你像这样循环它：

<table>
    <thead>
        <tr>
            <th>Pay week</th>
            <th>Date range</th>
            <th>Year</th>
            <th>Total hours</th>
        </tr>
    </thead>
    <tbody>
        @foreach($entries as $pay_week => $groups)
            @foreach($groups as $year => $group)
                <!--pay_week: {{ $pay_week }}<br />
                count: {{ $group->count() }}<br />
                duration: {{ $group->sum('total_duration') }}<br />
                year: {{ $year }}<br />-->
                <tr>
                    <td>{{ $pay_week }}</td>
                    <td></td>
                    <td>{{ $year }}</td>
                    <td>{{ $group->sum('total_duration') }}</td>
                </tr>
                @foreach($group as $entry)
                @endforeach
            @endforeach
        @endforeach
    </tbody>
</table>