xref: /webtrees/app/Services/LinkedRecordService.php (revision db60fbe701448745baf2f397225debcc8f5e760d)
1<?php
2
3/**
4 * webtrees: online genealogy
5 * Copyright (C) 2022 webtrees development team
6 * This program is free software: you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation, either version 3 of the License, or
9 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 * You should have received a copy of the GNU General Public License
15 * along with this program. If not, see <https://www.gnu.org/licenses/>.
16 */
17
18declare(strict_types=1);
19
20namespace Fisharebest\Webtrees\Services;
21
22use Fisharebest\Webtrees\Family;
23use Fisharebest\Webtrees\GedcomRecord;
24use Fisharebest\Webtrees\Individual;
25use Fisharebest\Webtrees\Location;
26use Fisharebest\Webtrees\Media;
27use Fisharebest\Webtrees\Note;
28use Fisharebest\Webtrees\Registry;
29use Fisharebest\Webtrees\Repository;
30use Fisharebest\Webtrees\Source;
31use Fisharebest\Webtrees\Submitter;
32use Illuminate\Database\Capsule\Manager as DB;
33use Illuminate\Database\Query\Builder;
34use Illuminate\Database\Query\Expression;
35use Illuminate\Database\Query\JoinClause;
36use Illuminate\Support\Collection;
37
38use function addcslashes;
39
40/**
41 * Find records linked to other records
42 */
43class LinkedRecordService
44{
45    /**
46     * Find all records linked to a record.
47     *
48     * @param GedcomRecord $record
49     *
50     * @return Collection<int,Family>
51     */
52    public function allLinkedRecords(GedcomRecord $record): Collection
53    {
54        $like = addcslashes($record->xref(), '\\%_');
55
56        $union = DB::table('change')
57            ->where('gedcom_id', '=', $record->tree()->id())
58            ->where('new_gedcom', 'LIKE', '%@' . $like . '@%')
59            ->where('new_gedcom', 'NOT LIKE', '0 @' . $like . '@%')
60            ->whereIn('change_id', function (Builder $query) use ($record): void {
61                $query
62                    ->select(new Expression('MAX(change_id)'))
63                    ->from('change')
64                    ->where('gedcom_id', '=', $record->tree()->id())
65                    ->where('status', '=', 'pending')
66                    ->groupBy(['xref']);
67            })
68            ->select(['xref']);
69
70        $xrefs = DB::table('link')
71            ->where('l_file', '=', $record->tree()->id())
72            ->where('l_to', '=', $record->xref())
73            ->select(['l_from'])
74            ->union($union)
75            ->pluck('l_from');
76
77        return $xrefs->map(static fn (string $xref) => Registry::gedcomRecordFactory()->make($xref, $record->tree()));
78    }
79
80    /**
81     * Find families linked to a record.
82     *
83     * @param GedcomRecord $record
84     * @param string|null  $link_type
85     *
86     * @return Collection<int,Family>
87     */
88    public function linkedFamilies(GedcomRecord $record, string $link_type = null): Collection
89    {
90        $query = DB::table('families')
91            ->join('link', static function (JoinClause $join): void {
92                $join
93                    ->on('l_file', '=', 'f_file')
94                    ->on('l_from', '=', 'f_id');
95            })
96            ->where('f_file', '=', $record->tree()->id())
97            ->where('l_to', '=', $record->xref());
98
99        if ($link_type !== null) {
100            $query->where('l_type', '=', $link_type);
101        }
102
103        return $query
104            ->select(['families.*'])
105            ->groupBy('f_id', 'f_file')
106            ->get()
107            ->map(Registry::familyFactory()->mapper($record->tree()))
108            ->filter(GedcomRecord::accessFilter());
109    }
110
111    /**
112     * Find individuals linked to a record.
113     *
114     * @param GedcomRecord $record
115     * @param string|null  $link_type
116     *
117     * @return Collection<int,Individual>
118     */
119    public function linkedIndividuals(GedcomRecord $record, string $link_type = null): Collection
120    {
121        $query = DB::table('individuals')
122            ->join('link', static function (JoinClause $join): void {
123                $join
124                    ->on('l_file', '=', 'i_file')
125                    ->on('l_from', '=', 'i_id');
126            })
127            ->where('i_file', '=', $record->tree()->id())
128            ->where('l_to', '=', $record->xref());
129
130        if ($link_type !== null) {
131            $query->where('l_type', '=', $link_type);
132        }
133
134        return $query
135            ->select(['individuals.*'])
136            ->groupBy('i_id', 'i_file')
137            ->get()
138            ->map(Registry::individualFactory()->mapper($record->tree()))
139            ->filter(GedcomRecord::accessFilter());
140    }
141
142    /**
143     * Find locations linked to a record.
144     *
145     * @param GedcomRecord $record
146     *
147     * @return Collection<int,Location>
148     */
149    public function linkedLocations(GedcomRecord $record): Collection
150    {
151        return DB::table('other')
152            ->join('link', static function (JoinClause $join): void {
153                $join
154                    ->on('l_file', '=', 'o_file')
155                    ->on('l_from', '=', 'o_id');
156            })
157            ->where('o_file', '=', $record->tree()->id())
158            ->where('o_type', '=', Location::RECORD_TYPE)
159            ->where('l_to', '=', $record->xref())
160            ->select(['other.*'])
161            ->groupBy('o_id', 'o_file')
162            ->get()
163            ->map(Registry::locationFactory()->mapper($record->tree()))
164            ->filter(GedcomRecord::accessFilter());
165    }
166
167    /**
168     * Find media objects linked to a record.
169     *
170     * @param GedcomRecord $record
171     *
172     * @return Collection<int,Media>
173     */
174    public function linkedMedia(GedcomRecord $record): Collection
175    {
176        return DB::table('media')
177            ->join('link', static function (JoinClause $join): void {
178                $join
179                    ->on('l_file', '=', 'm_file')
180                    ->on('l_from', '=', 'm_id');
181            })
182            ->where('m_file', '=', $record->tree()->id())
183            ->where('l_to', '=', $record->xref())
184            ->select(['media.*'])
185            ->groupBy('m_id', 'm_file')
186            ->get()
187            ->map(Registry::mediaFactory()->mapper($record->tree()))
188            ->filter(GedcomRecord::accessFilter());
189    }
190
191    /**
192     * Find notes linked to a record.
193     *
194     * @param GedcomRecord $record
195     *
196     * @return Collection<int,Note>
197     */
198    public function linkedNotes(GedcomRecord $record): Collection
199    {
200        return DB::table('other')
201            ->join('link', static function (JoinClause $join): void {
202                $join
203                    ->on('l_file', '=', 'o_file')
204                    ->on('l_from', '=', 'o_id');
205            })
206            ->where('o_file', '=', $record->tree()->id())
207            ->where('o_type', '=', Note::RECORD_TYPE)
208            ->where('l_to', '=', $record->xref())
209            ->select(['other.*'])
210            ->groupBy('o_id', 'o_file')
211            ->get()
212            ->map(Registry::noteFactory()->mapper($record->tree()))
213            ->filter(GedcomRecord::accessFilter());
214    }
215
216    /**
217     * Find repositories linked to a record.
218     *
219     * @param GedcomRecord $record
220     *
221     * @return Collection<int,Repository>
222     */
223    public function linkedRepositories(GedcomRecord $record): Collection
224    {
225        return DB::table('other')
226            ->join('link', static function (JoinClause $join): void {
227                $join
228                    ->on('l_file', '=', 'o_file')
229                    ->on('l_from', '=', 'o_id');
230            })
231            ->where('o_file', '=', $record->tree()->id())
232            ->where('o_type', '=', Repository::RECORD_TYPE)
233            ->where('l_to', '=', $record->xref())
234            ->select(['other.*'])
235            ->groupBy('o_id', 'o_file')
236            ->get()
237            ->map(Registry::repositoryFactory()->mapper($record->tree()))
238            ->filter(GedcomRecord::accessFilter());
239    }
240
241    /**
242     * Find sources linked to a record.
243     *
244     * @param GedcomRecord $record
245     *
246     * @return Collection<int,Source>
247     */
248    public function linkedSources(GedcomRecord $record): Collection
249    {
250        return DB::table('sources')
251            ->join('link', static function (JoinClause $join): void {
252                $join
253                    ->on('l_file', '=', 's_file')
254                    ->on('l_from', '=', 's_id');
255            })
256            ->where('s_file', '=', $record->tree()->id())
257            ->where('l_to', '=', $record->xref())
258            ->select(['sources.*'])
259            ->groupBy('s_id', 's_file')
260            ->get()
261            ->map(Registry::sourceFactory()->mapper($record->tree()))
262            ->filter(GedcomRecord::accessFilter());
263    }
264
265    /**
266     * Find submitters linked to a record.
267     *
268     * @param GedcomRecord $record
269     *
270     * @return Collection<int,Repository>
271     */
272    public function linkedSubmitters(GedcomRecord $record): Collection
273    {
274        return DB::table('other')
275            ->join('link', static function (JoinClause $join): void {
276                $join
277                    ->on('l_file', '=', 'o_file')
278                    ->on('l_from', '=', 'o_id');
279            })
280            ->where('o_file', '=', $record->tree()->id())
281            ->where('o_type', '=', Submitter::RECORD_TYPE)
282            ->where('l_to', '=', $record->xref())
283            ->select(['other.*'])
284            ->groupBy('o_id', 'o_file')
285            ->get()
286            ->map(Registry::repositoryFactory()->mapper($record->tree()))
287            ->filter(GedcomRecord::accessFilter());
288    }
289}
290