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