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 */ 17declare(strict_types=1); 18 19namespace Fisharebest\Webtrees\Services; 20 21use Closure; 22use Illuminate\Database\Query\Builder; 23use Illuminate\Database\Query\Expression; 24use Psr\Http\Message\ResponseInterface; 25use Psr\Http\Message\ServerRequestInterface; 26 27/** 28 * Paginate and search queries for datatables. 29 */ 30class DatatablesService 31{ 32 /** 33 * Apply filtering and pagination to a query, and generate a response suitable for datatables. 34 * 35 * @link http://www.datatables.net/usage/server-side 36 * 37 * @param ServerRequestInterface $request Includes the datatables request parameters. 38 * @param Builder $query A query to fetch the unfiltered rows and columns. 39 * @param string[] $search_columns The names of searchable columns. 40 * @param string[] $sort_columns How to sort columns. 41 * @param Closure $callback Converts a row-object to an array-of-columns. 42 * 43 * @return ResponseInterface 44 */ 45 public function handle(ServerRequestInterface $request, Builder $query, array $search_columns, array $sort_columns, Closure $callback): ResponseInterface 46 { 47 $search = $request->getQueryParams()['search']['value'] ?? ''; 48 $start = (int) ($request->getQueryParams()['start'] ?? 0); 49 $length = (int) ($request->getQueryParams()['length'] ?? 0); 50 $order = $request->getQueryParams()['order'] ?? []; 51 $draw = (int) ($request->getQueryParams()['draw'] ?? 0); 52 53 // Count unfiltered records 54 $recordsTotal = (clone $query)->count(); 55 56 // Filtering 57 if ($search !== '') { 58 $query->where(static function (Builder $query) use ($search, $search_columns): void { 59 foreach ($search_columns as $search_column) { 60 $query->whereContains($search_column, $search, 'or'); 61 } 62 }); 63 } 64 65 // Sorting 66 if (!empty($order)) { 67 foreach ($order as $value) { 68 // Columns in datatables are numbered from zero. 69 // Columns in MySQL are numbered starting with one. 70 // If not specified, the Nth table column maps onto the Nth query column. 71 $sort_column = $sort_columns[$value['column']] ?? new Expression(1 + $value['column']); 72 73 $query->orderBy($sort_column, $value['dir']); 74 } 75 } else { 76 $query->orderBy(new Expression(1)); 77 } 78 79 // Paginating 80 if ($length > 0) { 81 $recordsFiltered = (clone $query)->count(); 82 83 $query->skip($start)->limit($length); 84 $data = $query->get(); 85 } else { 86 $data = $query->get(); 87 88 $recordsFiltered = $data->count(); 89 } 90 91 $data = $data->map($callback)->all(); 92 93 return response([ 94 'draw' => $draw, 95 'recordsTotal' => $recordsTotal, 96 'recordsFiltered' => $recordsFiltered, 97 'data' => $data, 98 ]); 99 } 100} 101