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(function (Builder $query) use ($field_value): void { 603 $query 604 ->where('individual_name.n_surn', '=', $field_value) 605 ->orWhere('individual_name.n_surname', '=', $field_value); 606 }); 607 break; 608 case 'BEGINS': 609 $query->where(function (Builder $query) use ($field_value): void { 610 $query 611 ->where('individual_name.n_surn', 'LIKE', $field_value . '%') 612 ->orWhere('individual_name.n_surn', 'LIKE', $field_value . '%'); 613 }); 614 break; 615 case 'CONTAINS': 616 $query->where(function (Builder $query) use ($field_value): void { 617 $query 618 ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%') 619 ->orWhere('individual_name.n_surn', 'LIKE', '%' . $field_value . '%'); 620 }); 621 break; 622 case 'SDX_STD': 623 $sdx = Soundex::russell($field_value); 624 if ($sdx !== '') { 625 $this->wherePhonetic($query, 'individual_name.n_soundex_surn_std', $sdx); 626 } else { 627 // No phonetic content? Use a substring match 628 $query->where(function (Builder $query) use ($field_value): void { 629 $query 630 ->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%') 631 ->orWhere('individual_name.n_surn', 'LIKE', '%' . $field_value . '%'); 632 }); 633 } 634 break; 635 case 'SDX': // SDX uses DM by default. 636 case 'SDX_DM': 637 $sdx = Soundex::daitchMokotoff($field_value); 638 if ($sdx !== '') { 639 $this->wherePhonetic($query, 'individual_name.n_soundex_surn_dm', $sdx); 640 } else { 641 // No phonetic content? Use a substring match 642 $query->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%'); 643 } 644 break; 645 } 646 break; 647 case 'NICK': 648 case '_MARNM': 649 case '_HEB': 650 case '_AKA': 651 $query 652 ->where('individual_name', '=', $parts[1]) 653 ->where('individual_name', 'LIKE', '%' . $field_value . '%'); 654 break; 655 } 656 unset($fields[$field_name]); 657 } elseif ($parts[1] === 'DATE') { 658 // *:DATE 659 $date = new Date($field_value); 660 if ($date->isOK()) { 661 $delta = 365 * ($modifiers[$field_name] ?? 0); 662 $query 663 ->where('individual_dates.d_fact', '=', $parts[0]) 664 ->where('individual_dates.d_julianday1', '>=', $date->minimumJulianDay() - $delta) 665 ->where('individual_dates.d_julianday2', '<=', $date->minimumJulianDay() + $delta); 666 } 667 unset($fields[$field_name]); 668 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'DATE') { 669 // FAMS:*:DATE 670 $date = new Date($field_value); 671 if ($date->isOK()) { 672 $delta = 365 * $modifiers[$field_name]; 673 $query 674 ->where('family_dates.d_fact', '=', $parts[1]) 675 ->where('family_dates.d_julianday1', '>=', $date->minimumJulianDay() - $delta) 676 ->where('family_dates.d_julianday2', '<=', $date->minimumJulianDay() + $delta); 677 } 678 unset($fields[$field_name]); 679 } elseif ($parts[1] === 'PLAC') { 680 // *:PLAC 681 // SQL can only link a place to a person/family, not to an event. 682 $query->where('individual_places.p_place', 'LIKE', '%' . $field_value . '%'); 683 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 684 // FAMS:*:PLAC 685 // SQL can only link a place to a person/family, not to an event. 686 $query->where('family_places.p_place', 'LIKE', '%' . $field_value . '%'); 687 } elseif ($parts[0] === 'FAMC' && $parts[2] === 'NAME') { 688 $table = $parts[1] === 'HUSB' ? 'father_name' : 'mother_name'; 689 // NAME:* 690 switch ($parts[3]) { 691 case 'GIVN': 692 switch ($modifiers[$field_name]) { 693 case 'EXACT': 694 $query->where($table . '.n_givn', '=', $field_value); 695 break; 696 case 'BEGINS': 697 $query->where($table . '.n_givn', 'LIKE', $field_value . '%'); 698 break; 699 case 'CONTAINS': 700 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 701 break; 702 case 'SDX_STD': 703 $sdx = Soundex::russell($field_value); 704 if ($sdx !== '') { 705 $this->wherePhonetic($query, $table . '.n_soundex_givn_std', $sdx); 706 } else { 707 // No phonetic content? Use a substring match 708 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 709 } 710 break; 711 case 'SDX': // SDX uses DM by default. 712 case 'SDX_DM': 713 $sdx = Soundex::daitchMokotoff($field_value); 714 if ($sdx !== '') { 715 $this->wherePhonetic($query, $table . '.n_soundex_givn_dm', $sdx); 716 } else { 717 // No phonetic content? Use a substring match 718 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 719 } 720 break; 721 } 722 break; 723 case 'SURN': 724 switch ($modifiers[$field_name]) { 725 case 'EXACT': 726 $query->where($table . '.n_surn', '=', $field_value); 727 break; 728 case 'BEGINS': 729 $query->where($table . '.n_surn', 'LIKE', $field_value . '%'); 730 break; 731 case 'CONTAINS': 732 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 733 break; 734 case 'SDX_STD': 735 $sdx = Soundex::russell($field_value); 736 if ($sdx !== '') { 737 $this->wherePhonetic($query, $table . '.n_soundex_surn_std', $sdx); 738 } else { 739 // No phonetic content? Use a substring match 740 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 741 } 742 break; 743 case 'SDX': // SDX uses DM by default. 744 case 'SDX_DM': 745 $sdx = Soundex::daitchMokotoff($field_value); 746 if ($sdx !== '') { 747 $this->wherePhonetic($query, $table . '.n_soundex_surn_dm', $sdx); 748 } else { 749 // No phonetic content? Use a substring match 750 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 751 } 752 break; 753 } 754 break; 755 } 756 unset($fields[$field_name]); 757 } elseif ($parts[0] === 'FAMS') { 758 // e.g. searches for occupation, religion, note, etc. 759 // Initial matching only. Need PHP to apply filter. 760 $query->where('spouse_families.f_gedcom', 'LIKE', "%\n1 " . $parts[1] . ' %' . $field_value . '%'); 761 } elseif ($parts[1] === 'TYPE') { 762 // e.g. FACT:TYPE or EVEN:TYPE 763 // Initial matching only. Need PHP to apply filter. 764 $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . '%\n2 TYPE %' . $field_value . '%'); 765 } else { 766 // e.g. searches for occupation, religion, note, etc. 767 // Initial matching only. Need PHP to apply filter. 768 $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . '%' . $parts[1] . '%' . $field_value . '%'); 769 } 770 } 771 return $query 772 ->get() 773 ->each($this->rowLimiter()) 774 ->map($this->individualRowMapper()) 775 ->filter(GedcomRecord::accessFilter()) 776 ->filter(static function (Individual $individual) use ($fields): bool { 777 // Check for searches which were only partially matched by SQL 778 foreach ($fields as $field_name => $field_value) { 779 $regex = '/' . preg_quote($field_value, '/') . '/i'; 780 781 $parts = explode(':', $field_name . '::::'); 782 783 // *:PLAC 784 if ($parts[1] === 'PLAC') { 785 foreach ($individual->facts([$parts[0]]) as $fact) { 786 if (preg_match($regex, $fact->place()->gedcomName())) { 787 continue 2; 788 } 789 } 790 return false; 791 } 792 793 // FAMS:*:PLAC 794 if ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 795 foreach ($individual->spouseFamilies() as $family) { 796 foreach ($family->facts([$parts[1]]) as $fact) { 797 if (preg_match($regex, $fact->place()->gedcomName())) { 798 continue 2; 799 } 800 } 801 } 802 return false; 803 } 804 805 // e.g. searches for occupation, religion, note, etc. 806 if ($parts[0] === 'FAMS') { 807 foreach ($individual->spouseFamilies() as $family) { 808 foreach ($family->facts([$parts[1]]) as $fact) { 809 if (preg_match($regex, $fact->value())) { 810 continue 3; 811 } 812 } 813 } 814 return false; 815 } 816 817 // e.g. FACT:TYPE or EVEN:TYPE 818 if ($parts[1] === 'TYPE' || $parts[1] === '_WT_USER') { 819 foreach ($individual->facts([$parts[0]]) as $fact) { 820 if (preg_match($regex, $fact->attribute($parts[1]))) { 821 continue 2; 822 } 823 } 824 825 return false; 826 } 827 } 828 829 return true; 830 }); 831 } 832 833 /** 834 * @param string $soundex 835 * @param string $lastname 836 * @param string $firstname 837 * @param string $place 838 * @param Tree[] $search_trees 839 * 840 * @return Collection<Individual> 841 */ 842 public function searchIndividualsPhonetic(string $soundex, string $lastname, string $firstname, string $place, array $search_trees): Collection 843 { 844 switch ($soundex) { 845 default: 846 case 'Russell': 847 $givn_sdx = Soundex::russell($firstname); 848 $surn_sdx = Soundex::russell($lastname); 849 $plac_sdx = Soundex::russell($place); 850 $givn_field = 'n_soundex_givn_std'; 851 $surn_field = 'n_soundex_surn_std'; 852 $plac_field = 'p_std_soundex'; 853 break; 854 case 'DaitchM': 855 $givn_sdx = Soundex::daitchMokotoff($firstname); 856 $surn_sdx = Soundex::daitchMokotoff($lastname); 857 $plac_sdx = Soundex::daitchMokotoff($place); 858 $givn_field = 'n_soundex_givn_dm'; 859 $surn_field = 'n_soundex_surn_dm'; 860 $plac_field = 'p_dm_soundex'; 861 break; 862 } 863 864 // Nothing to search for? Return nothing. 865 if ($givn_sdx === '' && $surn_sdx === '' && $plac_sdx === '') { 866 return new Collection(); 867 } 868 869 $query = DB::table('individuals') 870 ->select(['individuals.*']) 871 ->distinct(); 872 873 $this->whereTrees($query, 'i_file', $search_trees); 874 875 if ($plac_sdx !== '') { 876 $query->join('placelinks', static function (JoinClause $join): void { 877 $join 878 ->on('placelinks.pl_file', '=', 'individuals.i_file') 879 ->on('placelinks.pl_gid', '=', 'individuals.i_id'); 880 }); 881 $query->join('places', static function (JoinClause $join): void { 882 $join 883 ->on('places.p_file', '=', 'placelinks.pl_file') 884 ->on('places.p_id', '=', 'placelinks.pl_p_id'); 885 }); 886 887 $this->wherePhonetic($query, $plac_field, $plac_sdx); 888 } 889 890 if ($givn_sdx !== '' || $surn_sdx !== '') { 891 $query->join('name', static function (JoinClause $join): void { 892 $join 893 ->on('name.n_file', '=', 'individuals.i_file') 894 ->on('name.n_id', '=', 'individuals.i_id'); 895 }); 896 897 $this->wherePhonetic($query, $givn_field, $givn_sdx); 898 $this->wherePhonetic($query, $surn_field, $surn_sdx); 899 } 900 901 return $query 902 ->get() 903 ->each($this->rowLimiter()) 904 ->map($this->individualRowMapper()) 905 ->filter(GedcomRecord::accessFilter()); 906 } 907 908 /** 909 * Paginate a search query. 910 * 911 * @param Builder $query Searches the database for the desired records. 912 * @param Closure $row_mapper Converts a row from the query into a record. 913 * @param Closure $row_filter 914 * @param int $offset Skip this many rows. 915 * @param int $limit Take this many rows. 916 * 917 * @return Collection<mixed> 918 */ 919 private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection 920 { 921 $collection = new Collection(); 922 923 foreach ($query->cursor() as $row) { 924 $record = $row_mapper($row); 925 // If the object has a method "canShow()", then use it to filter for privacy. 926 if ($row_filter($record)) { 927 if ($offset > 0) { 928 $offset--; 929 } else { 930 if ($limit > 0) { 931 $collection->push($record); 932 } 933 934 $limit--; 935 936 if ($limit === 0) { 937 break; 938 } 939 } 940 } 941 } 942 943 944 return $collection; 945 } 946 947 /** 948 * Apply search filters to a SQL query column. Apply collation rules to MySQL. 949 * 950 * @param Builder $query 951 * @param Expression|string $field 952 * @param string[] $search_terms 953 */ 954 private function whereSearch(Builder $query, $field, array $search_terms): void 955 { 956 if ($field instanceof Expression) { 957 $field = $field->getValue(); 958 } 959 960 foreach ($search_terms as $search_term) { 961 $query->whereContains(new Expression($field), $search_term); 962 } 963 } 964 965 /** 966 * Apply soundex search filters to a SQL query column. 967 * 968 * @param Builder $query 969 * @param Expression|string $field 970 * @param string $soundex 971 */ 972 private function wherePhonetic(Builder $query, $field, string $soundex): void 973 { 974 if ($soundex !== '') { 975 $query->where(static function (Builder $query) use ($soundex, $field): void { 976 foreach (explode(':', $soundex) as $sdx) { 977 $query->orWhere($field, 'LIKE', '%' . $sdx . '%'); 978 } 979 }); 980 } 981 } 982 983 /** 984 * @param Builder $query 985 * @param string $tree_id_field 986 * @param Tree[] $trees 987 */ 988 private function whereTrees(Builder $query, string $tree_id_field, array $trees): void 989 { 990 $tree_ids = array_map(static function (Tree $tree): int { 991 return $tree->id(); 992 }, $trees); 993 994 $query->whereIn($tree_id_field, $tree_ids); 995 } 996 997 /** 998 * Find the media object that uses a particular media file. 999 * 1000 * @param string $file 1001 * 1002 * @return Media[] 1003 */ 1004 public function findMediaObjectsForMediaFile(string $file): array 1005 { 1006 return DB::table('media') 1007 ->join('media_file', static function (JoinClause $join): void { 1008 $join 1009 ->on('media_file.m_file', '=', 'media.m_file') 1010 ->on('media_file.m_id', '=', 'media.m_id'); 1011 }) 1012 ->join('gedcom_setting', 'media.m_file', '=', 'gedcom_setting.gedcom_id') 1013 ->where(new Expression('setting_value || multimedia_file_refn'), '=', $file) 1014 ->select(['media.*']) 1015 ->distinct() 1016 ->get() 1017 ->map($this->mediaRowMapper()) 1018 ->all(); 1019 } 1020 1021 /** 1022 * A closure to filter records by privacy-filtered GEDCOM data. 1023 * 1024 * @param array $search_terms 1025 * 1026 * @return Closure 1027 */ 1028 private function rawGedcomFilter(array $search_terms): Closure 1029 { 1030 return static function (GedcomRecord $record) use ($search_terms): bool { 1031 // Ignore non-genealogy fields 1032 $gedcom = preg_replace('/\n\d (?:_UID|_WT_USER) .*/', '', $record->gedcom()); 1033 1034 // Ignore matches in links 1035 $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom); 1036 1037 // Re-apply the filtering 1038 foreach ($search_terms as $search_term) { 1039 if (mb_stripos($gedcom, $search_term) === false) { 1040 return false; 1041 } 1042 } 1043 1044 return true; 1045 }; 1046 } 1047 1048 /** 1049 * Searching for short or common text can give more results than the system can process. 1050 * 1051 * @param int $limit 1052 * 1053 * @return Closure 1054 */ 1055 private function rowLimiter(int $limit = 1000): Closure 1056 { 1057 return static function () use ($limit): void { 1058 static $n = 0; 1059 1060 if (++$n > $limit) { 1061 $message = I18N::translate('The search returned too many results.'); 1062 1063 throw new HttpServiceUnavailableException($message); 1064 } 1065 }; 1066 } 1067 1068 /** 1069 * Convert a row from any tree in the families table into a family object. 1070 * 1071 * @return Closure 1072 */ 1073 private function familyRowMapper(): Closure 1074 { 1075 return function (stdClass $row): Family { 1076 $tree = $this->tree_service->find((int) $row->f_file); 1077 1078 return Family::rowMapper($tree)($row); 1079 }; 1080 } 1081 1082 /** 1083 * Convert a row from any tree in the individuals table into an individual object. 1084 * 1085 * @return Closure 1086 */ 1087 private function individualRowMapper(): Closure 1088 { 1089 return function (stdClass $row): Individual { 1090 $tree = $this->tree_service->find((int) $row->i_file); 1091 1092 return Individual::rowMapper($tree)($row); 1093 }; 1094 } 1095 1096 /** 1097 * Convert a row from any tree in the media table into an media object. 1098 * 1099 * @return Closure 1100 */ 1101 private function mediaRowMapper(): Closure 1102 { 1103 return function (stdClass $row): Media { 1104 $tree = $this->tree_service->find((int) $row->m_file); 1105 1106 return Media::rowMapper($tree)($row); 1107 }; 1108 } 1109 1110 /** 1111 * Convert a row from any tree in the other table into a note object. 1112 * 1113 * @return Closure 1114 */ 1115 private function noteRowMapper(): Closure 1116 { 1117 return function (stdClass $row): Note { 1118 $tree = $this->tree_service->find((int) $row->o_file); 1119 1120 return Note::rowMapper($tree)($row); 1121 }; 1122 } 1123 1124 /** 1125 * Convert a row from any tree in the other table into a repository object. 1126 * 1127 * @return Closure 1128 */ 1129 private function repositoryRowMapper(): Closure 1130 { 1131 return function (stdClass $row): Repository { 1132 $tree = $this->tree_service->find((int) $row->o_file); 1133 1134 return Repository::rowMapper($tree)($row); 1135 }; 1136 } 1137 1138 /** 1139 * Convert a row from any tree in the sources table into a source object. 1140 * 1141 * @return Closure 1142 */ 1143 private function sourceRowMapper(): Closure 1144 { 1145 return function (stdClass $row): Source { 1146 $tree = $this->tree_service->find((int) $row->s_file); 1147 1148 return Source::rowMapper($tree)($row); 1149 }; 1150 } 1151 1152 /** 1153 * Convert a row from any tree in the other table into a submitter object. 1154 * 1155 * @return Closure 1156 */ 1157 private function submitterRowMapper(): Closure 1158 { 1159 return function (stdClass $row): Submitter { 1160 $tree = $this->tree_service->find((int) $row->o_file); 1161 1162 return Submitter::rowMapper($tree)($row); 1163 }; 1164 } 1165} 1166