xref: /webtrees/app/Services/DatatablesService.php (revision 9ee8cdae88d7d69ce2e20928a950aa88e76671f2) 
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;
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 Request  $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 JsonResponse
43     */
44    public function handle(Request $request, Builder $query, array $search_columns, array $sort_columns, Closure $callback): JsonResponse
45    {
46        $search = $request->get('search', [])['value'] ?? '';
47        $start  = (int) $request->get('start');
48        $length = (int) $request->get('length');
49        $order  = $request->get('order', []);
50        $draw   = (int) $request->get('draw');
51
52        // Count unfiltered records
53        $recordsTotal = (clone $query)->count();
54
55        // Filtering
56        if ($search !== '') {
57            $query->where(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 new JsonResponse([
93            'draw'            => $draw,
94            'recordsTotal'    => $recordsTotal,
95            'recordsFiltered' => $recordsFiltered,
96            'data'            => $data,
97        ]);
98    }
99}
100