DataTables警告：datatable-crud表无效JSON响应问题求助

问题背景 #

使用DataTables时触发错误：DataTables warning: table id=datatable-crud - Invalid JSON response，以下是相关代码片段，需排查解决：

日期搜索表单代码 #

@extends('layouts.app')
@section('content')

<div class="row">
    <div class="col-lg-12 margin-tb">
        <div class="pull-left">
            
        </div>
        <div class="pull-right m-1">
            <!-- <a class="btn btn-primary" href="{{ route('perluhantars.index') }}"> Back</a> -->
        </div>
    </div>
</div>

@if ($errors->any())
    <div class="alert alert-danger">
        <ul>
            @foreach ($errors->all() as $error)
                <li>{{ $error }}</li>
            @endforeach
        </ul>
    </div>
@endif

<form action="{{ route('telahdihantars.store') }}" method="POST">
    @csrf
    
    <div class="card card-primary m-1" >
        <div class="card-header">
            Surat Keluar Yang Telah Dihantar
        </div>
        <div class="row">
            
            
            <div class="col-xs-12 col-sm-12 col-md-12">
                <div class="form-group">
                    <strong>Dari:</strong>
                    <input type="text" name="dari"  id="dari" class="form-control" placeholder="Dari" value="">
                </div>
            </div>
           
            <div class="col-xs-12 col-sm-12 col-md-12">
                <div class="form-group">
                    <strong>Hingga:</strong>
                    <input type="text" name="hingga"  id="hingga" class="form-control" placeholder="Hingga" value="">
                </div>
            </div>
            <div class="col-xs-12 col-sm-12 col-md-12 text-center">
                    <button type="submit" class="btn btn-primary">Submit</button>
            </div>
        </div>
    </div>
</form>
@endsection

@section('third_party_scripts')
    <script type="text/javascript">
        $( function() {
            $( "#dari" ).datepicker({
                dateFormat: "yy-mm-dd",
                changeMonth: true,
                changeYear: true
            });

            $( "#hingga" ).datepicker({
                dateFormat: "yy-mm-dd",
                changeMonth: true,
                changeYear: true
            });
        });
    </script>
@endsection

Store方法代码 #

public function store(Request $request)
    {
        $request->validate([
            'dari' => 'required',
            'hingga' => 'required',
            
        ]);

        $dari = $request->dari;
        $hingga = $request->hingga;

        $documentouts =  DB::table('documentouts')
                        ->leftjoin('cawangans','documentouts.cawangan_id', '=', 'cawangans.id')
                        ->leftjoin('kategoris', 'documentouts.kategori_id', '=', 'kategoris.id')
                        ->leftjoin('peringkats', 'documentouts.peringkat_id', '=', 'peringkats.id')
                        ->leftjoin('users as a' , 'documentouts.daripada' , '=', 'a.id')
                        ->leftjoin('users as b', 'documentouts.penghantar_id', '=', 'b.id')
                        ->select('documentouts.id','cawangans.cawangan as organisasi','kategoris.kategori as kategori','peringkats.peringkat as peringkat','documentouts.kepada', 'a.name as daripada',  'documentouts.tarikh_surat', 'documentouts.rujuk', 'documentouts.tajuk','b.name as penghantar', 'documentouts.penerima')  
                        ->whereBetween('tarikh_surat', [ $hingga , $dari ])->get();
                         // ->where('tarikh_surat', '>' , $dari )->get();

        if(request()->ajax()) {
            return datatables()->of($documentouts)
                ->addColumn('action', 'telahdihantars.action')
                ->rawColumns(['action'])
                ->addIndexColumn()
                ->make(true);
        }

        return view('telahdihantars.index2');

    }

telahdihantars.index2视图代码 #

@extends('layouts.app2')
@section('content')

<div class="row">
    <div class="col-lg-12 margin-tb">
        <div class="pull-left">
            <h2>Surat Perlu Saya Hantar</h2>
        </div>
        <div class="pull-right m-1">
            <!-- <a class="btn btn-success" href="{{ route('documentouts.create') }}"> Tambah Rekod</a> -->
        </div>
    </div>
</div>
@if ($message = Session::get('success'))
<div class="alert alert-success">
    <p>{{ $message }}</p>
</div>
@endif
<div class="card-body">
    <table class="table table-bordered" id="datatable-crud">
        <thead>
        <tr>
        <th>Id</th>
        <th>Organisasi</th>
        <th>Kategori</th>
        <th>Peringkat</th>
        <th>Kepada</th>
        <th>Daripada</th>  
        <th>Tarikh Surat</th>
        <th>Rujuk</th>
        <th>Tajuk</th>
        <th>Penghantar Surat</th>
        <th>Penerima</th>
        <th>Action</th>
        </tr>
        </thead>
    </table>
