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