Laravel Yajra Datatable服务端分页与列搜索计数问题求助

Hey there! Let's get your Yajra Datatable column search and pagination working correctly with your 100k+ records. You've already laid good groundwork, but we need to adjust how we handle column-specific filters, pagination offsets, and query reusability to get accurate counts and proper results.

1. Fix Pagination Logic (Don’t Hardcode take(10)) #

Right now you’re using take(10) which only returns the first 10 rows every time. Datatables sends start (offset) and length (number of rows per page) parameters in the request—use these to paginate properly:

$start = $request->input('start');
$length = $request->input('length');

Then replace take(10) with skip($start)->take($length) later in your data query.

2. Reuse Your Base Query to Avoid Duplication #

Instead of writing the same join three times, create a base query builder instance. You can clone it for counts and data fetching to keep your code DRY and consistent:

// Base query with joins and selects
$baseQuery = \DB::table('items')
    ->join('brands', 'items.brand', '=', 'brands.code')
    ->select(
        'items.id as items_id',
        'items.code as items_code',
        'items.description as items_description',
        'brands.description as brands_description'
    );

3. Handle Column-Specific Search Filters #

The $columns array contains search values for each column. We need to loop through it, check if a column has a search value, and add the corresponding WHERE clause to our query. First, map your Datatable column names to actual database columns (since you’re using aliases like brands_description):

// Map Datatable column names to database columns
$columnMap = [
    'items_id' => 'items.id',
    'brands_description' => 'brands.description',
    'items_code' => 'items.code',
    'items_description' => 'items.description'
];

Then build the filter query:

// Clone base query for filtered counts and data
$filteredQuery = clone $baseQuery;

// Apply global search first (wrap in closure to keep OR logic isolated)
$search = $request->input('search.value');
if (!empty($search)) {
    $filteredQuery->where(function($q) use ($search) {
        $q->where('brands.description', 'LIKE', "%{$search}%")
          ->orWhere('items.description', 'LIKE', "%{$search}%")
          ->orWhere('items.code', 'LIKE', "%{$search}%");
    });
}

// Apply column-specific searches (uses AND logic for multiple filters)
$columns = $request->get('columns');
foreach ($columns as $column) {
    $columnName = $column['name'];
    $searchValue = $column['search']['value'];
    
    // Skip columns with no search value or excluded from search
    if (empty($searchValue) || !isset($columnMap[$columnName])) {
        continue;
    }
    
    $filteredQuery->where($columnMap[$columnName], 'LIKE', "%{$searchValue}%");
}

4. Calculate Accurate Counts #

Now use the base and filtered queries to get your counts:

$countTotal = $baseQuery->count();
$countFiltered = $filteredQuery->count();

5. Fetch Paginated Data #

Use the filtered query to get the paginated results:

$items = $filteredQuery->skip($start)->take($length)->get();

Full Updated Controller Method #

public function getRecords(Request $request)
{
    $start = $request->input('start');
    $length = $request->input('length');
    $search = $request->input('search.value');
    $columns = $request->get('columns');

    // Map Datatable column names to actual database columns
    $columnMap = [
        'items_id' => 'items.id',
        'brands_description' => 'brands.description',
        'items_code' => 'items.code',
        'items_description' => 'items.description'
    ];

    // Base query with joins and selects
    $baseQuery = \DB::table('items')
        ->join('brands', 'items.brand', '=', 'brands.code')
        ->select(
            'items.id as items_id',
            'items.code as items_code',
            'items.description as items_description',
            'brands.description as brands_description'
        );

    // Clone base query for filtering
    $filteredQuery = clone $baseQuery;

    // Apply global search
    if (!empty($search)) {
        $filteredQuery->where(function($q) use ($search) {
            $q->where('brands.description', 'LIKE', "%{$search}%")
              ->orWhere('items.description', 'LIKE', "%{$search}%")
              ->orWhere('items.code', 'LIKE', "%{$search}%");
        });
    }

    // Apply column-specific searches
    foreach ($columns as $column) {
        $columnName = $column['name'];
        $searchValue = $column['search']['value'];
        
        if (empty($searchValue) || !isset($columnMap[$columnName])) {
            continue;
        }

        $filteredQuery->where($columnMap[$columnName], 'LIKE', "%{$searchValue}%");
    }

    // Calculate counts
    $countTotal = $baseQuery->count();
    $countFiltered = $filteredQuery->count();

    // Fetch paginated data
    $items = $filteredQuery->skip($start)->take($length)->get();

    return Datatables::of($items)
        ->with([
            "recordsTotal" => $countTotal,
            "recordsFiltered" => $countFiltered,
        ])
        ->rawColumns(['items_id','brands_description'])
        ->make(true);
}

Quick Fix for Your Frontend JS #

Your current column search logic has a bug: columnClass.indexOf('no_search') != false will incorrectly add search boxes to columns that do have the no_search class. Change it to check if the class is not present:

initComplete: function () {
    this.api().columns().every(function () {
        var column = this;
        var columnClass = column.header().className;
        // Only add search input to columns without "no_search" class
        if (columnClass.indexOf('no_search') === -1) {
            var input = document.createElement("input");
            $(input).addClass('form-control');
            $(input).appendTo($(column.header()).empty())
                .on('change', function () {
                    column.search($(this).val(), false, false, true).draw();
                });
        }
    });
}

Key Notes #

• Query Cloning: Using clone $baseQuery ensures we don’t modify the original base query when adding filters, so our total count remains accurate.
• Column Mapping: The $columnMap is crucial because your Datatable uses aliased column names that don’t match the actual database column names.
• Global vs Column Search: We wrap the global search in a closure to ensure it uses OR logic correctly, while column searches use AND logic (so multiple column filters are applied together).

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