Laravel查询变量复用失败：变量仅能生效一次

将部分查询逻辑存入变量$base_query：

$base_query = DB::connection('mysql_live')->table('users_transactions_all')
->whereDate('users_transactions_all.date_added', '>=', $start_date)->whereDate('users_transactions_all.date_added', '<=', $end_date)
->join('users', 'users.id', '=', 'users_transactions_all.user_id')
->when($data->regcountry != "all", function ($query) use ($data) {
    return $query->whereIn('users.regcountry', $data->regcountry);
});

首次使用该变量执行查询可正常获取结果：

$payments = $base_query->select('users_transactions_all.date_elem AS date',
        DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"),
        DB::raw("SUM(users_transactions_all.partner_payout_eur) as eurPayoutPartner"),
        DB::raw("COUNT(users_transactions_all.id) as count"))
        ->groupBy('date')->get();

但复用该变量执行第二次查询时出现错误：

$packages = $base_query->select(DB::raw("COUNT(users_transactions_all.package_name) as count"),
DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"), 'users_transactions_all.package_name')
->groupBy('users_transactions_all.package_name')->get();

错误信息：

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'date' in 'group statement'

替换第二次查询中的$base_query为原查询内容则可正常执行，变量首次使用后无法复用，请问原因及解决方法？

Laravel的查询构建器是可变对象，调用select()、groupBy()这类方法时，会直接修改$base_query实例本身的内部状态，而非返回全新实例。

第一次执行查询时，已经给$base_query添加了select('users_transactions_all.date_elem AS date')和groupBy('date')的条件；第二次复用变量时，这些条件依然存在，导致最终生成的SQL同时包含两次查询的groupBy条件（date和users_transactions_all.package_name），但第二次查询的select里并没有date字段，因此触发"未知列'date'"的错误。

有两种常用方法实现查询逻辑的复用：

方法1：克隆查询构建器实例 #

每次使用基础查询前，用clone关键字复制全新的查询实例，避免修改原变量状态：

// 第一次查询：克隆原base_query，基于新实例构建查询
$payments = (clone $base_query)->select('users_transactions_all.date_elem AS date',
        DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"),
        DB::raw("SUM(users_transactions_all.partner_payout_eur) as eurPayoutPartner"),
        DB::raw("COUNT(users_transactions_all.id) as count"))
        ->groupBy('date')->get();

// 第二次查询：再次克隆原base_query，构建独立的查询
$packages = (clone $base_query)->select(DB::raw("COUNT(users_transactions_all.package_name) as count"),
        DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"), 
        'users_transactions_all.package_name')
->groupBy('users_transactions_all.package_name')->get();

方法2：将基础查询封装为闭包 #

把基础查询逻辑放进闭包，每次调用闭包都会生成全新的查询构建器实例，确保每次查询独立：

// 封装基础查询为闭包，捕获所需变量
$base_query = function() use ($start_date, $end_date, $data) {
    return DB::connection('mysql_live')->table('users_transactions_all')
        ->whereDate('users_transactions_all.date_added', '>=', $start_date)
        ->whereDate('users_transactions_all.date_added', '<=', $end_date)
        ->join('users', 'users.id', '=', 'users_transactions_all.user_id')
        ->when($data->regcountry != "all", function ($query) use ($data) {
            return $query->whereIn('users.regcountry', $data->regcountry);
        });
};

// 第一次查询：调用闭包获取新实例
$payments = $base_query()->select('users_transactions_all.date_elem AS date',
        DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"),
        DB::raw("SUM(users_transactions_all.partner_payout_eur) as eurPayoutPartner"),
        DB::raw("COUNT(users_transactions_all.id) as count"))
        ->groupBy('date')->get();

// 第二次查询：再次调用闭包获取新实例
$packages = $base_query()->select(DB::raw("COUNT(users_transactions_all.package_name) as count"),
        DB::raw("SUM(users_transactions_all.eur_amount) as eurAmount"), 
        'users_transactions_all.package_name')
->groupBy('users_transactions_all.package_name')->get();

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