Laravel中使用Yajra Datatables结合Eloquent实现子查询与筛选

一、预定义模型关联 #

先在对应模型中配置关联关系，简化查询调用：

• Transaction 模型（交易表）

// app/Models/Transaction.php
// 关联下单用户
public function user()
{
    return $this->belongsTo(User::class);
}
// 关联商品分类
public function productCategory()
{
    return $this->belongsTo(ProductCategory::class, 'product_category_id');
}

• Bonus 模型（奖金规则表）

// app/Models/Bonus.php
// 关联对应商品分类
public function productCategory()
{
    return $this->belongsTo(ProductCategory::class, 'product_category_id');
}

二、原生SQL转Eloquent查询构造 #

通过fromSub方法嵌套子查询，完全对齐原生SQL的执行逻辑，同时保留ORM的链式调用能力：

// 内层子查询tbl1：筛选交易状态为已完成的记录
$completedTransactionQuery = Transaction::select('no_invoice', 'product_category_id', 'nominal_transaksi', 'user_id', 'status_transaksi_id')
    ->where('status_transaksi_id', 1);

// 中间层子查询tbl2：按用户、商品分类维度聚合交易笔数、交易总金额
$userCategoryTransactionQuery = DB::query()->fromSub($completedTransactionQuery, 'tbl1')
    ->selectRaw('tbl1.*, u.name, pc.nama_kategori, COUNT(tbl1.nominal_transaksi) as jumlah_transaksi, SUM(tbl1.nominal_transaksi) as total_nominal_transaksi')
    ->join('users as u', 'u.id', '=', 'tbl1.user_id')
    ->join('product_categories as pc', 'pc.id', '=', 'tbl1.product_category_id')
    ->groupBy('u.id', 'pc.id');

