Laravel Eloquent关联中计算paidbook_count与settlement_count差值

你可以通过以下几种方式实现paidbook_count减去settlement_count的差值计算：

方法一：查询时直接计算差值（推荐） #

在查询构造器中使用selectRaw让数据库直接完成差值计算，性能更优：

return User::withCount([
        'settlement' => function($query) use($start_date, $end_date){
            $query->whereBetween('created_at', [$start_date, $end_date]);
        },
        'paidbook' => function($query) use($start_date, $end_date){
            $query->whereBetween('created_at', [$start_date, $end_date]);
        }
    ])
    ->selectRaw('*, paidbook_count - settlement_count as count_difference')
    ->get();

返回结果会新增count_difference字段，示例输出：

[
  {
    "id": 1,
    "firstname": "ooko",
    "settlement_count": 1,
    "paidbook_count": 2,
    "count_difference": 1
  }
]

方法二：添加模型访问器 #

给User模型添加访问器，让模型实例自动计算差值：

// 在User模型中添加
public function getCountDifferenceAttribute()
{
    return ($this->paidbook_count ?? 0) - ($this->settlement_count ?? 0);
}

如果希望返回JSON时自动包含该字段，还需要在模型中配置$appends：

// User模型中添加
protected $appends = ['count_difference'];

之后查询返回的结果会自动带上count_difference字段。

方法三：集合层面处理 #

如果已经拿到查询结果集合，可通过map遍历添加差值字段：

$users = User::withCount([
        'settlement' => function($query) use($start_date, $end_date){
            $query->whereBetween('created_at', [$start_date, $end_date]);
        },
        'paidbook' => function($query) use($start_date, $end_date){
            $query->whereBetween('created_at', [$start_date, $end_date]);
        }
    ])->get();

$usersWithDifference = $users->map(function($user) {
    $user->count_difference = ($user->paidbook_count ?? 0) - ($user->settlement_count ?? 0);
    return $user;
});

return $usersWithDifference;

这种方式适合需要对集合做额外自定义处理的场景。

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