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