// 外层主查询：关联奖金规则表，按规则计算每个用户的对应类型奖金
$bonusSummaryQuery = DB::query()->fromSub($userCategoryTransactionQuery, 'tbl2')
    ->selectRaw("
        tbl2.name,
        b.nama_bonus,
        SUM(
            CASE
                WHEN b.nama_bonus REGEXP 'saldo' THEN tbl2.jumlah_transaksi * b.nominal_bonus
                WHEN b.nama_bonus REGEXP 'bintang' AND tbl2.nama_kategori REGEXP 'transfer' THEN FLOOR(tbl2.total_nominal_transaksi / b.keterangan_bonus)
                ELSE FLOOR(tbl2.total_nominal_transaksi / b.keterangan_bonus)
            END
        ) as bonus_member
    ")
    ->join('bonus as b', 'b.product_category_id', '=', 'tbl2.product_category_id')
    ->groupBy('b.nama_bonus', 'tbl2.name')
    ->orderBy('tbl2.name');

执行上述查询得到的结果和你在phpMyAdmin中运行原生SQL的结果完全一致。

三、集成Yajra Datatables实现服务端渲染与筛选 #

1. 路由定义 #

// routes/web.php
Route::get('/bonus/summary', [BonusSummaryController::class, 'index'])->name('bonus.summary');
Route::get('/bonus/summary/data', [BonusSummaryController::class, 'getData'])->name('bonus.summary.data');

2. 控制器方法实现 #

// app/Http/Controllers/BonusSummaryController.php
<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Transaction;
use App\Models\Bonus;
use DB;

class BonusSummaryController extends Controller
{
    // 渲染统计页面
    public function index()
    {
        $bonusTypes = Bonus::distinct()->pluck('nama_bonus');
        return view('bonus.summary', compact('bonusTypes'));
    }

    // 处理Datatables数据请求
    public function getData(Request $request)
    {
        // 内层已完成交易查询，附加筛选条件
        $completedTransactionQuery = Transaction::select('no_invoice', 'product_category_id', 'nominal_transaksi', 'user_id', 'status_transaksi_id')
            ->where('status_transaksi_id', 1);

        // 用户名筛选
        if ($request->filled('name')) {
            $keyword = $request->input('name');
            $completedTransactionQuery->whereHas('user', function($query) use ($keyword) {
                $query->where('name', 'like', "%{$keyword}%");
            });
        }

        // 奖金类型筛选
        if ($request->filled('nama_bonus')) {
            $bonusType = $request->input('nama_bonus');
            $completedTransactionQuery->whereHas('productCategory.bonus', function($query) use ($bonusType) {
                $query->where('nama_bonus', $bonusType);
            });
        }

        // 复用之前的聚合、奖金计算逻辑
        $userCategoryTransactionQuery = DB::query()->fromSub($completedTransactionQuery, 'tbl1')
            ->selectRaw('tbl1.*, u.name, pc.nama_kategori, COUNT(tbl1.nominal_transaksi) as jumlah_transaksi, SUM(tbl1.nominal_transaksi) as total_nominal_transaksi')
            ->join('users as u', 'u.id', '=', 'tbl1.user_id')
            ->join('product_categories as pc', 'pc.id', '=', 'tbl1.product_category_id')
            ->groupBy('u.id', 'pc.id');

        $bonusSummaryQuery = DB::query()->fromSub($userCategoryTransactionQuery, 'tbl2')
            ->selectRaw("
                tbl2.name,
                b.nama_bonus,
                SUM(
                    CASE
                        WHEN b.nama_bonus REGEXP 'saldo' THEN tbl2.jumlah_transaksi * b.nominal_bonus
                        WHEN b.nama_bonus REGEXP 'bintang' AND tbl2.nama_kategori REGEXP 'transfer' THEN FLOOR(tbl2.total_nominal_transaksi / b.keterangan_bonus)
                        ELSE FLOOR(tbl2.total_nominal_transaksi / b.keterangan_bonus)
                    END
                ) as bonus_member
            ")
            ->join('bonus as b', 'b.product_category_id', '=', 'tbl2.product_category_id')
            ->groupBy('b.nama_bonus', 'tbl2.name')
            ->orderBy('tbl2.name');

        return datatables()->query($bonusSummaryQuery)
            // 配置列筛选逻辑
            ->filterColumn('name', function($query, $keyword) {
                $query->where('tbl2.name', 'like', "%{$keyword}%");
            })
            ->filterColumn('nama_bonus', function($query, $keyword) {
                $query->where('b.nama_bonus', 'like', "%{$keyword}%");
            })
            ->filterColumn('bonus_member', function($query, $keyword) {
                $query->havingRaw("bonus_member = ?", [$keyword]);
            })
            ->make(true);
    }
}

3. 前端视图实现 #

<!-- resources/views/bonus/summary.blade.php -->
<div class="container mt-4">
    <div class="card">
        <div class="card-header">
            <h5>交易维度奖金汇总统计</h5>
            <div class="row mt-3">
                <div class="col-md-4">
                    <input type="text" id="filter-name" class="form-control" placeholder="输入用户名筛选">
                </div>
                <div class="col-md-4">
                    <select id="filter-bonus-type" class="form-control">
                        <option value="">全部奖金类型</option>
                        @foreach($bonusTypes as $type)
                        <option value="{{ $type }}">{{ $type }}</option>
                        @endforeach
                    </select>
                </div>
            </div>
        </div>
        <div class="card-body">
            <table class="table table-bordered" id="bonus-table">
                <thead>
                    <tr>
                        <th>用户名</th>
                        <th>奖金类型</th>
                        <th>应发奖金</th>
                    </tr>
                </thead>
            </table>
        </div>
    </div>
</div>

<!-- 请自行在项目中引入jQuery、DataTables对应的CSS、JS资源 -->
<script>
$(function() {
    let bonusTable = $('#bonus-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: {
            url: '{{ route('bonus.summary.data') }}',
            data: function(params) {
                params.name = $('#filter-name').val();
                params.nama_bonus = $('#filter-bonus-type').val();
            }
        },
        columns: [
            {data: 'name', name: 'name'},
            {data: 'nama_bonus', name: 'nama_bonus'},
            {data: 'bonus_member', name: 'bonus_member'}
        ]
    });

    // 筛选条件变更时重载表格
    $('#filter-name').on('keyup change', debounce(function() {
        bonusTable.draw();
    }, 300));
    $('#filter-bonus-type').on('change', function() {
        bonusTable.draw();
    });

    // 防抖函数，避免输入时频繁请求
    function debounce(func, wait) {
        let timeout;
        return function() {
            clearTimeout(timeout);
            timeout = setTimeout(() => func.apply(this, arguments), wait);
        }
    }
});
</script>

注意事项

• 所有原生SQL片段涉及外部用户输入的部分，必须使用参数绑定写法，禁止直接拼接变量到SQL语句中，避免SQL注入风险
• 交易表数据量较大时，建议为status_transaksi_id、user_id、product_category_id字段建立联合索引，可大幅提升聚合查询速度
• 若需要扩展更多筛选维度（如交易时间范围），只需在$completedTransactionQuery层追加对应条件即可，不会破坏外层奖金计算逻辑

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