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