Laravel 查询：高效实现日志数据按用户、状态及时间范围的计数与过滤

use Carbon\Carbon;
use App\Models\WebhookLog; // 假设您的模型名为 WebhookLog

// 假设 $companyId 和 $statusCode 已经获取
$companyId = $company->id; // 例如，从当前登录用户或传入参数获取
$statusCode = 400; // 例如，需要统计的状态码

$baseQuery = WebhookLog::where('company_id', $companyId)
                       ->where('status_code', $statusCode);

$todayLogsCount = $baseQuery->whereBetween('updated_at', [
                                Carbon::today()->startOfDay(),
                                Carbon::today()->endOfDay()
                            ])
                            ->count();

// 如果您需要获取这些日志的列表，而不是计数
$todayLogs = $baseQuery->whereBetween('updated_at', [
                            Carbon::today()->startOfDay(),
                            Carbon::today()->endOfDay()
                        ])
                        ->orderBy('updated_at', 'desc') // 可选：按更新时间降序排列
                        ->get();

$last24hLogsCount = $baseQuery->where('updated_at', '>=', Carbon::now()->subDay())
                               ->count();

// 如果您需要获取这些日志的列表
$last24hLogs = $baseQuery->where('updated_at', '>=', Carbon::now()->subDay())
                         ->orderBy('updated_at', 'desc')
                         ->get();

<?php

namespace App\Http\Controllers;

use App\Models\WebhookLog;
use Carbon\Carbon;
use Illuminate\Http\Request;

class LogAnalysisController extends Controller
{
    /**
     * 分析特定公司和状态码的日志。
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $companyId
     * @return \Illuminate\Http\JsonResponse
     */
    public function analyzeLogs(Request $request, $companyId)
    {
        // 从请求中获取状态码，如果未提供则使用默认值或抛出错误
        $statusCode = $request->input('status_code', 400);

        // 构建基础查询
        $query = WebhookLog::where('company_id', $companyId)
                           ->where('status_code', $statusCode);

        // --- 统计今天的日志 ---
        $todayLogsCount = (clone $query)->whereBetween('updated_at', [
                                            Carbon::today()->startOfDay(),
                                            Carbon::today()->endOfDay()
                                        ])
                                        ->count();

        // --- 统计过去24小时的日志 ---
        $last24hLogsCount = (clone $query)->where('updated_at', '>=', Carbon::now()->subDay())
                                          ->count();

        // --- 获取今天的日志列表（例如，用于展示）---
        $todayLogsList = (clone $query)->whereBetween('updated_at', [
                                            Carbon::today()->startOfDay(),
                                            Carbon::today()->endOfDay()
                                        ])
                                        ->orderBy('updated_at', 'desc')
                                        ->get();

        return response()->json([
            'company_id' => $companyId,
            'status_code' => $statusCode,
            'today_logs_count' => $todayLogsCount,
            'last_24h_logs_count' => $last24hLogsCount,
            'today_logs_list' => $todayLogsList->toArray(), // 将集合转换为数组以便JSON输出
        ]);
    }
}