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