Laravel中如何实现DataTable筛选后统计值动态更新

实现方案 #

一、后端Controller调整 #

你当前的统计值仅在页面首次加载时计算，筛选请求走的是ajax分支，未返回最新统计数据，因此需要调整ajax逻辑，将筛选后的统计值附加到接口返回中：

public function index(Request $request,ExportDataTable $dataTable)
{
    if(request()->ajax())
    {
        // 抽离公共逻辑：获取当前用户权限条件
        $loged_user =Auth::user();
        $user= DB::table('users')
            ->selectRaw('users.*, REPLACE(Org2, "Admin", "") AS ROrg2, REPLACE(Org1, "Admin", "") AS ROrg1, REPLACE(Org3, "Admin" , "") AS ROrg3')
            ->where('email',$loged_user->email)
            ->first();
        // 构建基础查询
        $query= DB::table('predictions')
                ->where('CurrOrg1','like','%'.$user->ROrg1.'%')
                ->where('CurrOrg2','like','%'.$user->ROrg2.'%')
                ->where('CurrOrg3','like','%'.$user->ROrg3.'%')
                ->select('predictions.id','predictions.EmployeeNumber','predictions.Role','predictions.Region','predictions.CurrOrg1','predictions.CurrOrg2','predictions.RiskZone','predictions.Probablity','predictions.Rating','predictions.Feedback','predictions.Gender','predictions.CurrOrg3','predictions.CurrOrg4','predictions.CurrOrg5','predictions.ProfessionalClassification','predictions.Local_Expat','predictions.EmployeeRoleSeniority','predictions.FeedRisklevel','predictions.Feedinfluencers','predictions.Action','predictions.Fname','predictions.Lname','predictions.Avgweekhr');

        // 叠加筛选条件
        if(!empty($request->CurrOrg2)) {
            $query->whereIn('CurrOrg2',$request->CurrOrg2);
        }
        if(!empty($request->Region)) {
            $query->whereIn('Region',$request->Region);
        }
        if(!empty($request->ProfessionalClassification)) {
            $query->whereIn('ProfessionalClassification',$request->ProfessionalClassification);
        }
        if(!empty($request->CurrOrg1)) {
            $query->whereIn('CurrOrg1',$request->CurrOrg1);
        }
        if(!empty($request->CurrOrg3)) {
            $query->whereIn('CurrOrg3',$request->CurrOrg3);
        }
        if(!empty($request->CurrOrg4)) {
            $query->whereIn('CurrOrg4',$request->CurrOrg4);
        }
        if(!empty($request->CurrOrg5)) {
            $query->whereIn('CurrOrg5',$request->CurrOrg5);
        }
        if(!empty($request->Role)) {
            $query->whereIn('Role',$request->Role);
        }
        if(!empty($request->BusinessUnit)) {
            $query->whereIn('BusinessUnit',$request->BusinessUnit);
        }
        if(!empty($request->Gender)) {
            $query->whereIn('Gender',$request->Gender);
        }

        // 计算筛选后的统计值，注意用clone避免污染原查询
        $Records = $query->count();
        $Highrisk = (clone $query)->where('RiskZone','High Risk')->count();
        $Lowrisk = (clone $query)->where('RiskZone','Low Risk')->count();
        $Action = (clone $query)->where('Action','!=','No')->count();

        $data = $query->get();    
       
        // 返回数据时附加统计值
        return datatables()->of($data)
        ->addColumn('Feedback', function($data)
        {   
            if($data->Action == 'No')
            {
            return "<a href='#' style='background-color:#CA0088;color:#fff' class='btn btn-sm Feedback' id='".$data->id."'>Feedback</a>";
            }
            else
               {
                 return "<a href='#' style='background-color:#00A300;color:#fff' class='btn btn-sm Feedback' id='".$data->id."'>Feedback</a>";
               } 
        })
        ->escapeColumns([])
        ->with('stats', compact('Records', 'Highrisk', 'Lowrisk', 'Action'))
        ->make(true);
    }
    // 原有非ajax逻辑保持不变即可
    // ... 原有代码
}

二、视图层调整 #

给统计数值的标签添加唯一ID，方便后续JS动态更新：

<div class="container-fluid">
        <!-- Info boxes -->
        <div class="row">
          <div class="col-12 col-sm-6 col-md-3">
            <div class="info-box">
              <span class="info-box-icon bg-info elevation-1"><i class="fas fa-users"></i></span>

              <div class="info-box-content">
                <span class="info-box-text"># Employees</span>
                <span class="info-box-number" id="stats_records">
                 {{ $Records }}
                </span>
              </div>
              <!-- /.info-box-content -->
            </div>
            <!-- /.info-box -->
          </div>
          <!-- /.col -->
          <div class="col-12 col-sm-6 col-md-3">
            <div class="info-box mb-3">
              <span class="info-box-icon bg-danger elevation-1"><i class="fas fa-user"></i></span>

              <div class="info-box-content">
                <span class="info-box-text">High Risk Employees</span>
                <span class="info-box-number" id="stats_high_risk"> {{ $Highrisk }} </span>
              </div>
              <!-- /.info-box-content -->
            </div>
            <!-- /.info-box -->
          </div>
          <!-- 其余低风险、已处理统计项同理添加ID即可 -->

三、添加前端JS逻辑 #

监听DataTable的ajax加载完成事件，拿到接口返回的统计数据后更新页面数值：

// 把#prediction_table替换成你实际的DataTable容器ID
$('#prediction_table').on('xhr.dt', function (e, settings, json, xhr) {
    // 确认统计数据存在后更新页面
    if (json && json.stats) {
        $('#stats_records').text(json.stats.Records);
        $('#stats_high_risk').text(json.stats.Highrisk);
        // 其余低风险、已处理统计项同理更新
        // $('#stats_low_risk').text(json.stats.Lowrisk);
        // $('#stats_action').text(json.stats.Action);
    }
});

以上步骤完成后，每次触发筛选操作、DataTable重新加载数据时，统计区域的数值就会同步更新。

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