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