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', 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', 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', 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_sort', 'n_num']) 155 ->distinct(); 156 157 $this->whereTrees($query, 'i_file', $trees); 158 $this->whereSearch($query, 'n_full', $search); 159 160 return $this->paginateQuery($query, Individual::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 161 } 162 163 /** 164 * Search for media objects. 165 * 166 * @param Tree[] $trees 167 * @param string[] $search 168 * @param int $offset 169 * @param int $limit 170 * 171 * @return Collection 172 * @return Media[] 173 */ 174 public function searchMedia(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 175 { 176 $query = DB::table('media'); 177 178 $this->whereTrees($query, 'media.m_file', $trees); 179 $this->whereSearch($query, 'm_gedcom', $search); 180 181 return $this->paginateQuery($query, Media::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 182 } 183 184 /** 185 * Search for notes. 186 * 187 * @param Tree[] $trees 188 * @param string[] $search 189 * @param int $offset 190 * @param int $limit 191 * 192 * @return Collection 193 * @return Note[] 194 */ 195 public function searchNotes(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 196 { 197 $query = DB::table('other') 198 ->where('o_type', '=', 'NOTE'); 199 200 $this->whereTrees($query, 'o_file', $trees); 201 $this->whereSearch($query, 'o_gedcom', $search); 202 203 return $this->paginateQuery($query, Note::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 204 } 205 206 /** 207 * Search for repositories. 208 * 209 * @param Tree[] $trees 210 * @param string[] $search 211 * @param int $offset 212 * @param int $limit 213 * 214 * @return Collection 215 * @return Repository[] 216 */ 217 public function searchRepositories(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 218 { 219 $query = DB::table('other') 220 ->where('o_type', '=', 'REPO'); 221 222 $this->whereTrees($query, 'o_file', $trees); 223 $this->whereSearch($query, 'o_gedcom', $search); 224 225 return $this->paginateQuery($query, Repository::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 226 } 227 228 /** 229 * Search for sources. 230 * 231 * @param Tree[] $trees 232 * @param string[] $search 233 * @param int $offset 234 * @param int $limit 235 * 236 * @return Collection 237 * @return Source[] 238 */ 239 public function searchSources(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 240 { 241 $query = DB::table('sources'); 242 243 $this->whereTrees($query, 's_file', $trees); 244 $this->whereSearch($query, 's_gedcom', $search); 245 246 return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 247 } 248 249 /** 250 * Search for sources by name. 251 * 252 * @param Tree[] $trees 253 * @param string[] $search 254 * @param int $offset 255 * @param int $limit 256 * 257 * @return Collection 258 * @return Source[] 259 */ 260 public function searchSourcesByName(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 261 { 262 $query = DB::table('sources') 263 ->orderBy('s_name'); 264 265 $this->whereTrees($query, 's_file', $trees); 266 $this->whereSearch($query, 's_name', $search); 267 268 return $this->paginateQuery($query, Source::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 269 } 270 271 /** 272 * Search for submitters. 273 * 274 * @param Tree[] $trees 275 * @param string[] $search 276 * @param int $offset 277 * @param int $limit 278 * 279 * @return Collection 280 * @return GedcomRecord[] 281 */ 282 public function searchSubmitters(array $trees, array $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 283 { 284 $query = DB::table('other') 285 ->where('o_type', '=', 'SUBM'); 286 287 $this->whereTrees($query, 'o_file', $trees); 288 $this->whereSearch($query, 'o_gedcom', $search); 289 290 return $this->paginateQuery($query, GedcomRecord::rowMapper(), GedcomRecord::accessFilter(), $offset, $limit); 291 } 292 293 /** 294 * Search for places. 295 * 296 * @param Tree $tree 297 * @param string $search 298 * @param int $offset 299 * @param int $limit 300 * 301 * @return Collection 302 * @return Place[] 303 */ 304 public function searchPlaces(Tree $tree, string $search, int $offset = 0, int $limit = PHP_INT_MAX): Collection 305 { 306 $query = DB::table('places AS p0') 307 ->where('p0.p_file', '=', $tree->id()) 308 ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id') 309 ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id') 310 ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id') 311 ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id') 312 ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id') 313 ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id') 314 ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id') 315 ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id') 316 ->orderBy('p0.p_place') 317 ->orderBy('p1.p_place') 318 ->orderBy('p2.p_place') 319 ->orderBy('p3.p_place') 320 ->orderBy('p4.p_place') 321 ->orderBy('p5.p_place') 322 ->orderBy('p6.p_place') 323 ->orderBy('p7.p_place') 324 ->orderBy('p8.p_place') 325 ->select([ 326 'p0.p_place AS place0', 327 'p1.p_place AS place1', 328 'p2.p_place AS place2', 329 'p3.p_place AS place3', 330 'p4.p_place AS place4', 331 'p5.p_place AS place5', 332 'p6.p_place AS place6', 333 'p7.p_place AS place7', 334 'p8.p_place AS place8', 335 ]); 336 337 // Filter each level of the hierarchy. 338 foreach (explode(',', $search, 9) as $level => $string) { 339 $query->whereContains('p' . $level . '.p_place', $string); 340 } 341 342 $row_mapper = function (stdClass $row) use ($tree): Place { 343 $place = implode(', ', array_filter((array) $row)); 344 345 return new Place($place, $tree); 346 }; 347 348 $filter = function (): bool { 349 return true; 350 }; 351 352 return $this->paginateQuery($query, $row_mapper, $filter, $offset, $limit); 353 } 354 355 /** 356 * @param Tree[] $trees 357 * @param string[] $fields 358 * @param string[] $modifiers 359 * 360 * @return Collection 361 * @return Individual[] 362 */ 363 public function searchIndividualsAdvanced(array $trees, array $fields, array $modifiers): Collection 364 { 365 $fields = array_filter($fields); 366 367 $query = DB::table('individuals') 368 ->select(['individuals.*']) 369 ->distinct(); 370 371 $this->whereTrees($query, 'i_file', $trees); 372 373 // Join the following tables 374 $father_name = false; 375 $mother_name = false; 376 $spouse_family = false; 377 $indi_name = false; 378 $indi_date = false; 379 $fam_date = false; 380 $indi_plac = false; 381 $fam_plac = false; 382 383 foreach ($fields as $field_name => $field_value) { 384 if ($field_value !== '') { 385 if (substr($field_name, 0, 14) === 'FAMC:HUSB:NAME') { 386 $father_name = true; 387 } elseif (substr($field_name, 0, 14) === 'FAMC:WIFE:NAME') { 388 $mother_name = true; 389 } elseif (substr($field_name, 0, 4) === 'NAME') { 390 $indi_name = true; 391 } elseif (strpos($field_name, ':DATE') !== false) { 392 if (substr($field_name, 0, 4) === 'FAMS') { 393 $fam_date = true; 394 $spouse_family = true; 395 } else { 396 $indi_date = true; 397 } 398 } elseif (strpos($field_name, ':PLAC') !== false) { 399 if (substr($field_name, 0, 4) === 'FAMS') { 400 $fam_plac = true; 401 $spouse_family = true; 402 } else { 403 $indi_plac = true; 404 } 405 } elseif ($field_name === 'FAMS:NOTE') { 406 $spouse_family = true; 407 } 408 } 409 } 410 411 if ($father_name || $mother_name) { 412 $query->join('link AS l1', function (JoinClause $join): void { 413 $join 414 ->on('l1.l_file', '=', 'individuals.i_file') 415 ->on('l1.l_from', '=', 'individuals.i_id') 416 ->where('l1.l_type', '=', 'FAMC'); 417 }); 418 419 if ($father_name) { 420 $query->join('link AS l2', function (JoinClause $join): void { 421 $join 422 ->on('l2.l_file', '=', 'l1.l_file') 423 ->on('l2.l_from', '=', 'l1.l_to') 424 ->where('l2.l_type', '=', 'HUSB'); 425 }); 426 $query->join('name AS father_name', function (JoinClause $join): void { 427 $join 428 ->on('father_name.n_file', '=', 'l2.l_file') 429 ->on('father_name.n_id', '=', 'l2.l_to'); 430 }); 431 } 432 433 if ($mother_name) { 434 $query->join('link AS l3', function (JoinClause $join): void { 435 $join 436 ->on('l3.l_file', '=', 'l1.l_file') 437 ->on('l3.l_from', '=', 'l1.l_to') 438 ->where('l3.l_type', '=', 'WIFE'); 439 }); 440 $query->join('name AS mother_name', function (JoinClause $join): void { 441 $join 442 ->on('mother_name.n_file', '=', 'l3.l_file') 443 ->on('mother_name.n_id', '=', 'l3.l_to'); 444 }); 445 } 446 } 447 448 if ($spouse_family) { 449 $query->join('link AS l4', function (JoinClause $join): void { 450 $join 451 ->on('l4.l_file', '=', 'individuals.i_file') 452 ->on('l4.l_from', '=', 'individuals.i_id') 453 ->where('l4.l_type', '=', 'FAMS'); 454 }); 455 $query->join('families AS spouse_families', function (JoinClause $join): void { 456 $join 457 ->on('spouse_families.f_file', '=', 'l4.l_file') 458 ->on('spouse_families.f_id', '=', 'l4.l_to'); 459 }); 460 } 461 462 if ($indi_name) { 463 $query->join('name AS individual_name', function (JoinClause $join): void { 464 $join 465 ->on('individual_name.n_file', '=', 'individuals.i_file') 466 ->on('individual_name.n_id', '=', 'individuals.i_id'); 467 }); 468 } 469 470 if ($indi_date) { 471 $query->join('dates AS individual_dates', function (JoinClause $join): void { 472 $join 473 ->on('individual_dates.d_file', '=', 'individuals.i_file') 474 ->on('individual_dates.d_gid', '=', 'individuals.i_id'); 475 }); 476 } 477 478 if ($fam_date) { 479 $query->join('dates AS family_dates', function (JoinClause $join): void { 480 $join 481 ->on('family_dates.d_file', '=', 'spouse_families.f_file') 482 ->on('family_dates.d_gid', '=', 'spouse_families.f_id'); 483 }); 484 } 485 486 if ($indi_plac) { 487 $query->join('placelinks AS individual_placelinks', function (JoinClause $join): void { 488 $join 489 ->on('individual_placelinks.pl_file', '=', 'individuals.i_file') 490 ->on('individual_placelinks.pl_gid', '=', 'individuals.i_id'); 491 }); 492 $query->join('places AS individual_places', function (JoinClause $join): void { 493 $join 494 ->on('individual_places.p_file', '=', 'individual_placelinks.pl_file') 495 ->on('individual_places.p_id', '=', 'individual_placelinks.pl_p_id'); 496 }); 497 } 498 499 if ($fam_plac) { 500 $query->join('placelinks AS familyl_placelinks', function (JoinClause $join): void { 501 $join 502 ->on('familyl_placelinks.pl_file', '=', 'individuals.i_file') 503 ->on('familyl_placelinks.pl_gid', '=', 'individuals.i_id'); 504 }); 505 $query->join('places AS family_places', function (JoinClause $join): void { 506 $join 507 ->on('family_places.p_file', '=', 'familyl_placelinks.pl_file') 508 ->on('family_places.p_id', '=', 'familyl_placelinks.pl_p_id'); 509 }); 510 } 511 512 foreach ($fields as $field_name => $field_value) { 513 $parts = preg_split('/:/', $field_name . '::::'); 514 if ($parts[0] === 'NAME') { 515 // NAME:* 516 switch ($parts[1]) { 517 case 'GIVN': 518 switch ($modifiers[$field_name]) { 519 case 'EXACT': 520 $query->where('individual_name.n_givn', '=', $field_value); 521 break; 522 case 'BEGINS': 523 $query->where('individual_name.n_givn', 'LIKE', $field_value . '%'); 524 break; 525 case 'CONTAINS': 526 $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%'); 527 break; 528 case 'SDX_STD': 529 $sdx = Soundex::russell($field_value); 530 if ($sdx !== '') { 531 $this->wherePhonetic($query, 'individual_name.n_soundex_givn_std', $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 case 'SDX': // SDX uses DM by default. 538 case 'SDX_DM': 539 $sdx = Soundex::daitchMokotoff($field_value); 540 if ($sdx !== '') { 541 $this->wherePhonetic($query, 'individual_name.n_soundex_givn_dm', $sdx); 542 } else { 543 // No phonetic content? Use a substring match 544 $query->where('individual_name.n_givn', 'LIKE', '%' . $field_value . '%'); 545 } 546 break; 547 } 548 break; 549 case 'SURN': 550 switch ($modifiers[$field_name]) { 551 case 'EXACT': 552 $query->where('individual_name.n_surn', '=', $field_value); 553 break; 554 case 'BEGINS': 555 $query->where('individual_name.n_surn', 'LIKE', $field_value . '%'); 556 break; 557 case 'CONTAINS': 558 $query->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%'); 559 break; 560 case 'SDX_STD': 561 $sdx = Soundex::russell($field_value); 562 if ($sdx !== '') { 563 $this->wherePhonetic($query, 'individual_name.n_soundex_surn_std', $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 case 'SDX': // SDX uses DM by default. 570 case 'SDX_DM': 571 $sdx = Soundex::daitchMokotoff($field_value); 572 if ($sdx !== '') { 573 $this->wherePhonetic($query, 'individual_name.n_soundex_surn_dm', $sdx); 574 } else { 575 // No phonetic content? Use a substring match 576 $query->where('individual_name.n_surn', 'LIKE', '%' . $field_value . '%'); 577 } 578 break; 579 } 580 break; 581 case 'NICK': 582 case '_MARNM': 583 case '_HEB': 584 case '_AKA': 585 $query 586 ->where('individual_name', '=', $parts[1]) 587 ->where('individual_name', 'LIKE', '%' . $field_value . '%'); 588 break; 589 } 590 unset($fields[$field_name]); 591 } elseif ($parts[1] === 'DATE') { 592 // *:DATE 593 $date = new Date($field_value); 594 if ($date->isOK()) { 595 $delta = 365 * ($modifiers[$field_name] ?? 0); 596 $query 597 ->where('individual_dates.d_fact', '=', $parts[0]) 598 ->where('individual_dates.d_julianday1', '>=', $date->minimumJulianDay() - $delta) 599 ->where('individual_dates.d_julianday2', '<=', $date->minimumJulianDay() + $delta); 600 } 601 unset($fields[$field_name]); 602 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'DATE') { 603 // FAMS:*:DATE 604 $date = new Date($field_value); 605 if ($date->isOK()) { 606 $delta = 365 * $modifiers[$field_name]; 607 $query 608 ->where('family_dates.d_fact', '=', $parts[1]) 609 ->where('family_dates.d_julianday1', '>=', $date->minimumJulianDay() - $delta) 610 ->where('family_dates.d_julianday2', '<=', $date->minimumJulianDay() + $delta); 611 } 612 unset($fields[$field_name]); 613 } elseif ($parts[1] === 'PLAC') { 614 // *:PLAC 615 // SQL can only link a place to a person/family, not to an event. 616 $query->where('individual_places.p_place', 'LIKE', '%' . $field_value . '%'); 617 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 618 // FAMS:*:PLAC 619 // SQL can only link a place to a person/family, not to an event. 620 $query->where('family_places.p_place', 'LIKE', '%' . $field_value . '%'); 621 } elseif ($parts[0] === 'FAMC' && $parts[2] === 'NAME') { 622 $table = $parts[1] === 'HUSB' ? 'father_name' : 'mother_name'; 623 // NAME:* 624 switch ($parts[3]) { 625 case 'GIVN': 626 switch ($modifiers[$field_name]) { 627 case 'EXACT': 628 $query->where($table . '.n_givn', '=', $field_value); 629 break; 630 case 'BEGINS': 631 $query->where($table . '.n_givn', 'LIKE', $field_value . '%'); 632 break; 633 case 'CONTAINS': 634 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 635 break; 636 case 'SDX_STD': 637 $sdx = Soundex::russell($field_value); 638 if ($sdx !== '') { 639 $this->wherePhonetic($query, $table . '.n_soundex_givn_std', $sdx); 640 } else { 641 // No phonetic content? Use a substring match 642 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 643 } 644 break; 645 case 'SDX': // SDX uses DM by default. 646 case 'SDX_DM': 647 $sdx = Soundex::daitchMokotoff($field_value); 648 if ($sdx !== '') { 649 $this->wherePhonetic($query, $table . '.n_soundex_givn_dm', $sdx); 650 } else { 651 // No phonetic content? Use a substring match 652 $query->where($table . '.n_givn', 'LIKE', '%' . $field_value . '%'); 653 } 654 break; 655 } 656 break; 657 case 'SURN': 658 switch ($modifiers[$field_name]) { 659 case 'EXACT': 660 $query->where($table . '.n_surn', '=', $field_value); 661 break; 662 case 'BEGINS': 663 $query->where($table . '.n_surn', 'LIKE', $field_value . '%'); 664 break; 665 case 'CONTAINS': 666 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 667 break; 668 case 'SDX_STD': 669 $sdx = Soundex::russell($field_value); 670 if ($sdx !== '') { 671 $this->wherePhonetic($query, $table . '.n_soundex_surn_std', $sdx); 672 } else { 673 // No phonetic content? Use a substring match 674 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 675 } 676 break; 677 case 'SDX': // SDX uses DM by default. 678 case 'SDX_DM': 679 $sdx = Soundex::daitchMokotoff($field_value); 680 if ($sdx !== '') { 681 $this->wherePhonetic($query, $table . '.n_soundex_surn_dm', $sdx); 682 } else { 683 // No phonetic content? Use a substring match 684 $query->where($table . '.n_surn', 'LIKE', '%' . $field_value . '%'); 685 } 686 break; 687 } 688 break; 689 } 690 unset($fields[$field_name]); 691 } elseif ($parts[0] === 'FAMS') { 692 // e.g. searches for occupation, religion, note, etc. 693 // Initial matching only. Need PHP to apply filter. 694 $query->where('families.f_gedcom', 'LIKE', "%\n1 " . $parts[1] . ' %' . $field_value . '%'); 695 } elseif ($parts[1] === 'TYPE') { 696 // e.g. FACT:TYPE or EVEN:TYPE 697 // Initial matching only. Need PHP to apply filter. 698 $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . '%\n2 TYPE %' . $field_value . '%'); 699 } else { 700 // e.g. searches for occupation, religion, note, etc. 701 // Initial matching only. Need PHP to apply filter. 702 $query->where('individuals.i_gedcom', 'LIKE', "%\n1 " . $parts[0] . ' %' . $field_value . '%'); 703 } 704 } 705 706 return $query 707 ->get() 708 ->each($this->rowLimiter()) 709 ->map(Individual::rowMapper()) 710 ->filter(GedcomRecord::accessFilter()) 711 ->filter(function (Individual $individual) use ($fields): bool { 712 // Check for XXXX:PLAC fields, which were only partially matched by SQL 713 foreach ($fields as $field_name => $field_value) { 714 $regex = '/' . preg_quote($field_value, '/') . '/i'; 715 716 $parts = preg_split('/:/', $field_name . '::::'); 717 718 if ($parts[1] === 'PLAC') { 719 // *:PLAC 720 foreach ($individual->facts([$parts[0]]) as $fact) { 721 if (preg_match($regex, $fact->place()->gedcomName())) { 722 return true; 723 } 724 } 725 } elseif ($parts[0] === 'FAMS' && $parts[2] === 'PLAC') { 726 // FAMS:*:PLAC 727 foreach ($individual->spouseFamilies() as $family) { 728 foreach ($family->facts([$parts[1]]) as $fact) { 729 if (preg_match($regex, $fact->place()->gedcomName())) { 730 return true; 731 } 732 } 733 } 734 } elseif ($parts[0] === 'FAMS') { 735 // e.g. searches for occupation, religion, note, etc. 736 foreach ($individual->spouseFamilies() as $family) { 737 foreach ($family->facts([$parts[1]]) as $fact) { 738 if (preg_match($regex, $fact->value())) { 739 return true; 740 } 741 } 742 } 743 } elseif ($parts[1] === 'TYPE') { 744 // e.g. FACT:TYPE or EVEN:TYPE 745 foreach ($individual->facts([$parts[0]]) as $fact) { 746 if (preg_match($regex, $fact->attribute('TYPE'))) { 747 return true; 748 } 749 } 750 } else { 751 // e.g. searches for occupation, religion, note, etc. 752 foreach ($individual->facts([$parts[0]]) as $fact) { 753 if (preg_match($regex, $fact->value())) { 754 return true; 755 } 756 } 757 } 758 759 // No match 760 return false; 761 } 762 763 return true; 764 }); 765 } 766 767 /** 768 * @param string $soundex 769 * @param string $lastname 770 * @param string $firstname 771 * @param string $place 772 * @param Tree[] $search_trees 773 * 774 * @return Collection 775 * @return Individual[] 776 */ 777 public function searchIndividualsPhonetic(string $soundex, string $lastname, string $firstname, string $place, array $search_trees): Collection 778 { 779 switch ($soundex) { 780 default: 781 case 'Russell': 782 $givn_sdx = Soundex::russell($firstname); 783 $surn_sdx = Soundex::russell($lastname); 784 $plac_sdx = Soundex::russell($place); 785 $givn_field = 'n_soundex_givn_std'; 786 $surn_field = 'n_soundex_surn_std'; 787 $plac_field = 'p_std_soundex'; 788 break; 789 case 'DaitchM': 790 $givn_sdx = Soundex::daitchMokotoff($firstname); 791 $surn_sdx = Soundex::daitchMokotoff($lastname); 792 $plac_sdx = Soundex::daitchMokotoff($place); 793 $givn_field = 'n_soundex_givn_dm'; 794 $surn_field = 'n_soundex_surn_dm'; 795 $plac_field = 'p_dm_soundex'; 796 break; 797 } 798 799 // Nothing to search for? Return nothing. 800 if ($givn_sdx === '' && $surn_sdx === '' && $plac_sdx === '') { 801 return new Collection(); 802 } 803 804 $query = DB::table('individuals') 805 ->select(['individuals.*']) 806 ->distinct(); 807 808 $this->whereTrees($query, 'i_file', $search_trees); 809 810 if ($plac_sdx !== '') { 811 $query->join('placelinks', function (JoinClause $join): void { 812 $join 813 ->on('placelinks.pl_file', '=', 'individuals.i_file') 814 ->on('placelinks.pl_gid', '=', 'individuals.i_id'); 815 }); 816 $query->join('places', function (JoinClause $join): void { 817 $join 818 ->on('places.p_file', '=', 'placelinks.pl_file') 819 ->on('places.p_id', '=', 'placelinks.pl_p_id'); 820 }); 821 822 $this->wherePhonetic($query, $plac_field, $plac_sdx); 823 } 824 825 if ($givn_sdx !== '' || $surn_sdx !== '') { 826 $query->join('name', function (JoinClause $join): void { 827 $join 828 ->on('name.n_file', '=', 'individuals.i_file') 829 ->on('name.n_id', '=', 'individuals.i_id'); 830 }); 831 832 $this->wherePhonetic($query, $givn_field, $givn_sdx); 833 $this->wherePhonetic($query, $surn_field, $surn_sdx); 834 } 835 836 return $query 837 ->get() 838 ->each($this->rowLimiter()) 839 ->map(Individual::rowMapper()) 840 ->filter(GedcomRecord::accessFilter()); 841 } 842 843 /** 844 * Paginate a search query. 845 * 846 * @param Builder $query Searches the database for the desired records. 847 * @param Closure $row_mapper Converts a row from the query into a record. 848 * @param Closure $row_filter 849 * @param int $offset Skip this many rows. 850 * @param int $limit Take this many rows. 851 * 852 * @return Collection 853 */ 854 private function paginateQuery(Builder $query, Closure $row_mapper, Closure $row_filter, int $offset, int $limit): Collection 855 { 856 $collection = new Collection(); 857 858 foreach ($query->cursor() as $row) { 859 $record = $row_mapper($row); 860 // If the object has a method "canShow()", then use it to filter for privacy. 861 if ($row_filter($record)) { 862 if ($offset > 0) { 863 $offset--; 864 } else { 865 if ($limit > 0) { 866 $collection->push($record); 867 } 868 869 $limit--; 870 871 if ($limit === 0) { 872 break; 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(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(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 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 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