My DataTables (https://datatables.net) backend handler (for Laravel 8+) #laravel #datatables #php

handleDataTables.php

<?php

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Handle DataTable backend (for Laravel)
 *
 * @param string $table Name of the table.
 * @param array $searchableColumns Column(s) that you wish to be searchable.
 * @param array $joins Join specification(s).
 * @param Closure $qbManipulationCallback Manipulate query builder before pagination.
 * @param array $columns Select statement(s).
 * @param Closure $actionColumn Add action column.
 * @param array $manipulateRow Specification for creating or editing existing column(s).
 * 
 * @return \Illuminate\Http\JsonResponse
 */
function handleQueryBuilderDataList(
    $table,
    $searchableColumns = [],
    $joins = [],
    Closure $qbManipulationCallback = null,
    $columns = [],
    Closure $actionColumn = null,
    $manipulateRow = []
) {
    $isFiltered     = false;
    $isSearching    = false;
    $isManipulated  = false;

    $recordsTotal   = 0;
    $recordsFiltered   = 0;

    $handleJoinTree = function (Builder $qb) use ($table, $joins) {
        $result = [];

        $handleJoinTree2 = function ($path, $item) use (&$result, &$handleJoinTree2, $table, $qb) {
            if (!empty($path)) {
                $pathExploded = explode(";", $path);
                $source       = end($pathExploded);
            } else {
                $source     = $table;
            }

            $target         = $item['target'];
            $targetAlias    = $item['alias'] ?? "";
            $neededColumns  = $item['columns'] ?? [];
            $omittedColumns = $item['omit'] ?? [];
            $source_column  = $item['fk'];
            $target_column  = $item['pk'] ?? "id";
            $operator       = $item['operator'] ?? "=";
            $type           = $item['type'] ?? "inner";
            $where          = $item['where'] ?? "";
            $subJoins       = $item['joins'] ?? [];

            $targetFinal    = !empty($targetAlias) ? $targetAlias : $target;
            $targetTable    = !empty($targetAlias) ? "$target as $targetAlias" : $target;

            $column1        = "{$source}.{$source_column}";
            $column2        = "{$targetFinal}.{$target_column}";

            $resultName     = !empty($path) ? "$path;$targetFinal" : $targetFinal;


            $includedColumns = !empty($neededColumns) ? $neededColumns : Schema::getColumnListing($target);

            /**
             * What happens when user includes $neededColumns and $omittedColumns
             * at the same time? 
             * Well, we omit element(s) inside $neededColumns.
             */
            $includedColumns = array_diff($includedColumns, $omittedColumns);

            $targetTableColumns = !empty($includedColumns) ? $includedColumns : Schema::getColumnListing($target);

            foreach ($targetTableColumns as $targetColumnItem) {
                $result[] = "{$targetFinal}.{$targetColumnItem} as {$resultName};{$targetColumnItem}";
            }

            $qb->join($targetTable, $column1, $operator, $column2, $type, $where);

            if (!empty($subJoins)) {
                foreach ($subJoins as $joinItem) {
                    $handleJoinTree2($resultName, $joinItem);
                }
            }
        };

        foreach ($joins as $item) {
            $handleJoinTree2("", $item);
        }

        return $result;
    };

    $createNestedArray = function ($array, $separator) {
        $result = array();

        foreach ($array as $path => $value) {
            $temp = &$result;

            foreach (explode($separator, $path) as $key) {
                $temp = &$temp[$key];
            }
            $temp = $value;
        }

        return $result;
    };

    $orders = [];

    $limit = request()->get("length") ? (int) request()->get("length") : 10;
    $page = request()->start == 0 ? 1 : ceil((request()->start + 1) / $limit);
    $dtColumns = request()->post("columns");
    $dtOrder = request()->post("order", []);
    $filters = request()->post("filters", []);

    $searchTerm = request()->post("search") ? request()->post("search")['value'] : "";
    $searchTerm = preg_replace("/[^a-zA-Z0-9]+/", "", $searchTerm);

    $qb = DB::table($table);

    $selectColumns = array_map(
        fn ($value) => "{$table}.{$value}",
        (!empty($columns) ? $columns : ["*"])
    );

    $selectColumns = array_merge($selectColumns, $handleJoinTree($qb));

    $qb->select($selectColumns);
    $qb->addSelect(DB::raw("(select count(*) from {$table}) as _recordsTotal"));

    if (is_callable($qbManipulationCallback)) {
        $isManipulated = true;

        $qbManipulationCallback($qb);
    }

    foreach ($filters as $filterColumnName => $filterColumnValue) {
        if (!empty($filterColumnValue)) {
            $isFiltered = true;
            $qb->where($filterColumnName, $filterColumnValue);
        }
    }

    if (!empty($searchTerm)) {
        $isSearching = true;

        if (empty($searchableColumns)) {
            $searchableColumns = $selectColumns;
        }

        $qb->where(function ($qb) use ($searchableColumns, $searchTerm) {
            foreach ($searchableColumns as $column) {
                $columnExploded = explode(' as ', $column);
                $columnName     = $columnExploded[0];

                $qb->orWhereRaw("$columnName LIKE \"%$searchTerm%\"");
            }
        });
    }

    if ($isFiltered || $isSearching || $isManipulated) {
        $qbCountFiltered = (clone $qb);
        $qbCountFiltered->select(DB::raw("count(*)"));

        $qb->addSelect(DB::raw("(" . $qbCountFiltered->toSql() . ") as _recordsFiltered"));
    } else {
        $qb->addSelect(DB::raw("(select count(*) from {$table}) as _recordsFiltered"));
    }

    foreach ($dtOrder as $orderItem) {
        $columnIndex = $orderItem['column'];
        $columnSortDirection = $orderItem['dir'];

        $targetColumn = $dtColumns[$columnIndex]['data'];

        $matches = array_filter($selectColumns, fn ($var) => preg_match("/\b$targetColumn\b/i", $var));

        if (!empty($matches)) {
            $matches = array_pop($matches);
            $finalOrderedColumn = "";

            $sortedColumnExploded = explode(" as ", $matches);

            if (is_array($sortedColumnExploded)) {
                $finalOrderedColumn = $sortedColumnExploded[0];
            } else {
                $finalOrderedColumn = $matches;
            }

            $qb->orderBy($finalOrderedColumn, $columnSortDirection);
        }
    }

    //$sqlQuery = $qb->toSql();
    $sqlQuery = "";

    $data = $qb->orderByDesc("{$table}.created_at")->cursorPaginate($limit, $selectColumns, 'page');

    $allData = json_decode(json_encode(array_map(fn ($item) => $createNestedArray($item, ";"), $data->items())));

    foreach ($allData as $key => $dt) {
        $dt->row_number = 1;

        if ($recordsTotal < 1) {
            $recordsTotal = $dt->_recordsTotal;

            if (!$isFiltered && !$isSearching && !$isManipulated) {
                $recordsFiltered = $recordsTotal;
            } else {
                $recordsFiltered = $dt->_recordsFiltered;
            }
        }

        if (is_callable($actionColumn)) {
            $dt->action_buttons = $actionColumn($key, $dt);
        }

        foreach ($manipulateRow as $rowName => $rowCallback) {
            if (is_callable($rowCallback)) {
                $dt->$rowName = $rowCallback($key, $dt);
            } else {
                $dt->$rowName = $rowCallback;
            }
        }
    }

    return response()->json([
        'data' => $allData,
        'draw' => request()->draw,
        'sqlQuery' => $sqlQuery,
        'recordsFiltered' => $recordsFiltered,
        'recordsTotal' => $recordsTotal,
        'prev_page_url' => $data->previousPageUrl(),
        'next_page_url' => $data->nextPageUrl(),
        'columns' => $dtColumns,
        'orders' => $orders
    ]);
}

The reason why I'm add recordsTotal and recordsFiltered inside each row is to minimize SQL query trip. If you think otherwise then please reply.