如何在Laravel中编写查询及生成图表所需的统计JSON数据

Hey there! Let's get that Laravel query sorted so you can generate the exact JSON your chart needs. Here's a step-by-step breakdown tailored to your requirements:

Key Requirements Recap #

We need a JSON array with every day from the last 30 days (even days with no activity) in this format:

[{ "day": "23", "count": 20 }, { "day": "22", "count": 10 }, ...]

Step 1: Generate a Full 30-Day Date Range #

First, we'll create a collection of all 30 days (including days with zero orders) to avoid gaps in your chart. This ensures every day shows up, even if no orders were placed:

use Carbon\Carbon;

// Build a collection of the last 30 days (with their day number and full date)
$dateRange = collect();
$startDate = Carbon::today()->subDays(29); // Start 29 days ago to get exactly 30 days (including today)

for ($i = 0; $i < 30; $i++) {
    $dateRange->push([
        'day' => $startDate->format('d'), // Get two-digit day (e.g., "05" or "23")
        'full_date' => $startDate->toDateString()
    ]);
    $startDate->addDay();
}

Step 2: Query Order Statistics #

Now let's fix your existing query to get accurate order counts grouped by full date (this avoids mixing up days from different months, like 1/23 and 2/23):

$orderCounts = Order::selectRaw('DATE(created_at) as full_date, COUNT(*) as count')
    ->where('created_at', '>=', Carbon::today()->subDays(30))
    ->groupBy('full_date')
    ->get()
    ->keyBy('full_date'); // Index results by full date for easy matching later

Step 3: Merge Data & Fill Zero Counts #

Combine our date range with the order stats, filling in 0 for days with no orders:

$chartData = $dateRange->map(function ($date) use ($orderCounts) {
    return [
        'day' => $date['day'],
        'count' => $orderCounts->has($date['full_date']) ? $orderCounts[$date['full_date']]['count'] : 0
    ];
});

Step 4: Convert to JSON #

Finally, convert the collection to the JSON format your chart expects:

$jsonOutput = $chartData->toJson();
// Output will look exactly like your example:
// [{"day":"23","count":20},{"day":"22","count":10},...]

Simplified Version (If Gaps Are Acceptable) #

If your chart doesn't need to show days with zero orders, you can use this shorter query (note: this may have duplicate day numbers across months):

$jsonOutput = Order::selectRaw('DAY(created_at) as day, COUNT(*) as count')
    ->where('created_at', '>=', Carbon::today()->subDays(30))
    ->groupBy('day')
    ->orderBy('day')
    ->get()
    ->toJson();

Why This Works #

• Full date grouping: Prevents overlapping day numbers from different months (a common gotcha with just DAY(created_at)).
• Date range collection: Ensures no gaps in your chart data, which is critical for consistent visualization.
• Clean JSON output: Matches exactly the format you specified for your chart.

内容的提问来源于stack exchange，提问作者Rob S