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