Laravel中如何对已过滤关联记录用withSum？避免代码重复

我的$user模型拥有名为earnings的关联关系，对应用户的收入交易记录。需要根据部分属性过滤earnings记录，但使用withSum时会统计所有关联记录，因此我不得不在获取earnings列表和计算earnings_sum_amount时重复编写相同的过滤子查询，代码冗余问题明显。

现有代码如下：

$users->with([
            'earnings' => function($query) use ($dateSpanArray, $brandModel) {

                /* Subquery is executing when one of 2 parameters are true */
                $query->when(($dateSpanArray || $brandModel) , function($query) use ($dateSpanArray, $brandModel) {

                    if($brandModel)
                    {
                        $query->whereIn('sender_id', $brandModel->wallets->pluck('id')->toArray());
                    }
                    if($dateSpanArray)
                    {
                        $query->whereBetween('created_at', [$dateSpanArray['from'],$dateSpanArray['to']]);
                    }
                })
                ->latest();
            }]
        )

->withSum([
            'earnings' => function($query) use ($dateSpanArray, $brandModel) {

                $query->when(($dateSpanArray || $brandModel) , function($query) use ($dateSpanArray, $brandModel) {

                    if($brandModel)
                    {
                        $query->whereIn('sender_id', $brandModel->wallets->pluck('id')->toArray());
                    }
                    if($dateSpanArray)
                    {
                        $query->whereBetween('created_at', [$dateSpanArray['from'],$dateSpanArray['to']]);
                    }
                })->select(DB::raw('COALESCE(SUM(amount), 0)'));
            }
        ], 'amount')
->get();

想请教是否存在更优写法，可避免此类代码重复？

方法1：提取过滤逻辑为可复用闭包 #

把重复的过滤逻辑抽成独立闭包，在with和withSum中直接复用，减少代码复制：

// 定义可复用的过滤闭包
$earningsFilter = function($query) use ($dateSpanArray, $brandModel) {
    $query->when(($dateSpanArray || $brandModel), function($query) use ($dateSpanArray, $brandModel) {
        if($brandModel) {
            $query->whereIn('sender_id', $brandModel->wallets->pluck('id')->toArray());
        }
        if($dateSpanArray) {
            $query->whereBetween('created_at', [$dateSpanArray['from'], $dateSpanArray['to']]);
        }
    });
};

// 复用闭包执行查询
$users->with([
        'earnings' => function($query) use ($earningsFilter) {
            $earningsFilter($query);
            $query->latest();
        }
    ])
    ->withSum([
        'earnings' => function($query) use ($earningsFilter) {
            $earningsFilter($query);
            $query->select(DB::raw('COALESCE(SUM(amount), 0)'));
        }
    ], 'amount')
    ->get();

方法2：在Earnings模型定义本地作用域 #

如果该过滤逻辑会在多个场景使用，推荐在Earnings模型中定义本地作用域，实现全局复用：

在Earnings.php模型中添加作用域方法：

public function scopeFilterByBrandAndDate($query, $brandModel = null, $dateSpanArray = null)
{
    return $query->when(($dateSpanArray || $brandModel), function($query) use ($dateSpanArray, $brandModel) {
        if($brandModel) {
            $query->whereIn('sender_id', $brandModel->wallets->pluck('id')->toArray());
        }
        if($dateSpanArray) {
            $query->whereBetween('created_at', [$dateSpanArray['from'], $dateSpanArray['to']]);
        }
    });
}

之后查询代码可简化为：

$users->with([
        'earnings' => function($query) use ($dateSpanArray, $brandModel) {
            $query->filterByBrandAndDate($brandModel, $dateSpanArray)
                  ->latest();
        }
    ])
    ->withSum([
        'earnings' => function($query) use ($dateSpanArray, $brandModel) {
            $query->filterByBrandAndDate($brandModel, $dateSpanArray)
                  ->select(DB::raw('COALESCE(SUM(amount), 0)'));
        }
    ], 'amount')
    ->get();

方法3：自定义带过滤的关联（进阶） #

若过滤逻辑和用户关联强绑定，可在User模型中定义一个带参数的关联方法：

在User.php模型中添加：

public function filteredEarnings($brandModel = null, $dateSpanArray = null)
{
    return $this->earnings()
                ->when(($dateSpanArray || $brandModel), function($query) use ($dateSpanArray, $brandModel) {
                    if($brandModel) {
                        $query->whereIn('sender_id', $brandModel->wallets->pluck('id')->toArray());
                    }
                    if($dateSpanArray) {
                        $query->whereBetween('created_at', [$dateSpanArray['from'], $dateSpanArray['to']]);
                    }
                });
}

查询时直接使用这个自定义关联：

$users->with([
        'filteredEarnings' => function($query) {
            $query->latest();
        }
    ])
    ->withSum([
        'filteredEarnings' => function($query) {
            $query->select(DB::raw('COALESCE(SUM(amount), 0)'));
        }
    ], 'amount')
    ->get();

注：此方法需确保调用关联时参数已正确传入，适合固定场景复用。

内容的提问来源于stack exchange，提问作者Domagoj Samardžić