xref: /webtrees/app/Services/DatatablesService.php (revision d346cc1c7558263f3862d4f0b4163a38490548ea) 
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 Symfony\Component\HttpFoundation\JsonResponse;
24use Symfony\Component\HttpFoundation\Request;
25use function strtr;
26
27/**
28 * Paginate and search queries for datatables.
29 */
30class DatatablesService
31{
32    // We need to escape special characters in a LIKE clause, so we can search for them.
33    private const ESCAPE_LIKE = [
34        '%'  => '\\%',
35        '_'  => '\\_',
36        '\\' => '\\\\',
37    ];
38
39    /**
40     * Apply filtering and pagination to a query, and generate a response suitable for datatables.
41     *
42     * @link http://www.datatables.net/usage/server-side
43     *
44     * @param Request  $request        Includes the datatables request parameters.
45     * @param Builder  $query          A query to fetch the unfiltered rows and columns.
46     * @param string[] $search_columns The names of searchable columns.
47     * @param Closure  $callback       Converts a row-object to an array-of-columns.
48     *
49     * @return JsonResponse
50     */
51    public function handle(Request $request, Builder $query, array $search_columns, Closure $callback): JsonResponse
52    {
53        $search = $request->get('search', [])['value'] ?? '';
54        $start  = (int) $request->get('start');
55        $length = (int) $request->get('length');
56        $order  = $request->get('order', []);
57        $draw   = (int) $request->get('draw');
58
59        // Count unfiltered records
60        $recordsTotal = (clone $query)->count();
61
62        // Filtering
63        if ($search !== '') {
64            $search = $this->escapeLike($search);
65
66            $query->where(function (Builder $query) use ($search, $search_columns): void {
67                foreach ($search_columns as $search_column) {
68                    $query->orWhere($search_column, 'LIKE', '%' . $search . '%');
69                }
70            });
71        }
72
73        // Sorting
74        if (!empty($order)) {
75            foreach ($order as $value) {
76                // Columns in datatables are numbered from zero.
77                // Columns in MySQL are numbered starting with one.
78                $query->orderBy(DB::raw(1 + $value['column']), $value['dir']);
79            }
80        } else {
81            $query->orderBy(DB::raw(1));
82        }
83
84        // Paginating
85        if ($length > 0) {
86            $recordsFiltered = (clone $query)->count();
87
88            $query->skip($start)->limit($length);
89            $data = $query->get();
90        } else {
91            $data = $query->get();
92
93            $recordsFiltered = $data->count();
94        }
95
96        $data = $data->map($callback)->all();
97
98        return new JsonResponse([
99            'draw'            => $draw,
100            'recordsTotal'    => $recordsTotal,
101            'recordsFiltered' => $recordsFiltered,
102            'data'            => $data,
103        ]);
104    }
105
106    /**
107     * Escape a search term, so we can use it in a LIKE clause.
108     * This lets us search for percent signs, underscores, etc.
109     *
110     * @param string $string
111     *
112     * @return string
113     */
114    private function escapeLike(string $string): string
115    {
116        return strtr($string, self::ESCAPE_LIKE);
117    }
118}
119