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