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