1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2019 webtrees development team 5 * This program is free software: you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation, either version 3 of the License, or 8 * (at your option) any later version. 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * You should have received a copy of the GNU General Public License 14 * along with this program. If not, see <http://www.gnu.org/licenses/>. 15 */ 16declare(strict_types=1); 17 18namespace Fisharebest\Webtrees\Services; 19 20use Closure; 21use Illuminate\Database\Capsule\Manager as DB; 22use Illuminate\Database\Query\Builder; 23use Psr\Http\Message\ResponseInterface; 24use Psr\Http\Message\ServerRequestInterface; 25 26/** 27 * Paginate and search queries for datatables. 28 */ 29class DatatablesService 30{ 31 /** 32 * Apply filtering and pagination to a query, and generate a response suitable for datatables. 33 * 34 * @link http://www.datatables.net/usage/server-side 35 * 36 * @param ServerRequestInterface $request Includes the datatables request parameters. 37 * @param Builder $query A query to fetch the unfiltered rows and columns. 38 * @param string[] $search_columns The names of searchable columns. 39 * @param string[] $sort_columns How to sort columns. 40 * @param Closure $callback Converts a row-object to an array-of-columns. 41 * 42 * @return ResponseInterface 43 */ 44 public function handle(ServerRequestInterface $request, Builder $query, array $search_columns, array $sort_columns, Closure $callback): ResponseInterface 45 { 46 $search = $request->getQueryParams()['search']['value'] ?? ''; 47 $start = (int) ($request->getQueryParams()['start'] ?? 0); 48 $length = (int) ($request->getQueryParams()['length'] ?? 0); 49 $order = $request->getQueryParams()['order'] ?? []; 50 $draw = (int) ($request->getQueryParams()['draw'] ?? 0); 51 52 // Count unfiltered records 53 $recordsTotal = (clone $query)->count(); 54 55 // Filtering 56 if ($search !== '') { 57 $query->where(static function (Builder $query) use ($search, $search_columns): void { 58 foreach ($search_columns as $search_column) { 59 $query->whereContains($search_column, $search, 'or'); 60 } 61 }); 62 } 63 64 // Sorting 65 if (!empty($order)) { 66 foreach ($order as $value) { 67 // Columns in datatables are numbered from zero. 68 // Columns in MySQL are numbered starting with one. 69 // If not specified, the Nth table column maps onto the Nth query column. 70 $sort_column = $sort_columns[$value['column']] ?? DB::raw(1 + $value['column']); 71 72 $query->orderBy($sort_column, $value['dir']); 73 } 74 } else { 75 $query->orderBy(DB::raw(1)); 76 } 77 78 // Paginating 79 if ($length > 0) { 80 $recordsFiltered = (clone $query)->count(); 81 82 $query->skip($start)->limit($length); 83 $data = $query->get(); 84 } else { 85 $data = $query->get(); 86 87 $recordsFiltered = $data->count(); 88 } 89 90 $data = $data->map($callback)->all(); 91 92 return response([ 93 'draw' => $draw, 94 'recordsTotal' => $recordsTotal, 95 'recordsFiltered' => $recordsFiltered, 96 'data' => $data, 97 ]); 98 } 99} 100