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