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