如何将指定关联聚合SQL语句转换为Laravel Eloquent查询？

算力阿峰

2026-5-26

转换后的Laravel Eloquent查询方案

嘿，我来帮你把这条SQL转换成Laravel风格的Eloquent查询，两种写法供你选择：

写法一：使用模型关联

假设你已经为TAX_CHECKPOINT表创建了对应的模型（比如命名为TaxCheckpoint），可以用模型链式调用完成查询：

use App\Models\Payment;
use App\Models\TaxCheckpoint;
use Illuminate\Support\Facades\DB;

$results = Payment::select(
        'tax_checkpoint.cuocode',
        'tax_checkpoint.name',
        DB::raw('COUNT(*) as total_payment'),
        DB::raw('SUM(payment.amount) as total_amount')
    )
    ->join('tax_checkpoint', function ($join) {
        // 用DB::raw包裹原生正则函数，确保数据库能识别执行
        $join->on('tax_checkpoint.cuocode', '=', DB::raw("REGEXP_SUBSTR(payment.invoiceid, 'R....')"));
    })
    ->where('payment.deleted', 0)
    // 用Laravel自带的whereDate替代原生日期格式化，更简洁
    ->whereDate('payment.txtime', '2018-04-24')
    ->groupBy('tax_checkpoint.cuocode', 'tax_checkpoint.name')
    ->orderBy('total_amount', 'desc')
    ->get();

写法二：使用DB门面（和原SQL表别名完全对应）

如果还没创建TaxCheckpoint模型，或者更习惯原SQL的表别名写法，可以直接用DB门面：

use Illuminate\Support\Facades\DB;

$results = DB::table('tax_payment as p')
    ->select(
        'c.cuocode',
        'c.name',
        DB::raw('COUNT(*) as total_payment'),
        DB::raw('SUM(p.amount) as total_amount')
    )
    ->join('tax_checkpoint as c', 'c.cuocode', '=', DB::raw("REGEXP_SUBSTR(p.invoiceid, 'R....')"))
    ->where('p.deleted', 0)
    ->whereDate('p.txtime', '2018-04-24')
    ->groupBy('c.cuocode', 'c.name')
    ->orderBy('total_amount', 'desc')
    ->get();