1<?php 2/** 3 * webtrees: online genealogy 4 * Copyright (C) 2019 webtrees development team 5 * This program is free software: you can redistribute it and/or modify 6 * it under the terms of the GNU General Public License as published by 7 * the Free Software Foundation, either version 3 of the License, or 8 * (at your option) any later version. 9 * This program is distributed in the hope that it will be useful, 10 * but WITHOUT ANY WARRANTY; without even the implied warranty of 11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 * GNU General Public License for more details. 13 * You should have received a copy of the GNU General Public License 14 * along with this program. If not, see <http://www.gnu.org/licenses/>. 15 */ 16declare(strict_types=1); 17 18namespace Fisharebest\Webtrees\Services; 19 20use Closure; 21use Fisharebest\Localization\Locale\LocaleInterface; 22use Fisharebest\Webtrees\Date; 23use Fisharebest\Webtrees\Exceptions\InternalServerErrorException; 24use Fisharebest\Webtrees\Exceptions\TooManySearchResultsException; 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; 43use function mb_stripos; 44 45/** 46 * Search trees for genealogy records. 47 */ 48class SearchService 49{ 50 /** @var LocaleInterface */ 51 private $locale; 52 53 /** 54 * SearchService constructor. 55 * 56 * @param LocaleInterface $locale 57 */ 58 public function __construct(LocaleInterface $locale) 59 { 60 $this->locale = $locale; 61 } 62 63 /** 64 * @param Tree[] $trees 65 * @param string[] $search 66 * 67 * @return Collection|Family[] 68 */ 69 public function searchFamilies(array $trees, array $search): Collection 70 { 71 $query = DB::table('families'); 72 73 $this->whereTrees($query, 'f_file', $trees); 74 $this->whereSearch($query, 'f_gedcom', $search); 75 76 return $query 77 ->get() 78 ->each($this->rowLimiter()) 79 ->map(Family::rowMapper()) 80 ->filter(GedcomRecord::accessFilter()) 81 ->filter($this->rawGedcomFilter($search)); 82 } 83 84 /** 85 * Search for families by name. 86 * 87 * @param Tree[] $trees 88 * @param string[] $search 89 * @param int $offset 90 * @param int $limit 91 * 92 * @return Collection|Family[] 93 */ 94 public function searchFamilyNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 95 { 96 $query = DB::table('families') 97 ->join('name AS husb_name', function (JoinClause $join): void { 98 $join 99 ->on('husb_name.n_file', '=', 'families.f_file') 100 ->on('husb_name.n_id', '=', 'families.f_husb'); 101 }) 102 ->join('name AS wife_name', function (JoinClause $join): void { 103 $join 104 ->on('wife_name.n_file', '=', 'families.f_file') 105 ->on('wife_name.n_id', '=', 'families.f_wife'); 106 }) 107 ->where('wife_name.n_type', '<>', '_MARNM') 108 ->where('husb_name.n_type', '<>', '_MARNM'); 109 110 $prefix = DB::connection()->getTablePrefix(); 111 $field = DB::raw('(' . $prefix . 'husb_name.n_full || ' . $prefix . 'wife_name.n_full)'); 112 113 $this->whereTrees($query, 'f_file', $trees); 114 $this->whereSearch($query, $field, $search); 115 116 $query 117 ->orderBy('husb_name.n_sort') 118 ->orderBy('wife_name.n_sort') 119 ->select(['families.*', 'husb_name.n_sort', 'wife_name.n_sort']) 120 ->distinct(); 121 122 return $this->paginateQuery($query, Family::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 123 } 124 125 /** 126 * @param Tree[] $trees 127 * @param string[] $search 128 * 129 * @return Collection|Individual[] 130 */ 131 public function searchIndividuals(array $trees, array $search): Collection 132 { 133 $query = DB::table('individuals'); 134 135 $this->whereTrees($query, 'i_file', $trees); 136 $this->whereSearch($query, 'i_gedcom', $search); 137 138 return $query 139 ->get() 140 ->each($this->rowLimiter()) 141 ->map(Individual::rowMapper()) 142 ->filter(GedcomRecord::accessFilter()) 143 ->filter($this->rawGedcomFilter($search)); 144 } 145 146 /** 147 * Search for individuals by name. 148 * 149 * @param Tree[] $trees 150 * @param string[] $search 151 * @param int $offset 152 * @param int $limit 153 * 154 * @return Collection|Individual[] 155 */ 156 public function searchIndividualNames(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 157 { 158 $query = DB::table('individuals') 159 ->join('name', function (JoinClause $join): void { 160 $join 161 ->on('name.n_file', '=', 'individuals.i_file') 162 ->on('name.n_id', '=', 'individuals.i_id'); 163 }) 164 ->orderBy('n_sort') 165 ->select(['individuals.*', 'n_sort', 'n_num']) 166 ->distinct(); 167 168 $this->whereTrees($query, 'i_file', $trees); 169 $this->whereSearch($query, 'n_full', $search); 170 171 return $this->paginateQuery($query, Individual::rowMapper(), 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|Media[] 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, Media::rowMapper(), 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|Note[] 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, Note::rowMapper(), 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|Repository[] 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, Repository::rowMapper(), 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|Source[] 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, Source::rowMapper(), 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|Source[] 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, Source::rowMapper(), 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|GedcomRecord[] 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, GedcomRecord::rowMapper(), 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|Place[] 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 = function (stdClass $row) use ($tree): Place { 347 $place = implode(', ', array_filter((array) $row)); 348 349 return new Place($place, $tree); 350 }; 351 352 $filter = 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|Individual[] 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', 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', 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', 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', 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', 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', 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', 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', 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', 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', 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', 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', 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', 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', 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 = preg_split('/:/', $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] . ' %' . $field_value . '%'); 706 } 707 } 708 709 return $query 710 ->get() 711 ->each($this->rowLimiter()) 712 ->map(Individual::rowMapper()) 713 ->filter(GedcomRecord::accessFilter()) 714 ->filter(function (Individual $individual) use ($fields): bool { 715 // Check for XXXX:PLAC fields, 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 = preg_split('/:/', $field_name . '::::'); 720 721 if ($parts[1] === 'PLAC') { 722 // *:PLAC 723 foreach ($individual->facts([$parts[0]]) as $fact) { 724 if (preg_match($regex, $fact->place()->gedcomName())) { 725 return true; 726 } 727 } 728 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 729 // FAMS:*:PLAC 730 foreach ($individual->spouseFamilies() as $family) { 731 foreach ($family->facts([$parts[1]]) as $fact) { 732 if (preg_match($regex, $fact->place()->gedcomName())) { 733 return true; 734 } 735 } 736 } 737 } elseif ($parts[0] === 'FAMS') { 738 // e.g. searches for occupation, religion, note, etc. 739 foreach ($individual->spouseFamilies() as $family) { 740 foreach ($family->facts([$parts[1]]) as $fact) { 741 if (preg_match($regex, $fact->value())) { 742 return true; 743 } 744 } 745 } 746 } elseif ($parts[1] === 'TYPE') { 747 // e.g. FACT:TYPE or EVEN:TYPE 748 foreach ($individual->facts([$parts[0]]) as $fact) { 749 if (preg_match($regex, $fact->attribute('TYPE'))) { 750 return true; 751 } 752 } 753 } else { 754 // e.g. searches for occupation, religion, note, etc. 755 foreach ($individual->facts([$parts[0]]) as $fact) { 756 if (preg_match($regex, $fact->value())) { 757 return true; 758 } 759 } 760 } 761 762 // No match 763 return false; 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|Individual[] 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', 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', 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', 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(Individual::rowMapper()) 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 return $collection; 881 } 882 883 /** 884 * Apply search filters to a SQL query column. Apply collation rules to MySQL. 885 * 886 * @param Builder $query 887 * @param Expression|string $field 888 * @param string[] $search_terms 889 */ 890 private function whereSearch(Builder $query, $field, array $search_terms): void 891 { 892 if ($field instanceof Expression) { 893 $field = $field->getValue(); 894 } 895 896 $field = DB::raw($field . ' /*! COLLATE ' . 'utf8_' . $this->locale->collation() . ' */'); 897 898 foreach ($search_terms as $search_term) { 899 $query->whereContains($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(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(function (Tree $tree) { 929 return $tree->id(); 930 }, $trees); 931 932 $query->whereIn($tree_id_field, $tree_ids); 933 } 934 935 /** 936 * A closure to filter records by privacy-filtered GEDCOM data. 937 * 938 * @param array $search_terms 939 * 940 * @return Closure 941 */ 942 private function rawGedcomFilter(array $search_terms): Closure 943 { 944 return function (GedcomRecord $record) use ($search_terms): bool { 945 // Ignore non-genealogy fields 946 $gedcom = preg_replace('/\n\d (?:_UID) .*/', '', $record->gedcom()); 947 948 // Ignore matches in links 949 $gedcom = preg_replace('/\n\d ' . Gedcom::REGEX_TAG . '( @' . Gedcom::REGEX_XREF . '@)?/', '', $gedcom); 950 951 // Re-apply the filtering 952 foreach ($search_terms as $search_term) { 953 if (mb_stripos($gedcom, $search_term) === false) { 954 return false; 955 } 956 } 957 958 return true; 959 }; 960 } 961 962 /** 963 * Searching for short or common text can give more results than the system can process. 964 * 965 * @param int $limit 966 * 967 * @return Closure 968 */ 969 private function rowLimiter(int $limit = 1000): Closure 970 { 971 return function () use ($limit) { 972 static $n = 0; 973 974 if (++$n > $limit) { 975 $message = I18N::translate('The search returned too many results.'); 976 977 throw new InternalServerErrorException($message); 978 } 979 }; 980 } 981} 982