xref: /webtrees/app/Services/SearchService.php (revision 2d686e681a4ef78769aa3a02ff242e7df5ab81b6)
132cd2800SGreg Roach<?php
232cd2800SGreg Roach/**
332cd2800SGreg Roach * webtrees: online genealogy
432cd2800SGreg Roach * Copyright (C) 2019 webtrees development team
532cd2800SGreg Roach * This program is free software: you can redistribute it and/or modify
632cd2800SGreg Roach * it under the terms of the GNU General Public License as published by
732cd2800SGreg Roach * the Free Software Foundation, either version 3 of the License, or
832cd2800SGreg Roach * (at your option) any later version.
932cd2800SGreg Roach * This program is distributed in the hope that it will be useful,
1032cd2800SGreg Roach * but WITHOUT ANY WARRANTY; without even the implied warranty of
1132cd2800SGreg Roach * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
1232cd2800SGreg Roach * GNU General Public License for more details.
1332cd2800SGreg Roach * You should have received a copy of the GNU General Public License
1432cd2800SGreg Roach * along with this program. If not, see <http://www.gnu.org/licenses/>.
1532cd2800SGreg Roach */
1632cd2800SGreg Roachdeclare(strict_types=1);
1732cd2800SGreg Roach
1832cd2800SGreg Roachnamespace Fisharebest\Webtrees\Services;
1932cd2800SGreg Roach
2032cd2800SGreg Roachuse Closure;
21a7a24840SGreg Roachuse Fisharebest\Localization\Locale\LocaleInterface;
2232cd2800SGreg Roachuse Fisharebest\Webtrees\Family;
23a7a24840SGreg Roachuse Fisharebest\Webtrees\Gedcom;
2432cd2800SGreg Roachuse Fisharebest\Webtrees\GedcomRecord;
2532cd2800SGreg Roachuse Fisharebest\Webtrees\Individual;
2632cd2800SGreg Roachuse Fisharebest\Webtrees\Media;
2732cd2800SGreg Roachuse Fisharebest\Webtrees\Note;
28b68caec6SGreg Roachuse Fisharebest\Webtrees\Place;
2932cd2800SGreg Roachuse Fisharebest\Webtrees\Repository;
30*2d686e68SGreg Roachuse Fisharebest\Webtrees\Soundex;
3132cd2800SGreg Roachuse Fisharebest\Webtrees\Source;
3232cd2800SGreg Roachuse Fisharebest\Webtrees\Tree;
3332cd2800SGreg Roachuse Illuminate\Database\Capsule\Manager as DB;
3432cd2800SGreg Roachuse Illuminate\Database\Query\Builder;
35a7a24840SGreg Roachuse Illuminate\Database\Query\Expression;
3632cd2800SGreg Roachuse Illuminate\Database\Query\JoinClause;
3732cd2800SGreg Roachuse Illuminate\Support\Collection;
3832cd2800SGreg Roachuse stdClass;
39a7a24840SGreg Roachuse function mb_stripos;
4032cd2800SGreg Roach
4132cd2800SGreg Roach/**
4232cd2800SGreg Roach * Search trees for genealogy records.
4332cd2800SGreg Roach */
4432cd2800SGreg Roachclass SearchService
4532cd2800SGreg Roach{
46a7a24840SGreg Roach    /** @var LocaleInterface */
47a7a24840SGreg Roach    private $locale;
48a7a24840SGreg Roach
49a7a24840SGreg Roach    /**
50a7a24840SGreg Roach     * SearchService constructor.
51a7a24840SGreg Roach     *
52a7a24840SGreg Roach     * @param LocaleInterface $locale
53a7a24840SGreg Roach     */
54a7a24840SGreg Roach    public function __construct(LocaleInterface $locale)
55a7a24840SGreg Roach    {
56a7a24840SGreg Roach        $this->locale = $locale;
57a7a24840SGreg Roach    }
58a7a24840SGreg Roach
59a7a24840SGreg Roach    /**
60a7a24840SGreg Roach     * @param Tree[]   $trees
61a7a24840SGreg Roach     * @param string[] $search
62a7a24840SGreg Roach     *
63a7a24840SGreg Roach     * @return Collection|Family[]
64a7a24840SGreg Roach     */
65a7a24840SGreg Roach    public function searchFamilies(array $trees, array $search): Collection
66a7a24840SGreg Roach    {
67a7a24840SGreg Roach        $query = DB::table('families');
68a7a24840SGreg Roach
69a7a24840SGreg Roach        $this->whereTrees($query, 'f_file', $trees);
70a7a24840SGreg Roach        $this->whereSearch($query, 'f_gedcom', $search);
71a7a24840SGreg Roach
72a7a24840SGreg Roach        return $query
73a7a24840SGreg Roach            ->get()
74a7a24840SGreg Roach            ->map(Family::rowMapper())
75a7a24840SGreg Roach            ->filter(GedcomRecord::accessFilter())
767f5fa3c2SGreg Roach            ->filter($this->rawGedcomFilter($search));
77a7a24840SGreg Roach    }
78a7a24840SGreg Roach
7932cd2800SGreg Roach    /**
8032cd2800SGreg Roach     * Search for families by name.
8132cd2800SGreg Roach     *
82a7a24840SGreg Roach     * @param Tree[]   $trees
83a7a24840SGreg Roach     * @param string[] $search
8432cd2800SGreg Roach     * @param int      $offset
8532cd2800SGreg Roach     * @param int      $limit
8632cd2800SGreg Roach     *
8732cd2800SGreg Roach     * @return Collection|Family[]
8832cd2800SGreg Roach     */
89a7a24840SGreg Roach    public function searchFamilyNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
9032cd2800SGreg Roach    {
9132cd2800SGreg Roach        $query = DB::table('families')
92a7a24840SGreg Roach            ->join('name AS husb_name', function (JoinClause $join): void {
9332cd2800SGreg Roach                $join
9432cd2800SGreg Roach                    ->on('husb_name.n_file', '=', 'families.f_file')
95a7a24840SGreg Roach                    ->on('husb_name.n_id', '=', 'families.f_husb');
9632cd2800SGreg Roach            })
97a7a24840SGreg Roach            ->join('name AS wife_name', function (JoinClause $join): void {
9832cd2800SGreg Roach                $join
9932cd2800SGreg Roach                    ->on('wife_name.n_file', '=', 'families.f_file')
100a7a24840SGreg Roach                    ->on('wife_name.n_id', '=', 'families.f_wife');
10132cd2800SGreg Roach            })
102a7a24840SGreg Roach            ->where('wife_name.n_type', '<>', '_MARNM')
103a7a24840SGreg Roach            ->where('husb_name.n_type', '<>', '_MARNM');
104a7a24840SGreg Roach
105a7a24840SGreg Roach        $prefix = DB::connection()->getTablePrefix();
106e3bddf11SGreg Roach        $field  = DB::raw($prefix . '(husb_name.n_full || ' . $prefix . 'wife_name.n_full)');
107a7a24840SGreg Roach
108a7a24840SGreg Roach        $this->whereTrees($query, 'f_file', $trees);
109a7a24840SGreg Roach        $this->whereSearch($query, $field, $search);
110a7a24840SGreg Roach
111a7a24840SGreg Roach        $query
11232cd2800SGreg Roach            ->orderBy('husb_name.n_sort')
11332cd2800SGreg Roach            ->orderBy('wife_name.n_sort')
114c0804649SGreg Roach            ->select(['families.*', 'husb_name.n_sort', 'wife_name.n_sort'])
11532cd2800SGreg Roach            ->distinct();
11632cd2800SGreg Roach
117a7a24840SGreg Roach        return $this->paginateQuery($query, Family::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
118a7a24840SGreg Roach    }
119a7a24840SGreg Roach
120a7a24840SGreg Roach    /**
121a7a24840SGreg Roach     * @param Tree[]   $trees
122a7a24840SGreg Roach     * @param string[] $search
123a7a24840SGreg Roach     *
124a7a24840SGreg Roach     * @return Collection|Individual[]
125a7a24840SGreg Roach     */
126a7a24840SGreg Roach    public function searchIndividuals(array $trees, array $search): Collection
127a7a24840SGreg Roach    {
128a7a24840SGreg Roach        $query = DB::table('individuals');
129a7a24840SGreg Roach
130a7a24840SGreg Roach        $this->whereTrees($query, 'i_file', $trees);
131a7a24840SGreg Roach        $this->whereSearch($query, 'i_gedcom', $search);
132a7a24840SGreg Roach
133a7a24840SGreg Roach        return $query
134a7a24840SGreg Roach            ->get()
135a7a24840SGreg Roach            ->map(Individual::rowMapper())
136a7a24840SGreg Roach            ->filter(GedcomRecord::accessFilter())
1377f5fa3c2SGreg Roach            ->filter($this->rawGedcomFilter($search));
13832cd2800SGreg Roach    }
13932cd2800SGreg Roach
14032cd2800SGreg Roach    /**
14132cd2800SGreg Roach     * Search for individuals by name.
14232cd2800SGreg Roach     *
143a7a24840SGreg Roach     * @param Tree[]   $trees
144a7a24840SGreg Roach     * @param string[] $search
14532cd2800SGreg Roach     * @param int      $offset
14632cd2800SGreg Roach     * @param int      $limit
14732cd2800SGreg Roach     *
14832cd2800SGreg Roach     * @return Collection|Individual[]
14932cd2800SGreg Roach     */
150a7a24840SGreg Roach    public function searchIndividualNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
15132cd2800SGreg Roach    {
15232cd2800SGreg Roach        $query = DB::table('individuals')
153a7a24840SGreg Roach            ->join('name', function (JoinClause $join): void {
15432cd2800SGreg Roach                $join
15532cd2800SGreg Roach                    ->on('name.n_file', '=', 'individuals.i_file')
156a7a24840SGreg Roach                    ->on('name.n_id', '=', 'individuals.i_id');
15732cd2800SGreg Roach            })
158e84cf2deSGreg Roach            ->orderBy('n_sort')
159c0804649SGreg Roach            ->select(['individuals.*', 'n_sort', 'n_num'])
16032cd2800SGreg Roach            ->distinct();
16132cd2800SGreg Roach
162a7a24840SGreg Roach        $this->whereTrees($query, 'i_file', $trees);
163a7a24840SGreg Roach        $this->whereSearch($query, 'n_full', $search);
164a7a24840SGreg Roach
165a7a24840SGreg Roach        return $this->paginateQuery($query, Individual::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
16632cd2800SGreg Roach    }
16732cd2800SGreg Roach
16832cd2800SGreg Roach    /**
16932cd2800SGreg Roach     * Search for media objects.
17032cd2800SGreg Roach     *
171a7a24840SGreg Roach     * @param Tree[]   $trees
172a7a24840SGreg Roach     * @param string[] $search
17332cd2800SGreg Roach     * @param int    $offset
17432cd2800SGreg Roach     * @param int    $limit
17532cd2800SGreg Roach     *
17632cd2800SGreg Roach     * @return Collection|Media[]
17732cd2800SGreg Roach     */
178a7a24840SGreg Roach    public function searchMedia(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
17932cd2800SGreg Roach    {
180a7a24840SGreg Roach        $query = DB::table('media');
18132cd2800SGreg Roach
182a7a24840SGreg Roach        $this->whereTrees($query, 'media.m_file', $trees);
183a7a24840SGreg Roach        $this->whereSearch($query, 'm_gedcom', $search);
184a7a24840SGreg Roach
185a7a24840SGreg Roach        return $this->paginateQuery($query, Media::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
18632cd2800SGreg Roach    }
18732cd2800SGreg Roach
18832cd2800SGreg Roach    /**
18932cd2800SGreg Roach     * Search for notes.
19032cd2800SGreg Roach     *
191a7a24840SGreg Roach     * @param Tree[]   $trees
192a7a24840SGreg Roach     * @param string[] $search
19332cd2800SGreg Roach     * @param int      $offset
19432cd2800SGreg Roach     * @param int      $limit
19532cd2800SGreg Roach     *
19632cd2800SGreg Roach     * @return Collection|Note[]
19732cd2800SGreg Roach     */
198a7a24840SGreg Roach    public function searchNotes(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
19932cd2800SGreg Roach    {
20032cd2800SGreg Roach        $query = DB::table('other')
201a7a24840SGreg Roach            ->where('o_type', '=', 'NOTE');
20232cd2800SGreg Roach
203a7a24840SGreg Roach        $this->whereTrees($query, 'o_file', $trees);
204a7a24840SGreg Roach        $this->whereSearch($query, 'o_gedcom', $search);
205a7a24840SGreg Roach
206a7a24840SGreg Roach        return $this->paginateQuery($query, Note::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
20732cd2800SGreg Roach    }
20832cd2800SGreg Roach
20932cd2800SGreg Roach    /**
21032cd2800SGreg Roach     * Search for repositories.
21132cd2800SGreg Roach     *
212a7a24840SGreg Roach     * @param Tree[]   $trees
213a7a24840SGreg Roach     * @param string[] $search
21432cd2800SGreg Roach     * @param int      $offset
21532cd2800SGreg Roach     * @param int      $limit
21632cd2800SGreg Roach     *
21732cd2800SGreg Roach     * @return Collection|Repository[]
21832cd2800SGreg Roach     */
219a7a24840SGreg Roach    public function searchRepositories(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
22032cd2800SGreg Roach    {
22132cd2800SGreg Roach        $query = DB::table('other')
222a7a24840SGreg Roach            ->where('o_type', '=', 'REPO');
22332cd2800SGreg Roach
224a7a24840SGreg Roach        $this->whereTrees($query, 'o_file', $trees);
225a7a24840SGreg Roach        $this->whereSearch($query, 'o_gedcom', $search);
226a7a24840SGreg Roach
227a7a24840SGreg Roach        return $this->paginateQuery($query, Repository::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
22832cd2800SGreg Roach    }
22932cd2800SGreg Roach
23032cd2800SGreg Roach    /**
231a7a24840SGreg Roach     * Search for sources.
23232cd2800SGreg Roach     *
233a7a24840SGreg Roach     * @param Tree[]   $trees
234a7a24840SGreg Roach     * @param string[] $search
23532cd2800SGreg Roach     * @param int      $offset
23632cd2800SGreg Roach     * @param int      $limit
23732cd2800SGreg Roach     *
23832cd2800SGreg Roach     * @return Collection|Source[]
23932cd2800SGreg Roach     */
240a7a24840SGreg Roach    public function searchSources(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
241a7a24840SGreg Roach    {
242a7a24840SGreg Roach        $query = DB::table('sources');
243a7a24840SGreg Roach
244a7a24840SGreg Roach        $this->whereTrees($query, 's_file', $trees);
245a7a24840SGreg Roach        $this->whereSearch($query, 's_gedcom', $search);
246a7a24840SGreg Roach
247a7a24840SGreg Roach        return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
248a7a24840SGreg Roach    }
249a7a24840SGreg Roach
250a7a24840SGreg Roach    /**
251a7a24840SGreg Roach     * Search for sources by name.
252a7a24840SGreg Roach     *
253a7a24840SGreg Roach     * @param Tree[]   $trees
254a7a24840SGreg Roach     * @param string[] $search
255a7a24840SGreg Roach     * @param int      $offset
256a7a24840SGreg Roach     * @param int      $limit
257a7a24840SGreg Roach     *
258a7a24840SGreg Roach     * @return Collection|Source[]
259a7a24840SGreg Roach     */
260a7a24840SGreg Roach    public function searchSourcesByName(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
26132cd2800SGreg Roach    {
26232cd2800SGreg Roach        $query = DB::table('sources')
263c0804649SGreg Roach            ->orderBy('s_name');
26432cd2800SGreg Roach
265a7a24840SGreg Roach        $this->whereTrees($query, 's_file', $trees);
266a7a24840SGreg Roach        $this->whereSearch($query, 's_name', $search);
267a7a24840SGreg Roach
268a7a24840SGreg Roach        return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
26932cd2800SGreg Roach    }
27032cd2800SGreg Roach
27132cd2800SGreg Roach    /**
27232cd2800SGreg Roach     * Search for submitters.
27332cd2800SGreg Roach     *
274a7a24840SGreg Roach     * @param Tree[]   $trees
275a7a24840SGreg Roach     * @param string[] $search
27632cd2800SGreg Roach     * @param int      $offset
27732cd2800SGreg Roach     * @param int      $limit
27832cd2800SGreg Roach     *
27932cd2800SGreg Roach     * @return Collection|GedcomRecord[]
28032cd2800SGreg Roach     */
281a7a24840SGreg Roach    public function searchSubmitters(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
28232cd2800SGreg Roach    {
28332cd2800SGreg Roach        $query = DB::table('other')
284a7a24840SGreg Roach            ->where('o_type', '=', 'SUBM');
28532cd2800SGreg Roach
286a7a24840SGreg Roach        $this->whereTrees($query, 'o_file', $trees);
287a7a24840SGreg Roach        $this->whereSearch($query, 'o_gedcom', $search);
288a7a24840SGreg Roach
289a7a24840SGreg Roach        return $this->paginateQuery($query, GedcomRecord::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit);
29032cd2800SGreg Roach    }
29132cd2800SGreg Roach
29232cd2800SGreg Roach    /**
293b68caec6SGreg Roach     * Search for places.
294b68caec6SGreg Roach     *
295b68caec6SGreg Roach     * @param Tree   $tree
296b68caec6SGreg Roach     * @param string $search
297b68caec6SGreg Roach     * @param int    $offset
298b68caec6SGreg Roach     * @param int    $limit
299b68caec6SGreg Roach     *
300b68caec6SGreg Roach     * @return Collection|Place[]
301b68caec6SGreg Roach     */
302b68caec6SGreg Roach    public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection
303b68caec6SGreg Roach    {
304b68caec6SGreg Roach        $query = DB::table('places AS p0')
305b68caec6SGreg Roach            ->where('p0.p_file', '=', $tree->id())
306b68caec6SGreg Roach            ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id')
307b68caec6SGreg Roach            ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id')
308b68caec6SGreg Roach            ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id')
309b68caec6SGreg Roach            ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id')
310b68caec6SGreg Roach            ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id')
311b68caec6SGreg Roach            ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id')
312b68caec6SGreg Roach            ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id')
313b68caec6SGreg Roach            ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id')
314b68caec6SGreg Roach            ->orderBy('p0.p_place')
315b68caec6SGreg Roach            ->orderBy('p1.p_place')
316b68caec6SGreg Roach            ->orderBy('p2.p_place')
317b68caec6SGreg Roach            ->orderBy('p3.p_place')
318b68caec6SGreg Roach            ->orderBy('p4.p_place')
319b68caec6SGreg Roach            ->orderBy('p5.p_place')
320b68caec6SGreg Roach            ->orderBy('p6.p_place')
321b68caec6SGreg Roach            ->orderBy('p7.p_place')
322b68caec6SGreg Roach            ->orderBy('p8.p_place')
323b68caec6SGreg Roach            ->select([
324b68caec6SGreg Roach                'p0.p_place AS place0',
325b68caec6SGreg Roach                'p1.p_place AS place1',
326b68caec6SGreg Roach                'p2.p_place AS place2',
327b68caec6SGreg Roach                'p3.p_place AS place3',
328b68caec6SGreg Roach                'p4.p_place AS place4',
329b68caec6SGreg Roach                'p5.p_place AS place5',
330b68caec6SGreg Roach                'p6.p_place AS place6',
331b68caec6SGreg Roach                'p7.p_place AS place7',
332b68caec6SGreg Roach                'p8.p_place AS place8',
333b68caec6SGreg Roach            ]);
334b68caec6SGreg Roach
335b68caec6SGreg Roach        // Filter each level of the hierarchy.
336b68caec6SGreg Roach        foreach (explode(',', $search, 9) as $level => $string) {
337b68caec6SGreg Roach            $query->whereContains('p' . $level . '.p_place', $string);
338b68caec6SGreg Roach        }
339b68caec6SGreg Roach
340b68caec6SGreg Roach        $row_mapper = function (stdClass $row) use ($tree): Place {
341b68caec6SGreg Roach            $place = implode(', ', array_filter((array) $row));
342b68caec6SGreg Roach
343b68caec6SGreg Roach            return new Place($place, $tree);
344b68caec6SGreg Roach        };
345b68caec6SGreg Roach
346a7a24840SGreg Roach        $filter = function (): bool {
347a7a24840SGreg Roach            return true;
348a7a24840SGreg Roach        };
349b68caec6SGreg Roach
350a7a24840SGreg Roach        return $this->paginateQuery($query, $row_mapper, $filter, $offset, $limit);
351a7a24840SGreg Roach    }
352b68caec6SGreg Roach
353b68caec6SGreg Roach    /**
35432cd2800SGreg Roach     * Paginate a search query.
35532cd2800SGreg Roach     *
35632cd2800SGreg Roach     * @param Builder $query      Searches the database for the desired records.
35732cd2800SGreg Roach     * @param Closure $row_mapper Converts a row from the query into a record.
358a7a24840SGreg Roach     * @param Closure $row_filter
35932cd2800SGreg Roach     * @param int     $offset     Skip this many rows.
36032cd2800SGreg Roach     * @param int     $limit      Take this many rows.
36132cd2800SGreg Roach     *
36232cd2800SGreg Roach     * @return Collection
36332cd2800SGreg Roach     */
364a7a24840SGreg Roach    private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection
36532cd2800SGreg Roach    {
36632cd2800SGreg Roach        $collection = new Collection();
36732cd2800SGreg Roach
36832cd2800SGreg Roach        foreach ($query->cursor() as $row) {
36932cd2800SGreg Roach            $record = $row_mapper($row);
370b68caec6SGreg Roach            // If the object has a method "canShow()", then use it to filter for privacy.
371a7a24840SGreg Roach            if ($row_filter($record)) {
37232cd2800SGreg Roach                if ($offset > 0) {
37332cd2800SGreg Roach                    $offset--;
37432cd2800SGreg Roach                } else {
37532cd2800SGreg Roach                    if ($limit > 0) {
37632cd2800SGreg Roach                        $collection->push($record);
37732cd2800SGreg Roach                    }
37832cd2800SGreg Roach
37932cd2800SGreg Roach                    $limit--;
38032cd2800SGreg Roach
38132cd2800SGreg Roach                    if ($limit === 0) {
38232cd2800SGreg Roach                        break;
38332cd2800SGreg Roach                    }
38432cd2800SGreg Roach                }
38532cd2800SGreg Roach            }
38632cd2800SGreg Roach        }
38732cd2800SGreg Roach
38832cd2800SGreg Roach        return $collection;
38932cd2800SGreg Roach    }
390a7a24840SGreg Roach
391a7a24840SGreg Roach    /**
392a7a24840SGreg Roach     * Apply search filters to a SQL query column.  Apply collation rules to MySQL.
393a7a24840SGreg Roach     *
394a7a24840SGreg Roach     * @param Builder           $query
395a7a24840SGreg Roach     * @param Expression|string $field
396a7a24840SGreg Roach     * @param string[]          $search_terms
397a7a24840SGreg Roach     */
398a7a24840SGreg Roach    private function whereSearch(Builder $query, $field, array $search_terms): void
399a7a24840SGreg Roach    {
400a7a24840SGreg Roach        if ($field instanceof Expression) {
401a7a24840SGreg Roach            $field = $field->getValue();
402a7a24840SGreg Roach        }
403a7a24840SGreg Roach
404a7a24840SGreg Roach        $field = DB::raw($field . ' /*! COLLATE ' . 'utf8_' . $this->locale->collation() . ' */');
405a7a24840SGreg Roach
406a7a24840SGreg Roach        foreach ($search_terms as $search_term) {
407a7a24840SGreg Roach            $query->whereContains($field, $search_term);
408a7a24840SGreg Roach        }
409a7a24840SGreg Roach    }
410a7a24840SGreg Roach
411a7a24840SGreg Roach    /**
412*2d686e68SGreg Roach     * Apply soundex search filters to a SQL query column.
413*2d686e68SGreg Roach     *
414*2d686e68SGreg Roach     * @param Builder           $query
415*2d686e68SGreg Roach     * @param Expression|string $field
416*2d686e68SGreg Roach     * @param string            $soundex
417*2d686e68SGreg Roach     */
418*2d686e68SGreg Roach    private function wherePhonetic(Builder $query, $field, string $soundex): void
419*2d686e68SGreg Roach    {
420*2d686e68SGreg Roach        if ($soundex !== '') {
421*2d686e68SGreg Roach            $query->where(function (Builder $query) use ($soundex, $field): void {
422*2d686e68SGreg Roach                foreach (explode(':', $soundex) as $sdx) {
423*2d686e68SGreg Roach                    $query->orWhere($field, 'LIKE', '%' . $sdx . '%');
424*2d686e68SGreg Roach                }
425*2d686e68SGreg Roach            });
426*2d686e68SGreg Roach        }
427*2d686e68SGreg Roach    }
428*2d686e68SGreg Roach
429*2d686e68SGreg Roach    /**
430a7a24840SGreg Roach     * @param Builder $query
431a7a24840SGreg Roach     * @param string  $tree_id_field
432a7a24840SGreg Roach     * @param Tree[]  $trees
433a7a24840SGreg Roach     */
434a7a24840SGreg Roach    private function whereTrees(Builder $query, string $tree_id_field, array $trees): void
435a7a24840SGreg Roach    {
436a7a24840SGreg Roach        $tree_ids = array_map(function (Tree $tree) {
437a7a24840SGreg Roach            return $tree->id();
438a7a24840SGreg Roach        }, $trees);
439a7a24840SGreg Roach
440a7a24840SGreg Roach        $query->whereIn($tree_id_field, $tree_ids);
441a7a24840SGreg Roach    }
442a7a24840SGreg Roach
443a7a24840SGreg Roach    /**
444a7a24840SGreg Roach     * A closure to filter records by privacy-filtered GEDCOM data.
445a7a24840SGreg Roach     *
446a7a24840SGreg Roach     * @param array $search_terms
447a7a24840SGreg Roach     *
448a7a24840SGreg Roach     * @return Closure
449a7a24840SGreg Roach     */
450a7a24840SGreg Roach    private function rawGedcomFilter(array $search_terms): Closure
451a7a24840SGreg Roach    {
452a7a24840SGreg Roach        return function (GedcomRecord $record) use ($search_terms): bool {
453a7a24840SGreg Roach            // Ignore non-genealogy fields
454a7a24840SGreg Roach            $gedcom = preg_replace('/\n\d (?:_UID) .*/', '', $record->gedcom());
455a7a24840SGreg Roach
456a7a24840SGreg Roach            // Ignore matches in links
457a7a24840SGreg Roach            $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom);
458a7a24840SGreg Roach
459a7a24840SGreg Roach            // Re-apply the filtering
460a7a24840SGreg Roach            foreach ($search_terms as $search_term) {
461a7a24840SGreg Roach                if (mb_stripos($gedcom, $search_term) === false) {
462a7a24840SGreg Roach                    return false;
463a7a24840SGreg Roach                }
464a7a24840SGreg Roach            }
465a7a24840SGreg Roach
466a7a24840SGreg Roach            return true;
467a7a24840SGreg Roach        };
468a7a24840SGreg Roach    }
469*2d686e68SGreg Roach
470*2d686e68SGreg Roach    /**
471*2d686e68SGreg Roach     * @param string $soundex
472*2d686e68SGreg Roach     * @param string $lastname
473*2d686e68SGreg Roach     * @param string $firstname
474*2d686e68SGreg Roach     * @param string $place
475*2d686e68SGreg Roach     * @param Tree[] $search_trees
476*2d686e68SGreg Roach     *
477*2d686e68SGreg Roach     * @return Collection|Individual[]
478*2d686e68SGreg Roach     */
479*2d686e68SGreg Roach    public function searchIndividualsPhonetic(string $soundex, string $lastname, string $firstname, string $place, array $search_trees)
480*2d686e68SGreg Roach    {
481*2d686e68SGreg Roach        switch ($soundex) {
482*2d686e68SGreg Roach            default:
483*2d686e68SGreg Roach            case 'Russell':
484*2d686e68SGreg Roach                $givn_sdx   = Soundex::russell($firstname);
485*2d686e68SGreg Roach                $surn_sdx   = Soundex::russell($lastname);
486*2d686e68SGreg Roach                $plac_sdx   = Soundex::russell($place);
487*2d686e68SGreg Roach                $givn_field = 'n_soundex_givn_std';
488*2d686e68SGreg Roach                $surn_field = 'n_soundex_surn_std';
489*2d686e68SGreg Roach                $plac_field = 'p_std_soundex';
490*2d686e68SGreg Roach                break;
491*2d686e68SGreg Roach            case 'DaitchM':
492*2d686e68SGreg Roach                $givn_sdx   = Soundex::daitchMokotoff($firstname);
493*2d686e68SGreg Roach                $surn_sdx   = Soundex::daitchMokotoff($lastname);
494*2d686e68SGreg Roach                $plac_sdx   = Soundex::daitchMokotoff($place);
495*2d686e68SGreg Roach                $givn_field = 'n_soundex_givn_dm';
496*2d686e68SGreg Roach                $surn_field = 'n_soundex_surn_dm';
497*2d686e68SGreg Roach                $plac_field = 'p_dm_soundex';
498*2d686e68SGreg Roach                break;
499*2d686e68SGreg Roach        }
500*2d686e68SGreg Roach
501*2d686e68SGreg Roach        // Nothing to search for? Return nothing.
502*2d686e68SGreg Roach        if ($givn_sdx === '' && $surn_sdx === '' && $plac_sdx === '') {
503*2d686e68SGreg Roach            return new Collection;
504*2d686e68SGreg Roach        }
505*2d686e68SGreg Roach
506*2d686e68SGreg Roach        $query = DB::table('individuals')
507*2d686e68SGreg Roach            ->select(['individuals.*'])
508*2d686e68SGreg Roach            ->distinct();
509*2d686e68SGreg Roach
510*2d686e68SGreg Roach        $this->whereTrees($query, 'i_file', $search_trees);
511*2d686e68SGreg Roach
512*2d686e68SGreg Roach        if ($plac_sdx !== '') {
513*2d686e68SGreg Roach            $query->join('placelinks', function (JoinClause $join): void {
514*2d686e68SGreg Roach                $join
515*2d686e68SGreg Roach                    ->on('placelinks.pl_file', '=', 'individuals.i_file')
516*2d686e68SGreg Roach                    ->on('placelinks.pl_gid', '=', 'individuals.i_id');
517*2d686e68SGreg Roach            });
518*2d686e68SGreg Roach            $query->join('places', function (JoinClause $join): void {
519*2d686e68SGreg Roach                $join
520*2d686e68SGreg Roach                    ->on('places.p_file', '=', 'placelinks.pl_file')
521*2d686e68SGreg Roach                    ->on('places.p_id', '=', 'placelinks.pl_p_id');
522*2d686e68SGreg Roach            });
523*2d686e68SGreg Roach
524*2d686e68SGreg Roach            $this->wherePhonetic($query, $plac_field, $plac_sdx);
525*2d686e68SGreg Roach        }
526*2d686e68SGreg Roach
527*2d686e68SGreg Roach        if ($givn_sdx !== '' || $surn_sdx !== '') {
528*2d686e68SGreg Roach            $query->join('name', function (JoinClause $join): void {
529*2d686e68SGreg Roach                $join
530*2d686e68SGreg Roach                    ->on('name.n_file', '=', 'individuals.i_file')
531*2d686e68SGreg Roach                    ->on('name.n_id', '=', 'individuals.i_id');
532*2d686e68SGreg Roach            });
533*2d686e68SGreg Roach
534*2d686e68SGreg Roach            $this->wherePhonetic($query, $givn_field, $givn_sdx);
535*2d686e68SGreg Roach            $this->wherePhonetic($query, $surn_field, $surn_sdx);
536*2d686e68SGreg Roach        }
537*2d686e68SGreg Roach
538*2d686e68SGreg Roach        return $query
539*2d686e68SGreg Roach            ->get()
540*2d686e68SGreg Roach            ->map(Individual::rowMapper())
541*2d686e68SGreg Roach            ->filter(GedcomRecord::accessFilter());
542*2d686e68SGreg Roach    }
54332cd2800SGreg Roach}
544