1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2021 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 <https://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Services; 21 22use Closure; 23use Fisharebest\Webtrees\Date; 24use Fisharebest\Webtrees\Exceptions\HttpServiceUnavailableException; 25use Fisharebest\Webtrees\Location; 26use Fisharebest\Webtrees\Registry; 27use Fisharebest\Webtrees\Family; 28use Fisharebest\Webtrees\Gedcom; 29use Fisharebest\Webtrees\GedcomRecord; 30use Fisharebest\Webtrees\I18N; 31use Fisharebest\Webtrees\Individual; 32use Fisharebest\Webtrees\Media; 33use Fisharebest\Webtrees\Note; 34use Fisharebest\Webtrees\Place; 35use Fisharebest\Webtrees\Repository; 36use Fisharebest\Webtrees\Soundex; 37use Fisharebest\Webtrees\Source; 38use Fisharebest\Webtrees\Submission; 39use Fisharebest\Webtrees\Submitter; 40use Fisharebest\Webtrees\Tree; 41use Illuminate\Database\Capsule\Manager as DB; 42use Illuminate\Database\Query\Builder; 43use Illuminate\Database\Query\Expression; 44use Illuminate\Database\Query\JoinClause; 45use Illuminate\Support\Collection; 46use stdClass; 47 48use function addcslashes; 49use function array_filter; 50use function array_map; 51use function array_unique; 52use function explode; 53use function implode; 54use function mb_stripos; 55use function preg_match; 56use function preg_quote; 57use function preg_replace; 58 59use const PHP_INT_MAX; 60 61/** 62 * Search trees for genealogy records. 63 */ 64class SearchService 65{ 66 // Do not attempt to show search results larger than this/ 67 protected const MAX_SEARCH_RESULTS = 5000; 68 69 private TreeService $tree_service; 70 71 /** 72 * SearchService constructor. 73 * 74 * @param TreeService $tree_service 75 */ 76 public function __construct( 77 TreeService $tree_service 78 ) { 79 $this->tree_service = $tree_service; 80 } 81 82 /** 83 * @param Tree[] $trees 84 * @param string[] $search 85 * 86 * @return Collection<Family> 87 */ 88 public function searchFamilies(array $trees, array $search): Collection 89 { 90 $query = DB::table('families'); 91 92 $this->whereTrees($query, 'f_file', $trees); 93 $this->whereSearch($query, 'f_gedcom', $search); 94 95 return $query 96 ->get() 97 ->each($this->rowLimiter()) 98 ->map($this->familyRowMapper()) 99 ->filter(GedcomRecord::accessFilter()) 100 ->filter($this->rawGedcomFilter($search)); 101 } 102 103 /** 104 * Search for families by name. 105 * 106 * @param Tree[] $trees 107 * @param string[] $search 108 * @param int $offset 109 * @param int $limit 110 * 111 * @return Collection<Family> 112 */ 113 public function searchFamilyNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 114 { 115 $query = DB::table('families') 116 ->leftJoin('name AS husb_name', static function (JoinClause $join): void { 117 $join 118 ->on('husb_name.n_file', '=', 'families.f_file') 119 ->on('husb_name.n_id', '=', 'families.f_husb') 120 ->where('husb_name.n_type', '<>', '_MARNM'); 121 }) 122 ->leftJoin('name AS wife_name', static function (JoinClause $join): void { 123 $join 124 ->on('wife_name.n_file', '=', 'families.f_file') 125 ->on('wife_name.n_id', '=', 'families.f_wife') 126 ->where('wife_name.n_type', '<>', '_MARNM'); 127 }); 128 129 $prefix = DB::connection()->getTablePrefix(); 130 $field = new Expression('COALESCE(' . $prefix . "husb_name.n_full, '') || COALESCE(" . $prefix . "wife_name.n_full, '')"); 131 132 $this->whereTrees($query, 'f_file', $trees); 133 $this->whereSearch($query, $field, $search); 134 135 $query 136 ->orderBy('husb_name.n_sort') 137 ->orderBy('wife_name.n_sort') 138 ->select(['families.*', 'husb_name.n_sort', 'wife_name.n_sort']); 139 140 return $this->paginateQuery($query, $this->familyRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 141 } 142 143 /** 144 * @param Place $place 145 * 146 * @return Collection<Family> 147 */ 148 public function searchFamiliesInPlace(Place $place): Collection 149 { 150 return DB::table('families') 151 ->join('placelinks', static function (JoinClause $query) { 152 $query 153 ->on('families.f_file', '=', 'placelinks.pl_file') 154 ->on('families.f_id', '=', 'placelinks.pl_gid'); 155 }) 156 ->where('f_file', '=', $place->tree()->id()) 157 ->where('pl_p_id', '=', $place->id()) 158 ->select(['families.*']) 159 ->get() 160 ->each($this->rowLimiter()) 161 ->map($this->familyRowMapper()) 162 ->filter(GedcomRecord::accessFilter()); 163 } 164 165 /** 166 * @param Tree[] $trees 167 * @param string[] $search 168 * 169 * @return Collection<Individual> 170 */ 171 public function searchIndividuals(array $trees, array $search): Collection 172 { 173 $query = DB::table('individuals'); 174 175 $this->whereTrees($query, 'i_file', $trees); 176 $this->whereSearch($query, 'i_gedcom', $search); 177 178 return $query 179 ->get() 180 ->each($this->rowLimiter()) 181 ->map($this->individualRowMapper()) 182 ->filter(GedcomRecord::accessFilter()) 183 ->filter($this->rawGedcomFilter($search)); 184 } 185 186 /** 187 * Search for individuals by name. 188 * 189 * @param Tree[] $trees 190 * @param string[] $search 191 * @param int $offset 192 * @param int $limit 193 * 194 * @return Collection<Individual> 195 */ 196 public function searchIndividualNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 197 { 198 $query = DB::table('individuals') 199 ->join('name', static function (JoinClause $join): void { 200 $join 201 ->on('name.n_file', '=', 'individuals.i_file') 202 ->on('name.n_id', '=', 'individuals.i_id'); 203 }) 204 ->orderBy('n_sort') 205 ->select(['individuals.*', 'n_sort']); 206 207 $this->whereTrees($query, 'i_file', $trees); 208 $this->whereSearch($query, 'n_full', $search); 209 210 return $this->paginateQuery($query, $this->individualRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 211 } 212 213 /** 214 * @param Place $place 215 * 216 * @return Collection<Individual> 217 */ 218 public function searchIndividualsInPlace(Place $place): Collection 219 { 220 return DB::table('individuals') 221 ->join('placelinks', static function (JoinClause $join) { 222 $join 223 ->on('i_file', '=', 'pl_file') 224 ->on('i_id', '=', 'pl_gid'); 225 }) 226 ->where('i_file', '=', $place->tree()->id()) 227 ->where('pl_p_id', '=', $place->id()) 228 ->select(['individuals.*']) 229 ->get() 230 ->each($this->rowLimiter()) 231 ->map($this->individualRowMapper()) 232 ->filter(GedcomRecord::accessFilter()); 233 } 234 235 /** 236 * Search for submissions. 237 * 238 * @param Tree[] $trees 239 * @param string[] $search 240 * @param int $offset 241 * @param int $limit 242 * 243 * @return Collection<Location> 244 */ 245 public function searchLocations(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 246 { 247 $query = DB::table('other') 248 ->where('o_type', '=', '_LOC'); 249 250 $this->whereTrees($query, 'o_file', $trees); 251 $this->whereSearch($query, 'o_gedcom', $search); 252 253 return $this->paginateQuery($query, $this->locationRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 254 } 255 256 /** 257 * Search for media objects. 258 * 259 * @param Tree[] $trees 260 * @param string[] $search 261 * @param int $offset 262 * @param int $limit 263 * 264 * @return Collection<Media> 265 */ 266 public function searchMedia(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 267 { 268 $query = DB::table('media'); 269 270 $this->whereTrees($query, 'media.m_file', $trees); 271 $this->whereSearch($query, 'm_gedcom', $search); 272 273 return $this->paginateQuery($query, $this->mediaRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 274 } 275 276 /** 277 * Search for notes. 278 * 279 * @param Tree[] $trees 280 * @param string[] $search 281 * @param int $offset 282 * @param int $limit 283 * 284 * @return Collection<Note> 285 */ 286 public function searchNotes(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 287 { 288 $query = DB::table('other') 289 ->where('o_type', '=', 'NOTE'); 290 291 $this->whereTrees($query, 'o_file', $trees); 292 $this->whereSearch($query, 'o_gedcom', $search); 293 294 return $this->paginateQuery($query, $this->noteRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 295 } 296 297 /** 298 * Search for repositories. 299 * 300 * @param Tree[] $trees 301 * @param string[] $search 302 * @param int $offset 303 * @param int $limit 304 * 305 * @return Collection<Repository> 306 */ 307 public function searchRepositories(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 308 { 309 $query = DB::table('other') 310 ->where('o_type', '=', 'REPO'); 311 312 $this->whereTrees($query, 'o_file', $trees); 313 $this->whereSearch($query, 'o_gedcom', $search); 314 315 return $this->paginateQuery($query, $this->repositoryRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 316 } 317 318 /** 319 * Search for sources. 320 * 321 * @param Tree[] $trees 322 * @param string[] $search 323 * @param int $offset 324 * @param int $limit 325 * 326 * @return Collection<Source> 327 */ 328 public function searchSources(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 329 { 330 $query = DB::table('sources'); 331 332 $this->whereTrees($query, 's_file', $trees); 333 $this->whereSearch($query, 's_gedcom', $search); 334 335 return $this->paginateQuery($query, $this->sourceRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 336 } 337 338 /** 339 * Search for sources by name. 340 * 341 * @param Tree[] $trees 342 * @param string[] $search 343 * @param int $offset 344 * @param int $limit 345 * 346 * @return Collection<Source> 347 */ 348 public function searchSourcesByName(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 349 { 350 $query = DB::table('sources') 351 ->orderBy('s_name'); 352 353 $this->whereTrees($query, 's_file', $trees); 354 $this->whereSearch($query, 's_name', $search); 355 356 return $this->paginateQuery($query, $this->sourceRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 357 } 358 359 /** 360 * Search for sources. 361 * 362 * @param Tree[] $trees 363 * @param string[] $search 364 * @param int $offset 365 * @param int $limit 366 * 367 * @return Collection<string> 368 */ 369 public function searchSurnames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 370 { 371 $query = DB::table('name'); 372 373 $this->whereTrees($query, 'n_file', $trees); 374 $this->whereSearch($query, 'n_surname', $search); 375 376 return $query 377 ->groupBy(['n_surname']) 378 ->orderBy('n_surname') 379 ->skip($offset) 380 ->take($limit) 381 ->pluck('n_surname'); 382 } 383 384 /** 385 * Search for submissions. 386 * 387 * @param Tree[] $trees 388 * @param string[] $search 389 * @param int $offset 390 * @param int $limit 391 * 392 * @return Collection<Submission> 393 */ 394 public function searchSubmissions(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 395 { 396 $query = DB::table('other') 397 ->where('o_type', '=', 'SUBN'); 398 399 $this->whereTrees($query, 'o_file', $trees); 400 $this->whereSearch($query, 'o_gedcom', $search); 401 402 return $this->paginateQuery($query, $this->submissionRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 403 } 404 405 /** 406 * Search for submitters. 407 * 408 * @param Tree[] $trees 409 * @param string[] $search 410 * @param int $offset 411 * @param int $limit 412 * 413 * @return Collection<Submitter> 414 */ 415 public function searchSubmitters(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 416 { 417 $query = DB::table('other') 418 ->where('o_type', '=', 'SUBM'); 419 420 $this->whereTrees($query, 'o_file', $trees); 421 $this->whereSearch($query, 'o_gedcom', $search); 422 423 return $this->paginateQuery($query, $this->submitterRowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 424 } 425 426 /** 427 * Search for places. 428 * 429 * @param Tree $tree 430 * @param string $search 431 * @param int $offset 432 * @param int $limit 433 * 434 * @return Collection<Place> 435 */ 436 public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 437 { 438 $query = DB::table('places AS p0') 439 ->where('p0.p_file', '=', $tree->id()) 440 ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id') 441 ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id') 442 ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id') 443 ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id') 444 ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id') 445 ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id') 446 ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id') 447 ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id') 448 ->orderBy('p0.p_place') 449 ->orderBy('p1.p_place') 450 ->orderBy('p2.p_place') 451 ->orderBy('p3.p_place') 452 ->orderBy('p4.p_place') 453 ->orderBy('p5.p_place') 454 ->orderBy('p6.p_place') 455 ->orderBy('p7.p_place') 456 ->orderBy('p8.p_place') 457 ->select([ 458 'p0.p_place AS place0', 459 'p1.p_place AS place1', 460 'p2.p_place AS place2', 461 'p3.p_place AS place3', 462 'p4.p_place AS place4', 463 'p5.p_place AS place5', 464 'p6.p_place AS place6', 465 'p7.p_place AS place7', 466 'p8.p_place AS place8', 467 ]); 468 469 // Filter each level of the hierarchy. 470 foreach (explode(',', $search, 9) as $level => $string) { 471 $query->where('p' . $level . '.p_place', 'LIKE', '%' . addcslashes($string, '\\%_') . '%'); 472 } 473 474 $row_mapper = static function (stdClass $row) use ($tree): Place { 475 $place = implode(', ', array_filter((array) $row)); 476 477 return new Place($place, $tree); 478 }; 479 480 $filter = static function (): bool { 481 return true; 482 }; 483 484 return $this->paginateQuery($query, $row_mapper, $filter, $offset, $limit); 485 } 486 487 /** 488 * @param Tree[] $trees 489 * @param string[] $fields 490 * @param string[] $modifiers 491 * 492 * @return Collection<Individual> 493 */ 494 public function searchIndividualsAdvanced(array $trees, array $fields, array $modifiers): Collection 495 { 496 $fields = array_filter($fields); 497 498 $query = DB::table('individuals') 499 ->select(['individuals.*']) 500 ->distinct(); 501 502 $this->whereTrees($query, 'i_file', $trees); 503 504 // Join the following tables 505 $father_name = false; 506 $mother_name = false; 507 $spouse_family = false; 508 $indi_name = false; 509 $indi_dates = []; 510 $fam_dates = []; 511 $indi_plac = false; 512 $fam_plac = false; 513 514 foreach ($fields as $field_name => $field_value) { 515 if ($field_value !== '') { 516 // Fields can have up to 4 parts, but we only need the first 3 to identify 517 // which tables to select 518 $field_parts = explode(':', $field_name . '::'); 519 520 if ($field_parts[0] === 'FAMC') { 521 // Parent name - FAMC:[HUSB|WIFE]:NAME:[GIVN|SURN] 522 if ($field_parts[1] === 'HUSB') { 523 $father_name = true; 524 } else { 525 $mother_name = true; 526 } 527 } elseif ($field_parts[0] === 'NAME') { 528 // Individual name - NAME:[GIVN|SURN] 529 $indi_name = true; 530 } elseif ($field_parts[0] === 'FAMS') { 531 // Family facts - FAMS:NOTE or FAMS:[FACT]:[DATE|PLAC] 532 $spouse_family = true; 533 if ($field_parts[2] === 'DATE') { 534 $fam_dates[] = $field_parts[1]; 535 } elseif ($field_parts[2] === 'PLAC') { 536 $fam_plac = true; 537 } 538 } else { 539 // Individual facts - [FACT] or [FACT]:[DATE|PLAC] 540 if ($field_parts[1] === 'DATE') { 541 $indi_dates[] = $field_parts[0]; 542 } elseif ($field_parts[1] === 'PLAC') { 543 $indi_plac = true; 544 } 545 } 546 } 547 } 548 549 if ($father_name || $mother_name) { 550 $query->join('link AS l1', static function (JoinClause $join): void { 551 $join 552 ->on('l1.l_file', '=', 'individuals.i_file') 553 ->on('l1.l_from', '=', 'individuals.i_id') 554 ->where('l1.l_type', '=', 'FAMC'); 555 }); 556 557 if ($father_name) { 558 $query->join('link AS l2', static function (JoinClause $join): void { 559 $join 560 ->on('l2.l_file', '=', 'l1.l_file') 561 ->on('l2.l_from', '=', 'l1.l_to') 562 ->where('l2.l_type', '=', 'HUSB'); 563 }); 564 $query->join('name AS father_name', static function (JoinClause $join): void { 565 $join 566 ->on('father_name.n_file', '=', 'l2.l_file') 567 ->on('father_name.n_id', '=', 'l2.l_to'); 568 }); 569 } 570 571 if ($mother_name) { 572 $query->join('link AS l3', static function (JoinClause $join): void { 573 $join 574 ->on('l3.l_file', '=', 'l1.l_file') 575 ->on('l3.l_from', '=', 'l1.l_to') 576 ->where('l3.l_type', '=', 'WIFE'); 577 }); 578 $query->join('name AS mother_name', static function (JoinClause $join): void { 579 $join 580 ->on('mother_name.n_file', '=', 'l3.l_file') 581 ->on('mother_name.n_id', '=', 'l3.l_to'); 582 }); 583 } 584 } 585 586 if ($spouse_family) { 587 $query->join('link AS l4', static function (JoinClause $join): void { 588 $join 589 ->on('l4.l_file', '=', 'individuals.i_file') 590 ->on('l4.l_from', '=', 'individuals.i_id') 591 ->where('l4.l_type', '=', 'FAMS'); 592 }); 593 $query->join('families AS spouse_families', static function (JoinClause $join): void { 594 $join 595 ->on('spouse_families.f_file', '=', 'l4.l_file') 596 ->on('spouse_families.f_id', '=', 'l4.l_to'); 597 }); 598 } 599 600 if ($indi_name) { 601 $query->join('name AS individual_name', static function (JoinClause $join): void { 602 $join 603 ->on('individual_name.n_file', '=', 'individuals.i_file') 604 ->on('individual_name.n_id', '=', 'individuals.i_id'); 605 }); 606 } 607 608 foreach (array_unique($indi_dates) as $indi_date) { 609 $query->join('dates AS date_' . $indi_date, static function (JoinClause $join) use ($indi_date): void { 610 $join 611 ->on('date_' . $indi_date . '.d_file', '=', 'individuals.i_file') 612 ->on('date_' . $indi_date . '.d_gid', '=', 'individuals.i_id'); 613 }); 614 } 615 616 foreach (array_unique($fam_dates) as $fam_date) { 617 $query->join('dates AS date_' . $fam_date, static function (JoinClause $join) use ($fam_date): void { 618 $join 619 ->on('date_' . $fam_date . '.d_file', '=', 'spouse_families.f_file') 620 ->on('date_' . $fam_date . '.d_gid', '=', 'spouse_families.f_id'); 621 }); 622 } 623 624 if ($indi_plac) { 625 $query->join('placelinks AS individual_placelinks', static function (JoinClause $join): void { 626 $join 627 ->on('individual_placelinks.pl_file', '=', 'individuals.i_file') 628 ->on('individual_placelinks.pl_gid', '=', 'individuals.i_id'); 629 }); 630 $query->join('places AS individual_places', static function (JoinClause $join): void { 631 $join 632 ->on('individual_places.p_file', '=', 'individual_placelinks.pl_file') 633 ->on('individual_places.p_id', '=', 'individual_placelinks.pl_p_id'); 634 }); 635 } 636 637 if ($fam_plac) { 638 $query->join('placelinks AS familyl_placelinks', static function (JoinClause $join): void { 639 $join 640 ->on('familyl_placelinks.pl_file', '=', 'individuals.i_file') 641 ->on('familyl_placelinks.pl_gid', '=', 'individuals.i_id'); 642 }); 643 $query->join('places AS family_places', static function (JoinClause $join): void { 644 $join 645 ->on('family_places.p_file', '=', 'familyl_placelinks.pl_file') 646 ->on('family_places.p_id', '=', 'familyl_placelinks.pl_p_id'); 647 }); 648 } 649 650 foreach ($fields as $field_name => $field_value) { 651 $parts = explode(':', $field_name . ':::'); 652 if ($parts[0] === 'NAME') { 653 // NAME:* 654 switch ($parts[1]) { 655 case 'GIVN': 656 switch ($modifiers[$field_name]) { 657 case 'EXACT': 658 $query->where('individual_name.n_givn', '=', $field_value); 659 break; 660 case 'BEGINS': 661 $query->where('individual_name.n_givn', 'LIKE', $field_value . '%'); 662 break; 663 case 'CONTAINS': 664 $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%'); 665 break; 666 case 'SDX_STD': 667 $sdx = Soundex::russell($field_value); 668 if ($sdx !== '') { 669 $this->wherePhonetic($query, 'individual_name.n_soundex_givn_std', $sdx); 670 } else { 671 // No phonetic content? Use a substring match 672 $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%'); 673 } 674 break; 675 case 'SDX': // SDX uses DM by default. 676 case 'SDX_DM': 677 $sdx = Soundex::daitchMokotoff($field_value); 678 if ($sdx !== '') { 679 $this->wherePhonetic($query, 'individual_name.n_soundex_givn_dm', $sdx); 680 } else { 681 // No phonetic content? Use a substring match 682 $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%'); 683 } 684 break; 685 } 686 unset($fields[$field_name]); 687 break; 688 case 'SURN': 689 switch ($modifiers[$field_name]) { 690 case 'EXACT': 691 $query->where(function (Builder $query) use ($field_value): void { 692 $query 693 ->where('individual_name.n_surn', '=', $field_value) 694 ->orWhere('individual_name.n_surname', '=', $field_value); 695 }); 696 break; 697 case 'BEGINS': 698 $query->where(function (Builder $query) use ($field_value): void { 699 $query 700 ->where('individual_name.n_surn', 'LIKE', $field_value . '%') 701 ->orWhere('individual_name.n_surname', 'LIKE', $field_value . '%'); 702 }); 703 break; 704 case 'CONTAINS': 705 $query->where(function (Builder $query) use ($field_value): void { 706 $query 707 ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%') 708 ->orWhere('individual_name.n_surname', 'LIKE', '%' . $field_value . '%'); 709 }); 710 break; 711 case 'SDX_STD': 712 $sdx = Soundex::russell($field_value); 713 if ($sdx !== '') { 714 $this->wherePhonetic($query, 'individual_name.n_soundex_surn_std', $sdx); 715 } else { 716 // No phonetic content? Use a substring match 717 $query->where(function (Builder $query) use ($field_value): void { 718 $query 719 ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%') 720 ->orWhere('individual_name.n_surname', 'LIKE', '%' . $field_value . '%'); 721 }); 722 } 723 break; 724 case 'SDX': // SDX uses DM by default. 725 case 'SDX_DM': 726 $sdx = Soundex::daitchMokotoff($field_value); 727 if ($sdx !== '') { 728 $this->wherePhonetic($query, 'individual_name.n_soundex_surn_dm', $sdx); 729 } else { 730 // No phonetic content? Use a substring match 731 $query->where(function (Builder $query) use ($field_value): void { 732 $query 733 ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%') 734 ->orWhere('individual_name.n_surname', 'LIKE', '%' . $field_value . '%'); 735 }); 736 } 737 break; 738 } 739 unset($fields[$field_name]); 740 break; 741 case 'NICK': 742 case '_MARNM': 743 case '_HEB': 744 case '_AKA': 745 $like = "%\n1 " . $parts[0] . "%\n2 " . $parts[1] . ' %' . preg_quote($field_value, '/') . '%'; 746 $query->where('individuals.i_gedcom', 'LIKE', $like); 747 break; 748 } 749 } elseif ($parts[1] === 'DATE') { 750 // *:DATE 751 $date = new Date($field_value); 752 if ($date->isOK()) { 753 $delta = 365 * ($modifiers[$field_name] ?? 0); 754 $query 755 ->where('date_' . $parts[0] . '.d_fact', '=', $parts[0]) 756 ->where('date_' . $parts[0] . '.d_julianday1', '>=', $date->minimumJulianDay() - $delta) 757 ->where('date_' . $parts[0] . '.d_julianday2', '<=', $date->maximumJulianDay() + $delta); 758 } 759 unset($fields[$field_name]); 760 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'DATE') { 761 // FAMS:*:DATE 762 $date = new Date($field_value); 763 if ($date->isOK()) { 764 $delta = 365 * $modifiers[$field_name]; 765 $query 766 ->where('date_' . $parts[1] . '.d_fact', '=', $parts[1]) 767 ->where('date_' . $parts[1] . '.d_julianday1', '>=', $date->minimumJulianDay() - $delta) 768 ->where('date_' . $parts[1] . '.d_julianday2', '<=', $date->maximumJulianDay() + $delta); 769 } 770 unset($fields[$field_name]); 771 } elseif ($parts[1] === 'PLAC') { 772 // *:PLAC 773 // SQL can only link a place to a person/family, not to an event. 774 $query->where('individual_places.p_place', 'LIKE', '%' . $field_value . '%'); 775 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 776 // FAMS:*:PLAC 777 // SQL can only link a place to a person/family, not to an event. 778 $query->where('family_places.p_place', 'LIKE', '%' . $field_value . '%'); 779 } elseif ($parts[0] === 'FAMC' && $parts[2] === 'NAME') { 780 $table = $parts[1] === 'HUSB' ? 'father_name' : 'mother_name'; 781 // NAME:* 782 switch ($parts[3]) { 783 case 'GIVN': 784 switch ($modifiers[$field_name]) { 785 case 'EXACT': 786 $query->where($table . '.n_givn', '=', $field_value); 787 break; 788 case 'BEGINS': 789 $query->where($table . '.n_givn', 'LIKE', $field_value . '%'); 790 break; 791 case 'CONTAINS': 792 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 793 break; 794 case 'SDX_STD': 795 $sdx = Soundex::russell($field_value); 796 if ($sdx !== '') { 797 $this->wherePhonetic($query, $table . '.n_soundex_givn_std', $sdx); 798 } else { 799 // No phonetic content? Use a substring match 800 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 801 } 802 break; 803 case 'SDX': // SDX uses DM by default. 804 case 'SDX_DM': 805 $sdx = Soundex::daitchMokotoff($field_value); 806 if ($sdx !== '') { 807 $this->wherePhonetic($query, $table . '.n_soundex_givn_dm', $sdx); 808 } else { 809 // No phonetic content? Use a substring match 810 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 811 } 812 break; 813 } 814 break; 815 case 'SURN': 816 switch ($modifiers[$field_name]) { 817 case 'EXACT': 818 $query->where($table . '.n_surn', '=', $field_value); 819 break; 820 case 'BEGINS': 821 $query->where($table . '.n_surn', 'LIKE', $field_value . '%'); 822 break; 823 case 'CONTAINS': 824 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 825 break; 826 case 'SDX_STD': 827 $sdx = Soundex::russell($field_value); 828 if ($sdx !== '') { 829 $this->wherePhonetic($query, $table . '.n_soundex_surn_std', $sdx); 830 } else { 831 // No phonetic content? Use a substring match 832 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 833 } 834 break; 835 case 'SDX': // SDX uses DM by default. 836 case 'SDX_DM': 837 $sdx = Soundex::daitchMokotoff($field_value); 838 if ($sdx !== '') { 839 $this->wherePhonetic($query, $table . '.n_soundex_surn_dm', $sdx); 840 } else { 841 // No phonetic content? Use a substring match 842 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 843 } 844 break; 845 } 846 break; 847 } 848 unset($fields[$field_name]); 849 } elseif ($parts[0] === 'FAMS') { 850 // e.g. searches for occupation, religion, note, etc. 851 // Initial matching only. Need PHP to apply filter. 852 $query->where('spouse_families.f_gedcom', 'LIKE', "%\n1 " . $parts[1] . ' %' . $field_value . '%'); 853 } elseif ($parts[1] === 'TYPE') { 854 // e.g. FACT:TYPE or EVEN:TYPE 855 // Initial matching only. Need PHP to apply filter. 856 $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . "%\n2 TYPE %" . $field_value . '%'); 857 } else { 858 // e.g. searches for occupation, religion, note, etc. 859 // Initial matching only. Need PHP to apply filter. 860 $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . '%' . $parts[1] . '%' . $field_value . '%'); 861 } 862 } 863 864 return $query 865 ->get() 866 ->each($this->rowLimiter()) 867 ->map($this->individualRowMapper()) 868 ->filter(GedcomRecord::accessFilter()) 869 ->filter(static function (Individual $individual) use ($fields): bool { 870 // Check for searches which were only partially matched by SQL 871 foreach ($fields as $field_name => $field_value) { 872 $parts = explode(':', $field_name . '::::'); 873 874 // NAME:* 875 if ($parts[0] === 'NAME') { 876 $regex = '/\n1 NAME.*(?:\n2.*)*\n2 ' . $parts[1] . ' .*' . preg_quote($field_value, '/') . '/i'; 877 878 if (preg_match($regex, $individual->gedcom())) { 879 continue; 880 } 881 882 return false; 883 } 884 885 $regex = '/' . preg_quote($field_value, '/') . '/i'; 886 887 // *:PLAC 888 if ($parts[1] === 'PLAC') { 889 foreach ($individual->facts([$parts[0]]) as $fact) { 890 if (preg_match($regex, $fact->place()->gedcomName())) { 891 continue 2; 892 } 893 } 894 return false; 895 } 896 897 // FAMS:*:PLAC 898 if ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 899 foreach ($individual->spouseFamilies() as $family) { 900 foreach ($family->facts([$parts[1]]) as $fact) { 901 if (preg_match($regex, $fact->place()->gedcomName())) { 902 continue 3; 903 } 904 } 905 } 906 return false; 907 } 908 909 // e.g. searches for occupation, religion, note, etc. 910 if ($parts[0] === 'FAMS') { 911 foreach ($individual->spouseFamilies() as $family) { 912 foreach ($family->facts([$parts[1]]) as $fact) { 913 if (preg_match($regex, $fact->value())) { 914 continue 3; 915 } 916 } 917 } 918 return false; 919 } 920 921 // e.g. FACT:TYPE or EVEN:TYPE 922 if ($parts[1] === 'TYPE' || $parts[1] === '_WT_USER') { 923 foreach ($individual->facts([$parts[0]]) as $fact) { 924 if (preg_match($regex, $fact->attribute($parts[1]))) { 925 continue 2; 926 } 927 } 928 929 return false; 930 } 931 } 932 933 return true; 934 }); 935 } 936 937 /** 938 * @param string $soundex 939 * @param string $lastname 940 * @param string $firstname 941 * @param string $place 942 * @param Tree[] $search_trees 943 * 944 * @return Collection<Individual> 945 */ 946 public function searchIndividualsPhonetic(string $soundex, string $lastname, string $firstname, string $place, array $search_trees): Collection 947 { 948 switch ($soundex) { 949 default: 950 case 'Russell': 951 $givn_sdx = Soundex::russell($firstname); 952 $surn_sdx = Soundex::russell($lastname); 953 $plac_sdx = Soundex::russell($place); 954 $givn_field = 'n_soundex_givn_std'; 955 $surn_field = 'n_soundex_surn_std'; 956 $plac_field = 'p_std_soundex'; 957 break; 958 case 'DaitchM': 959 $givn_sdx = Soundex::daitchMokotoff($firstname); 960 $surn_sdx = Soundex::daitchMokotoff($lastname); 961 $plac_sdx = Soundex::daitchMokotoff($place); 962 $givn_field = 'n_soundex_givn_dm'; 963 $surn_field = 'n_soundex_surn_dm'; 964 $plac_field = 'p_dm_soundex'; 965 break; 966 } 967 968 // Nothing to search for? Return nothing. 969 if ($givn_sdx === '' && $surn_sdx === '' && $plac_sdx === '') { 970 return new Collection(); 971 } 972 973 $query = DB::table('individuals') 974 ->select(['individuals.*']) 975 ->distinct(); 976 977 $this->whereTrees($query, 'i_file', $search_trees); 978 979 if ($plac_sdx !== '') { 980 $query->join('placelinks', static function (JoinClause $join): void { 981 $join 982 ->on('placelinks.pl_file', '=', 'individuals.i_file') 983 ->on('placelinks.pl_gid', '=', 'individuals.i_id'); 984 }); 985 $query->join('places', static function (JoinClause $join): void { 986 $join 987 ->on('places.p_file', '=', 'placelinks.pl_file') 988 ->on('places.p_id', '=', 'placelinks.pl_p_id'); 989 }); 990 991 $this->wherePhonetic($query, $plac_field, $plac_sdx); 992 } 993 994 if ($givn_sdx !== '' || $surn_sdx !== '') { 995 $query->join('name', static function (JoinClause $join): void { 996 $join 997 ->on('name.n_file', '=', 'individuals.i_file') 998 ->on('name.n_id', '=', 'individuals.i_id'); 999 }); 1000 1001 $this->wherePhonetic($query, $givn_field, $givn_sdx); 1002 $this->wherePhonetic($query, $surn_field, $surn_sdx); 1003 } 1004 1005 return $query 1006 ->get() 1007 ->each($this->rowLimiter()) 1008 ->map($this->individualRowMapper()) 1009 ->filter(GedcomRecord::accessFilter()); 1010 } 1011 1012 /** 1013 * Paginate a search query. 1014 * 1015 * @param Builder $query Searches the database for the desired records. 1016 * @param Closure $row_mapper Converts a row from the query into a record. 1017 * @param Closure $row_filter 1018 * @param int $offset Skip this many rows. 1019 * @param int $limit Take this many rows. 1020 * 1021 * @return Collection<mixed> 1022 */ 1023 private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection 1024 { 1025 $collection = new Collection(); 1026 1027 foreach ($query->cursor() as $row) { 1028 $record = $row_mapper($row); 1029 // searchIndividualNames() and searchFamilyNames() can return duplicate rows, 1030 // where individuals have multiple names - and we need to sort results by name. 1031 if ($collection->containsStrict($record)) { 1032 continue; 1033 } 1034 // If the object has a method "canShow()", then use it to filter for privacy. 1035 if ($row_filter($record)) { 1036 if ($offset > 0) { 1037 $offset--; 1038 } else { 1039 if ($limit > 0) { 1040 $collection->push($record); 1041 } 1042 1043 $limit--; 1044 1045 if ($limit === 0) { 1046 break; 1047 } 1048 } 1049 } 1050 } 1051 1052 1053 return $collection; 1054 } 1055 1056 /** 1057 * Apply search filters to a SQL query column. Apply collation rules to MySQL. 1058 * 1059 * @param Builder $query 1060 * @param Expression|string $field 1061 * @param string[] $search_terms 1062 */ 1063 private function whereSearch(Builder $query, $field, array $search_terms): void 1064 { 1065 if ($field instanceof Expression) { 1066 $field = $field->getValue(); 1067 } 1068 1069 foreach ($search_terms as $search_term) { 1070 $query->where(new Expression($field), 'LIKE', '%' . addcslashes($search_term, '\\%_') . '%'); 1071 } 1072 } 1073 1074 /** 1075 * Apply soundex search filters to a SQL query column. 1076 * 1077 * @param Builder $query 1078 * @param Expression|string $field 1079 * @param string $soundex 1080 */ 1081 private function wherePhonetic(Builder $query, $field, string $soundex): void 1082 { 1083 if ($soundex !== '') { 1084 $query->where(static function (Builder $query) use ($soundex, $field): void { 1085 foreach (explode(':', $soundex) as $sdx) { 1086 $query->orWhere($field, 'LIKE', '%' . $sdx . '%'); 1087 } 1088 }); 1089 } 1090 } 1091 1092 /** 1093 * @param Builder $query 1094 * @param string $tree_id_field 1095 * @param Tree[] $trees 1096 */ 1097 private function whereTrees(Builder $query, string $tree_id_field, array $trees): void 1098 { 1099 $tree_ids = array_map(static function (Tree $tree): int { 1100 return $tree->id(); 1101 }, $trees); 1102 1103 $query->whereIn($tree_id_field, $tree_ids); 1104 } 1105 1106 /** 1107 * Find the media object that uses a particular media file. 1108 * 1109 * @param string $file 1110 * 1111 * @return Media[] 1112 */ 1113 public function findMediaObjectsForMediaFile(string $file): array 1114 { 1115 return DB::table('media') 1116 ->join('media_file', static function (JoinClause $join): void { 1117 $join 1118 ->on('media_file.m_file', '=', 'media.m_file') 1119 ->on('media_file.m_id', '=', 'media.m_id'); 1120 }) 1121 ->join('gedcom_setting', 'media.m_file', '=', 'gedcom_setting.gedcom_id') 1122 ->where(new Expression('setting_value || multimedia_file_refn'), '=', $file) 1123 ->select(['media.*']) 1124 ->distinct() 1125 ->get() 1126 ->map($this->mediaRowMapper()) 1127 ->all(); 1128 } 1129 1130 /** 1131 * A closure to filter records by privacy-filtered GEDCOM data. 1132 * 1133 * @param array<string> $search_terms 1134 * 1135 * @return Closure 1136 */ 1137 private function rawGedcomFilter(array $search_terms): Closure 1138 { 1139 return static function (GedcomRecord $record) use ($search_terms): bool { 1140 // Ignore non-genealogy fields 1141 $gedcom = preg_replace('/\n\d (?:_UID|_WT_USER) .*/', '', $record->gedcom()); 1142 1143 // Ignore matches in links 1144 $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom); 1145 1146 // Re-apply the filtering 1147 foreach ($search_terms as $search_term) { 1148 if (mb_stripos($gedcom, $search_term) === false) { 1149 return false; 1150 } 1151 } 1152 1153 return true; 1154 }; 1155 } 1156 1157 /** 1158 * Searching for short or common text can give more results than the system can process. 1159 * 1160 * @param int $limit 1161 * 1162 * @return Closure 1163 */ 1164 private function rowLimiter(int $limit = self::MAX_SEARCH_RESULTS): Closure 1165 { 1166 return static function () use ($limit): void { 1167 static $n = 0; 1168 1169 if (++$n > $limit) { 1170 $message = I18N::translate('The search returned too many results.'); 1171 1172 throw new HttpServiceUnavailableException($message); 1173 } 1174 }; 1175 } 1176 1177 /** 1178 * Convert a row from any tree in the families table into a family object. 1179 * 1180 * @return Closure 1181 */ 1182 private function familyRowMapper(): Closure 1183 { 1184 return function (stdClass $row): Family { 1185 $tree = $this->tree_service->find((int) $row->f_file); 1186 1187 return Registry::familyFactory()->mapper($tree)($row); 1188 }; 1189 } 1190 1191 /** 1192 * Convert a row from any tree in the individuals table into an individual object. 1193 * 1194 * @return Closure 1195 */ 1196 private function individualRowMapper(): Closure 1197 { 1198 return function (stdClass $row): Individual { 1199 $tree = $this->tree_service->find((int) $row->i_file); 1200 1201 return Registry::individualFactory()->mapper($tree)($row); 1202 }; 1203 } 1204 1205 /** 1206 * Convert a row from any tree in the media table into a location object. 1207 * 1208 * @return Closure 1209 */ 1210 private function locationRowMapper(): Closure 1211 { 1212 return function (stdClass $row): Location { 1213 $tree = $this->tree_service->find((int) $row->o_file); 1214 1215 return Registry::locationFactory()->mapper($tree)($row); 1216 }; 1217 } 1218 1219 /** 1220 * Convert a row from any tree in the media table into an media object. 1221 * 1222 * @return Closure 1223 */ 1224 private function mediaRowMapper(): Closure 1225 { 1226 return function (stdClass $row): Media { 1227 $tree = $this->tree_service->find((int) $row->m_file); 1228 1229 return Registry::mediaFactory()->mapper($tree)($row); 1230 }; 1231 } 1232 1233 /** 1234 * Convert a row from any tree in the other table into a note object. 1235 * 1236 * @return Closure 1237 */ 1238 private function noteRowMapper(): Closure 1239 { 1240 return function (stdClass $row): Note { 1241 $tree = $this->tree_service->find((int) $row->o_file); 1242 1243 return Registry::noteFactory()->mapper($tree)($row); 1244 }; 1245 } 1246 1247 /** 1248 * Convert a row from any tree in the other table into a repository object. 1249 * 1250 * @return Closure 1251 */ 1252 private function repositoryRowMapper(): Closure 1253 { 1254 return function (stdClass $row): Repository { 1255 $tree = $this->tree_service->find((int) $row->o_file); 1256 1257 return Registry::repositoryFactory()->mapper($tree)($row); 1258 }; 1259 } 1260 1261 /** 1262 * Convert a row from any tree in the sources table into a source object. 1263 * 1264 * @return Closure 1265 */ 1266 private function sourceRowMapper(): Closure 1267 { 1268 return function (stdClass $row): Source { 1269 $tree = $this->tree_service->find((int) $row->s_file); 1270 1271 return Registry::sourceFactory()->mapper($tree)($row); 1272 }; 1273 } 1274 1275 /** 1276 * Convert a row from any tree in the other table into a submission object. 1277 * 1278 * @return Closure 1279 */ 1280 private function submissionRowMapper(): Closure 1281 { 1282 return function (stdClass $row): Submission { 1283 $tree = $this->tree_service->find((int) $row->o_file); 1284 1285 return Registry::submissionFactory()->mapper($tree)($row); 1286 }; 1287 } 1288 1289 /** 1290 * Convert a row from any tree in the other table into a submitter object. 1291 * 1292 * @return Closure 1293 */ 1294 private function submitterRowMapper(): Closure 1295 { 1296 return function (stdClass $row): Submitter { 1297 $tree = $this->tree_service->find((int) $row->o_file); 1298 1299 return Registry::submitterFactory()->mapper($tree)($row); 1300 }; 1301 } 1302} 1303