</div>


@endsection

@section('third_party_scripts')
    <script type="text/javascript">
    $(document).ready( function () {
    $.ajaxSetup({
    headers: {
    'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
    }
    });
    $('#datatable-crud').DataTable({
    processing: true,
    serverSide: true,
    ajax: "{{ url('telahdihantars') }}",
    columns: [
    { data: 'id', name: 'id' },
    { data: 'organisasi', name: 'organisasi' },
    { data: 'kategori', name: 'kategori' },
    { data: 'peringkat', name: 'peringkat' },
    { data: 'kepada', name: 'kepada' },
    { data: 'daripada', name: 'daripada' },
    { data: 'tarikh_surat', name: 'tarikh_surat' },
    { data: 'rujuk', name: 'rujuk' },
    { data: 'tajuk', name: 'tajuk' },
    { data: 'penghantar', name: 'penghantar' },
    { data: 'penerima', name: 'penerima' },
    {data: 'action', name: 'action', orderable: false},
    ],
    order: [[0, 'desc']]
    });
    $('body').on('click', '.delete', function () {
    if (confirm("Padam rekod?") == true) {
    var id = $(this).data('id');
    // ajax
    $.ajax({
    type:"POST",
    url: "{{ url('delete-documentout') }}",
    data: { id: id},
    dataType: 'json',
    success: function(res){
    var oTable = $('#datatable-crud').dataTable();
    oTable.fnDraw(false);
    }
    });
    }
    });
    });
    </script>

@endsection

排查与解决步骤 #

1. 修正日期范围参数顺序 #

whereBetween方法要求参数顺序为**[起始日期, 结束日期]**，你当前写反了$hingga和$dari，会导致查询逻辑错误，甚至返回异常数据。

修改Store方法中的查询代码：

->whereBetween('tarikh_surat', [ $dari , $hingga ])->get();

2. 拆分数据查询与视图渲染逻辑 #

当前Store方法同时处理表单提交和DataTables的AJAX请求，导致非AJAX请求返回HTML视图，而DataTables请求该地址时会拿到HTML而非JSON，直接触发错误。

解决方案：

• 新增index方法专门处理DataTables的AJAX请求和视图渲染
• Store方法仅处理表单提交，将日期参数传递给index方法

修改Store方法：

public function store(Request $request)
{
    $request->validate([
        'dari' => 'required',
        'hingga' => 'required',
    ]);

    // 将日期参数通过URL传递到index页面
    return redirect()->route('telahdihantars.index', [
        'dari' => $request->dari,
        'hingga' => $request->hingga
    ]);
}

新增Index方法：

public function index(Request $request)
{
    if(request()->ajax()) {
        // 从URL参数获取日期范围
        $dari = $request->query('dari');
        $hingga = $request->query('hingga');

        $documentouts =  DB::table('documentouts')
                        ->leftjoin('cawangans','documentouts.cawangan_id', '=', 'cawangans.id')
                        ->leftjoin('kategoris', 'documentouts.kategori_id', '=', 'kategoris.id')
                        ->leftjoin('peringkats', 'documentouts.peringkat_id', '=', 'peringkats.id')
                        ->leftjoin('users as a' , 'documentouts.daripada' , '=', 'a.id')
                        ->leftjoin('users as b', 'documentouts.penghantar_id', '=', 'b.id')
                        ->select('documentouts.id','cawangans.cawangan as organisasi','kategoris.kategori as kategori','peringkats.peringkat as peringkat','documentouts.kepada', 'a.name as daripada',  'documentouts.tarikh_surat', 'documentouts.rujuk', 'documentouts.tajuk','b.name as penghantar', 'documentouts.penerima')  
                        ->whereBetween('tarikh_surat', [ $dari, $hingga ])->get();

        return datatables()->of($documentouts)
            ->addColumn('action', 'telahdihantars.action')
            ->rawColumns(['action'])
            ->addIndexColumn()
            ->make(true);
    }

    return view('telahdihantars.index2');
}

修正路由配置：

确保路由指向正确的方法：

Route::get('telahdihantars', [TelahdihantarController::class, 'index'])->name('telahdihantars.index');
Route::post('telahdihantars', [TelahdihantarController::class, 'store'])->name('telahdihantars.store');

3. 验证JSON返回内容 #

如果修改后仍报错，打开浏览器开发者工具（F12），切换到Network标签，查看DataTables请求的Response内容：

• 如果返回HTML（比如错误页面），查看Laravel日志（storage/logs/laravel.log）定位具体异常
• 如果返回空JSON，检查日期参数是否正确传递、数据库查询是否有结果

内容的提问来源于stack exchange，提问作者Eidul Ameen Sahul Hamid