Laravel获取近12个月用户每月登录数据的问题求助

Hey there! Let's work through getting accurate monthly login activity data for your bar chart. I see where your previous attempts went wrong, so let's break it down and fix it step by step.

What Was Wrong With Your Previous Code? #

First Attempt: Negative Month Values #

Your initial loop had a critical issue: when calculating the oldest month (12 months ago), date('n') - $age could result in a negative number (e.g., if current month is 3, 3 - 12 = -9). Since MySQL only recognizes months 1-12, this query would return zero results for those invalid months, leading to inaccurate counts.

Second Attempt: Incorrect Query Logic #

Your revised code used whereMonth('created_at', '>=', Carbon::now()->subMonth(12)), which misuses the whereMonth method. whereMonth filters by the numeric month (1-12), not a date range. Additionally, you were counting all matching records at once instead of grouping them by month, so you ended up with a total count (or zero if the condition was wrong) instead of per-month data.

The Correct Solution #

We need to:

1. Fetch activity data for the past 12 months.
2. Group the data by year and month (to handle year-overlapping months like November 2023 to October 2024).
3. Ensure every month in the 12-month window has a value (even if it's 0, for empty months).
4. Format the output to match your desired "YYYY年MM月: count" structure.

Here's the code you can use (either in your model or a controller):

use Carbon\Carbon;
use App\Models\LogActivity; // Adjust the namespace to match your model's location

public function getMonthlyLoginActivity()
{
    // 1. Fetch grouped activity data for the past 12 months
    $activityGroups = LogActivity::whereNotIn('userId', [1])
        ->where('created_at', '>=', Carbon::now()->subMonths(12))
        ->selectRaw('YEAR(created_at) as year, MONTH(created_at) as month, COUNT(*) as count')
        ->groupBy('year', 'month')
        ->get()
        ->keyBy(function($item) {
            // Create a unique key for each year-month pair (e.g., "2023-11")
            return "{$item->year}-" . str_pad($item->month, 2, '0', STR_PAD_LEFT);
        });

    // 2. Generate the full 12-month list, filling in 0 for empty months
    $monthlyData = [];
    for ($i = 11; $i >= 0; $i--) {
        $currentDate = Carbon::now()->subMonths($i);
        $yearMonthKey = $currentDate->format('Y-m');
        $formattedMonth = $currentDate->format('Y年m月');
        
        // Use the count from the grouped data, or 0 if no activity exists
        $monthlyData[$formattedMonth] = $activityGroups->has($yearMonthKey) 
            ? $activityGroups[$yearMonthKey]->count 
            : 0;
    }

    // 3. Return the formatted JSON response
    return response()->json($monthlyData);
}

How This Works: #

• subMonths(12): Ensures we only pull data from the last 12 months, avoiding negative month values entirely.
• selectRaw + groupBy: Extracts the year and month from created_at, then counts how many activities happened in each year-month pair.
• Keyed Collection: Makes it easy to check if a specific month has activity data.
• Loop Through Months: Generates every month in the 12-month window, filling in 0 for months with no activity (critical for your bar chart to display continuous data).

Example Output #

You'll get a JSON response like this, perfect for your bar chart:

{
    "2023年11月": 300,
    "2023年12月": 800,
    "2024年01月": 100,
    ...
    "2024年10月": 450
}

Quick Notes #

• Make sure your created_at column is a datetime or timestamp type in your database.
• If you need calendar years (January-December) instead of the last 12 months, adjust the loop to start from January of the current year instead of subMonths($i).
• Using Laravel's response()->json() is better than json_encode() because it sets the correct HTTP headers automatically.

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