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