asbp · October 5, 2022 10:46 · asbp · Oct 5, 2022
diff --git a/handleDataTables.php b/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
    ]);
 }
	<?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
	]);
	}