Laravel带参数预加载：如何按$date获取含余额的科目表并解决N+1查询问题

Hey there! Let's tackle that annoying N+1 query problem you're facing when pulling parent COA records along with their latest Ledger balances as of a specific date. I'll break down what's causing it and walk you through a few clean, efficient solutions.

What's Causing the N+1 Problem? #

Right now, your code works like this:

1. You fetch all parent COAs in 1 query
2. Then you loop through each COA and call $coa->balance($date), which runs a separate query for every single COA

If you have 50 parent COAs, that's 1 + 50 = 51 total queries—way too slow as your dataset grows. Let's fix this with smarter query patterns.

Solution 1: Subquery to Fetch Balances in One Go #

The cleanest approach is to embed the balance lookup directly into your initial COA query using a subquery. This way, you only run 2 total queries (one for COAs, one subquery for all balances).

First, simplify your date handling with Carbon's built-in endOfDay() method (it does the same thing as adding 23:59:59, but is far more readable):

public function get(Request $request){
    $date = $request->date;
    $carbonDate = Carbon::parse($date)->endOfDay();

    $coas = COA::where('parent_id', null)
        ->select('*')
        // Add the balance as a subquery
        ->addSelect([
            'balance' => Ledger::select('balance')
                ->whereColumn('c_o_a_id', 'coas.id')
                ->where('created_at', '<=', $carbonDate)
                ->orderBy('created_at', 'desc')
                ->orderBy('id', 'desc')
                ->limit(1)
        ])
        ->get()
        // Fallback to 0 if no balance exists for a COA
        ->map(function ($coa) {
            $coa->balance = $coa->balance ?? 0;
            return $coa;
        });

    return view('website.accounts.Trial.show', compact('coas', 'date'));
}

Why this works:

• The subquery runs once for all COAs, pulling the latest balance (sorted by created_at then id) for each matching COA ID
• No more looped queries—just two efficient database calls that scale well with larger datasets

Solution 2: Eager Loading with a Conditional Relationship #

If you prefer using Laravel's relationship features, you can define a "latest ledger" relationship on your COA model, then eager load it with your date filter.

First, add this relationship to your COA model:

// app/Models/COA.php
public function latestLedger()
{
    return $this->hasOne(Ledger::class, 'c_o_a_id')
        ->latest('created_at')
        ->latest('id');
}

Then update your controller to eager load this relationship with the date constraint:

public function get(Request $request){
    $date = $request->date;
    $carbonDate = Carbon::parse($date)->endOfDay();

    $coas = COA::where('parent_id', null)
        ->with(['latestLedger' => function ($query) use ($carbonDate) {
            $query->where('created_at', '<=', $carbonDate);
        }])
        ->get()
        ->map(function ($coa) {
            $coa->balance = $coa->latestLedger?->balance ?? 0;
            return $coa;
        });

    return view('website.accounts.Trial.show', compact('coas', 'date'));
}

Why this works:

• Laravel automatically runs a single WHERE IN query to fetch all relevant ledgers for your COAs
• The conditional closure adds your date filter directly to the eager load query
• This keeps your code aligned with Laravel's ORM patterns while eliminating the N+1 issue

Solution 3: Batch Fetch Ledgers and Map Manually #

If you want more explicit control over the process, you can fetch all COAs first, pull all relevant ledgers in one query, then map the balances to their COAs:

public function get(Request $request){
    $date = $request->date;
    $carbonDate = Carbon::parse($date)->endOfDay();

    // Step 1: Get all parent COAs
    $coas = COA::where('parent_id', null)->get();
    $coaIds = $coas->pluck('id')->toArray();

    // Step 2: Fetch all latest ledgers for these COAs in one query
    $latestLedgers = Ledger::whereIn('c_o_a_id', $coaIds)
        ->where('created_at', '<=', $carbonDate)
        ->orderBy('created_at', 'desc')
        ->orderBy('id', 'desc')
        ->get()
        ->unique('c_o_a_id'); // Keep only the latest entry per COA

    // Step 3: Map balances to each COA
    $coas = $coas->map(function ($coa) use ($latestLedgers) {
        $matchingLedger = $latestLedgers->firstWhere('c_o_a_id', $coa->id);
        $coa->balance = $matchingLedger?->balance ?? 0;
        return $coa;
    });

    return view('website.accounts.Trial.show', compact('coas', 'date'));
}

Why this works:

• You have full visibility into each step of the process, which can be helpful for debugging
• Still only two total queries, completely avoiding the N+1 trap

Bonus: Clean Up the Old Balance Method #

Once you implement one of these solutions, you can remove or deprecate the old balance() method on your COA model—it was the source of the N+1 problem, and you won't need it anymore!

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