xref: /webtrees/app/Services/SearchService.php (revision e3bddf118fb1c51b71345fd30d48a450ce15ace3) 
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 Fisharebest\Localization\Locale\LocaleInterface;
22use Fisharebest\Webtrees\Family;
23use Fisharebest\Webtrees\Gedcom;
24use Fisharebest\Webtrees\GedcomRecord;
25use Fisharebest\Webtrees\Individual;
26use Fisharebest\Webtrees\Media;
27use Fisharebest\Webtrees\Note;
28use Fisharebest\Webtrees\Place;
29use Fisharebest\Webtrees\Repository;
30use Fisharebest\Webtrees\Source;
31use Fisharebest\Webtrees\Tree;
32use Illuminate\Database\Capsule\Manager as DB;
33use Illuminate\Database\Query\Builder;
34use Illuminate\Database\Query\Expression;
35use Illuminate\Database\Query\JoinClause;
36use Illuminate\Support\Collection;
37use stdClass;
38use function mb_stripos;
39
40/**
41 * Search trees for genealogy records.
42 */
43class SearchService
44{
45    /** @var LocaleInterface */
46    private $locale;
47
48    /**
49     * SearchService constructor.
50     *
51     * @param LocaleInterface $locale
52     */
53    public function __construct(LocaleInterface $locale)
54    {
55        $this->locale = $locale;
56    }
57
58    /**
59     * @param Tree[]   $trees
60     * @param string[] $search
61     *
62     * @return Collection|Family[]
63     */
64    public function searchFamilies(array $trees, array $search): Collection
65    {
66        $query = DB::table('families');
67
68        $this->whereTrees($query, 'f_file', $trees);
69        $this->whereSearch($query, 'f_gedcom', $search);
70
71        return $query
72            ->get()
73            ->map(Family::rowMapper())
74            ->filter(GedcomRecord::accessFilter())
75            ->filter($this->rawGedcomFilter($search));
76    }
77
78    /**
79     * Search for families by name.
80     *
81     * @param Tree[]   $trees
82     * @param string[] $search
83     * @param int      $offset
84     * @param int      $limit
85     *
86     * @return Collection|Family[]
87     */
88    public function searchFamilyNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
89    {
90        $query = DB::table('families')
91            ->join('name AS husb_name', function (JoinClause $join): void {
92                $join
93                    ->on('husb_name.n_file', '=', 'families.f_file')
94                    ->on('husb_name.n_id', '=', 'families.f_husb');
95            })
96            ->join('name AS wife_name', function (JoinClause $join): void {
97                $join
98                    ->on('wife_name.n_file', '=', 'families.f_file')
99                    ->on('wife_name.n_id', '=', 'families.f_wife');
100            })
101            ->where('wife_name.n_type', '<>', '_MARNM')
102            ->where('husb_name.n_type', '<>', '_MARNM');
103
104        $prefix = DB::connection()->getTablePrefix();
105        $field  = DB::raw($prefix . '(husb_name.n_full || ' . $prefix . 'wife_name.n_full)');
106
107        $this->whereTrees($query, 'f_file', $trees);
108        $this->whereSearch($query, $field, $search);
109
110        $query
111            ->orderBy('husb_name.n_sort')
112            ->orderBy('wife_name.n_sort')
113            ->select(['families.*', 'husb_name.n_sort', 'wife_name.n_sort'])
114            ->distinct();
115
116        return $this->paginateQuery($query, Family::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
117    }
118
119    /**
120     * @param Tree[]   $trees
121     * @param string[] $search
122     *
123     * @return Collection|Individual[]
124     */
125    public function searchIndividuals(array $trees, array $search): Collection
126    {
127        $query = DB::table('individuals');
128
129        $this->whereTrees($query, 'i_file', $trees);
130        $this->whereSearch($query, 'i_gedcom', $search);
131
132        return $query
133            ->get()
134            ->map(Individual::rowMapper())
135            ->filter(GedcomRecord::accessFilter())
136            ->filter($this->rawGedcomFilter($search));
137    }
138
139    /**
140     * Search for individuals by name.
141     *
142     * @param Tree[]   $trees
143     * @param string[] $search
144     * @param int      $offset
145     * @param int      $limit
146     *
147     * @return Collection|Individual[]
148     */
149    public function searchIndividualNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
150    {
151        $query = DB::table('individuals')
152            ->join('name', function (JoinClause $join): void {
153                $join
154                    ->on('name.n_file', '=', 'individuals.i_file')
155                    ->on('name.n_id', '=', 'individuals.i_id');
156            })
157            ->orderBy('n_sort')
158            ->select(['individuals.*', 'n_sort', 'n_num'])
159            ->distinct();
160
161        $this->whereTrees($query, 'i_file', $trees);
162        $this->whereSearch($query, 'n_full', $search);
163
164        return $this->paginateQuery($query, Individual::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
165    }
166
167    /**
168     * Search for media objects.
169     *
170     * @param Tree[]   $trees
171     * @param string[] $search
172     * @param int    $offset
173     * @param int    $limit
174     *
175     * @return Collection|Media[]
176     */
177    public function searchMedia(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
178    {
179        $query = DB::table('media');
180
181        $this->whereTrees($query, 'media.m_file', $trees);
182        $this->whereSearch($query, 'm_gedcom', $search);
183
184        return $this->paginateQuery($query, Media::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
185    }
186
187    /**
188     * Search for notes.
189     *
190     * @param Tree[]   $trees
191     * @param string[] $search
192     * @param int      $offset
193     * @param int      $limit
194     *
195     * @return Collection|Note[]
196     */
197    public function searchNotes(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
198    {
199        $query = DB::table('other')
200            ->where('o_type', '=', 'NOTE');
201
202        $this->whereTrees($query, 'o_file', $trees);
203        $this->whereSearch($query, 'o_gedcom', $search);
204
205        return $this->paginateQuery($query, Note::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
206    }
207
208    /**
209     * Search for repositories.
210     *
211     * @param Tree[]   $trees
212     * @param string[] $search
213     * @param int      $offset
214     * @param int      $limit
215     *
216     * @return Collection|Repository[]
217     */
218    public function searchRepositories(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
219    {
220        $query = DB::table('other')
221            ->where('o_type', '=', 'REPO');
222
223        $this->whereTrees($query, 'o_file', $trees);
224        $this->whereSearch($query, 'o_gedcom', $search);
225
226        return $this->paginateQuery($query, Repository::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
227    }
228
229    /**
230     * Search for sources.
231     *
232     * @param Tree[]   $trees
233     * @param string[] $search
234     * @param int      $offset
235     * @param int      $limit
236     *
237     * @return Collection|Source[]
238     */
239    public function searchSources(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
240    {
241        $query = DB::table('sources');
242
243        $this->whereTrees($query, 's_file', $trees);
244        $this->whereSearch($query, 's_gedcom', $search);
245
246        return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
247    }
248
249    /**
250     * Search for sources by name.
251     *
252     * @param Tree[]   $trees
253     * @param string[] $search
254     * @param int      $offset
255     * @param int      $limit
256     *
257     * @return Collection|Source[]
258     */
259    public function searchSourcesByName(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
260    {
261        $query = DB::table('sources')
262            ->orderBy('s_name');
263
264        $this->whereTrees($query, 's_file', $trees);
265        $this->whereSearch($query, 's_name', $search);
266
267        return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
268    }
269
270    /**
271     * Search for submitters.
272     *
273     * @param Tree[]   $trees
274     * @param string[] $search
275     * @param int      $offset
276     * @param int      $limit
277     *
278     * @return Collection|GedcomRecord[]
279     */
280    public function searchSubmitters(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
281    {
282        $query = DB::table('other')
283            ->where('o_type', '=', 'SUBM');
284
285        $this->whereTrees($query, 'o_file', $trees);
286        $this->whereSearch($query, 'o_gedcom', $search);
287
288        return $this->paginateQuery($query, GedcomRecord::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
289    }
290
291    /**
292     * Search for places.
293     *
294     * @param Tree   $tree
295     * @param string $search
296     * @param int    $offset
297     * @param int    $limit
298     *
299     * @return Collection|Place[]
300     */
301    public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
302    {
303        $query = DB::table('places AS p0')
304            ->where('p0.p_file', '=', $tree->id())
305            ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id')
306            ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id')
307            ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id')
308            ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id')
309            ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id')
310            ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id')
311            ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id')
312            ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id')
313            ->orderBy('p0.p_place')
314            ->orderBy('p1.p_place')
315            ->orderBy('p2.p_place')
316            ->orderBy('p3.p_place')
317            ->orderBy('p4.p_place')
318            ->orderBy('p5.p_place')
319            ->orderBy('p6.p_place')
320            ->orderBy('p7.p_place')
321            ->orderBy('p8.p_place')
322            ->select([
323                'p0.p_place AS place0',
324                'p1.p_place AS place1',
325                'p2.p_place AS place2',
326                'p3.p_place AS place3',
327                'p4.p_place AS place4',
328                'p5.p_place AS place5',
329                'p6.p_place AS place6',
330                'p7.p_place AS place7',
331                'p8.p_place AS place8',
332            ]);
333
334        // Filter each level of the hierarchy.
335        foreach (explode(',', $search, 9) as $level => $string) {
336            $query->whereContains('p' . $level . '.p_place', $string);
337        }
338
339        $row_mapper = function (stdClass $row) use ($tree): Place {
340            $place = implode(', ', array_filter((array) $row));
341
342            return new Place($place, $tree);
343        };
344
345        $filter = function (): bool {
346            return true;
347        };
348
349        return $this->paginateQuery($query, $row_mapper, $filter, $offset, $limit);
350    }
351
352    /**
353     * Paginate a search query.
354     *
355     * @param Builder $query      Searches the database for the desired records.
356     * @param Closure $row_mapper Converts a row from the query into a record.
357     * @param Closure $row_filter
358     * @param int     $offset     Skip this many rows.
359     * @param int     $limit      Take this many rows.
360     *
361     * @return Collection
362     */
363    private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection
364    {
365        $collection = new Collection();
366
367        foreach ($query->cursor() as $row) {
368            $record = $row_mapper($row);
369            // If the object has a method "canShow()", then use it to filter for privacy.
370            if ($row_filter($record)) {
371                if ($offset > 0) {
372                    $offset--;
373                } else {
374                    if ($limit > 0) {
375                        $collection->push($record);
376                    }
377
378                    $limit--;
379
380                    if ($limit === 0) {
381                        break;
382                    }
383                }
384            }
385        }
386
387        return $collection;
388    }
389
390    /**
391     * Apply search filters to a SQL query column.  Apply collation rules to MySQL.
392     *
393     * @param Builder           $query
394     * @param Expression|string $field
395     * @param string[]          $search_terms
396     */
397    private function whereSearch(Builder $query, $field, array $search_terms): void
398    {
399        if ($field instanceof Expression) {
400            $field = $field->getValue();
401        }
402
403        $field = DB::raw($field . ' /*! COLLATE ' . 'utf8_' . $this->locale->collation() . ' */');
404
405        foreach ($search_terms as $search_term) {
406            $query->whereContains($field, $search_term);
407        }
408    }
409
410    /**
411     * @param Builder $query
412     * @param string  $tree_id_field
413     * @param Tree[]  $trees
414     */
415    private function whereTrees(Builder $query, string $tree_id_field, array $trees): void
416    {
417        $tree_ids = array_map(function (Tree $tree) {
418            return $tree->id();
419        }, $trees);
420
421        $query->whereIn($tree_id_field, $tree_ids);
422    }
423
424    /**
425     * A closure to filter records by privacy-filtered GEDCOM data.
426     *
427     * @param array $search_terms
428     *
429     * @return Closure
430     */
431    private function rawGedcomFilter(array $search_terms): Closure
432    {
433        return function (GedcomRecord $record) use ($search_terms): bool {
434            // Ignore non-genealogy fields
435            $gedcom = preg_replace('/\n\d (?:_UID) .*/', '', $record->gedcom());
436
437            // Ignore matches in links
438            $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom);
439
440            // Re-apply the filtering
441            foreach ($search_terms as $search_term) {
442                if (mb_stripos($gedcom, $search_term) === false) {
443                    return false;
444                }
445            }
446
447            return true;
448        };
449    }
450}
451