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