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