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