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