Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | |
0.69% |
1 / 145 |
|
5.26% |
1 / 19 |
CRAP | |
0.00% |
0 / 1 |
ResourceTags | |
0.69% |
1 / 145 |
|
5.26% |
1 / 19 |
4332.50 | |
0.00% |
0 / 1 |
__construct | |
100.00% |
1 / 1 |
|
100.00% |
1 / 1 |
1 | |||
createLink | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
6 | |||
checkForTags | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
20 | |||
getResourcesForTag | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
12 | |||
getListsForTag | |
0.00% |
0 / 11 |
|
0.00% |
0 / 1 |
72 | |||
getStatistics | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
6 | |||
destroyResourceLinks | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
56 | |||
destroyListLinks | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
12 | |||
processDestroyLinks | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
20 | |||
getAnonymousCount | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
assignAnonymousTags | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
getUniqueResources | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
20 | |||
getUniqueTags | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
30 | |||
getUniqueUsers | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
20 | |||
formatTagOrder | |
0.00% |
0 / 7 |
|
0.00% |
0 / 1 |
20 | |||
getResourceTags | |
0.00% |
0 / 38 |
|
0.00% |
0 / 1 |
72 | |||
deleteByIdArray | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
6 | |||
getDuplicates | |
0.00% |
0 / 3 |
|
0.00% |
0 / 1 |
2 | |||
deduplicate | |
0.00% |
0 / 4 |
|
0.00% |
0 / 1 |
6 |
1 | <?php |
2 | |
3 | /** |
4 | * Table Definition for resource_tags |
5 | * |
6 | * PHP version 8 |
7 | * |
8 | * Copyright (C) Villanova University 2010. |
9 | * |
10 | * This program is free software; you can redistribute it and/or modify |
11 | * it under the terms of the GNU General Public License version 2, |
12 | * as published by the Free Software Foundation. |
13 | * |
14 | * This program is distributed in the hope that it will be useful, |
15 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
17 | * GNU General Public License for more details. |
18 | * |
19 | * You should have received a copy of the GNU General Public License |
20 | * along with this program; if not, write to the Free Software |
21 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA |
22 | * |
23 | * @category VuFind |
24 | * @package Db_Table |
25 | * @author Demian Katz <demian.katz@villanova.edu> |
26 | * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License |
27 | * @link https://vufind.org Main Site |
28 | */ |
29 | |
30 | namespace VuFind\Db\Table; |
31 | |
32 | use DateTime; |
33 | use Laminas\Db\Adapter\Adapter; |
34 | use Laminas\Db\Sql\Expression; |
35 | use Laminas\Db\Sql\Select; |
36 | use VuFind\Db\Row\RowGateway; |
37 | use VuFind\Db\Service\DbServiceAwareInterface; |
38 | use VuFind\Db\Service\DbServiceAwareTrait; |
39 | use VuFind\Db\Service\ResourceTagsServiceInterface; |
40 | |
41 | use function count; |
42 | use function in_array; |
43 | use function is_array; |
44 | |
45 | /** |
46 | * Table Definition for resource_tags |
47 | * |
48 | * @category VuFind |
49 | * @package Db_Table |
50 | * @author Demian Katz <demian.katz@villanova.edu> |
51 | * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License |
52 | * @link https://vufind.org Main Site |
53 | */ |
54 | class ResourceTags extends Gateway implements DbServiceAwareInterface |
55 | { |
56 | use DbServiceAwareTrait; |
57 | |
58 | /** |
59 | * Constructor |
60 | * |
61 | * @param Adapter $adapter Database adapter |
62 | * @param PluginManager $tm Table manager |
63 | * @param array $cfg Laminas configuration |
64 | * @param RowGateway $rowObj Row prototype object (null for default) |
65 | * @param bool $caseSensitive Are tags case sensitive? |
66 | * @param string $table Name of database table to interface with |
67 | */ |
68 | public function __construct( |
69 | Adapter $adapter, |
70 | PluginManager $tm, |
71 | $cfg, |
72 | ?RowGateway $rowObj = null, |
73 | protected $caseSensitive = false, |
74 | $table = 'resource_tags' |
75 | ) { |
76 | parent::__construct($adapter, $tm, $cfg, $rowObj, $table); |
77 | } |
78 | |
79 | /** |
80 | * Look up a row for the specified resource. |
81 | * |
82 | * @param string $resource ID of resource to link up |
83 | * @param string $tag ID of tag to link up |
84 | * @param string $user ID of user creating link (optional but recommended) |
85 | * @param string $list ID of list to link up (optional) |
86 | * @param string $posted Posted date (optional -- omit for current) |
87 | * |
88 | * @return void |
89 | * |
90 | * @deprecated Use ResourceTagsServiceInterface::createLink() |
91 | */ |
92 | public function createLink( |
93 | $resource, |
94 | $tag, |
95 | $user = null, |
96 | $list = null, |
97 | $posted = null |
98 | ) { |
99 | $this->getDbService(ResourceTagsServiceInterface::class)->createLink( |
100 | $resource, |
101 | $tag, |
102 | $user, |
103 | $list, |
104 | $posted ? DateTime::createFromFormat('Y-m-d H:i:s', $posted) : null |
105 | ); |
106 | } |
107 | |
108 | /** |
109 | * Check whether or not the specified tags are present in the table. |
110 | * |
111 | * @param array $ids IDs to check. |
112 | * |
113 | * @return array Associative array with two keys: present and missing |
114 | * |
115 | * @deprecated |
116 | */ |
117 | public function checkForTags($ids) |
118 | { |
119 | // Set up return arrays: |
120 | $retVal = ['present' => [], 'missing' => []]; |
121 | |
122 | // Look up IDs in the table: |
123 | $callback = function ($select) use ($ids) { |
124 | $select->where->in('tag_id', $ids); |
125 | }; |
126 | $results = $this->select($callback); |
127 | |
128 | // Record all IDs that are present: |
129 | foreach ($results as $current) { |
130 | $retVal['present'][] = $current->tag_id; |
131 | } |
132 | $retVal['present'] = array_unique($retVal['present']); |
133 | |
134 | // Detect missing IDs: |
135 | foreach ($ids as $current) { |
136 | if (!in_array($current, $retVal['present'])) { |
137 | $retVal['missing'][] = $current; |
138 | } |
139 | } |
140 | |
141 | // Send back the results: |
142 | return $retVal; |
143 | } |
144 | |
145 | /** |
146 | * Get resources associated with a particular tag. |
147 | * |
148 | * @param string $tag Tag to match |
149 | * @param string $userId ID of user owning favorite list |
150 | * @param string $listId ID of list to retrieve (null for all favorites) |
151 | * @param ?bool $caseSensitive Should tags be case sensitive? (null to use configured default) |
152 | * |
153 | * @return \Laminas\Db\ResultSet\AbstractResultSet |
154 | */ |
155 | public function getResourcesForTag($tag, $userId, $listId = null, $caseSensitive = null) |
156 | { |
157 | $callback = function ($select) use ($tag, $userId, $listId, $caseSensitive) { |
158 | $select->columns( |
159 | [ |
160 | 'resource_id' => new Expression( |
161 | 'DISTINCT(?)', |
162 | ['resource_tags.resource_id'], |
163 | [Expression::TYPE_IDENTIFIER] |
164 | ), Select::SQL_STAR, |
165 | ] |
166 | ); |
167 | $select->join( |
168 | ['t' => 'tags'], |
169 | 'resource_tags.tag_id = t.id', |
170 | [] |
171 | ); |
172 | if ($caseSensitive ?? $this->caseSensitive) { |
173 | $select->where->equalTo('t.tag', $tag); |
174 | } else { |
175 | $select->where->literal('lower(t.tag) = lower(?)', [$tag]); |
176 | } |
177 | $select->where->equalTo('resource_tags.user_id', $userId); |
178 | if (null !== $listId) { |
179 | $select->where->equalTo('resource_tags.list_id', $listId); |
180 | } |
181 | }; |
182 | |
183 | return $this->select($callback); |
184 | } |
185 | |
186 | /** |
187 | * Get lists associated with a particular tag. |
188 | * |
189 | * @param string|array|null $tag Tag to match (null for all) |
190 | * @param string|array|null $listId List ID to retrieve (null for all) |
191 | * @param bool $publicOnly Whether to return only public lists |
192 | * @param bool $andTags Use AND operator when filtering by tag. |
193 | * @param ?bool $caseSensitive Should tags be case sensitive? (null to use configured default) |
194 | * |
195 | * @return \Laminas\Db\ResultSet\AbstractResultSet |
196 | */ |
197 | public function getListsForTag( |
198 | $tag, |
199 | $listId = null, |
200 | $publicOnly = true, |
201 | $andTags = true, |
202 | $caseSensitive = null |
203 | ) { |
204 | $tag = (array)($tag ?? []); |
205 | $listId = $listId ? (array)$listId : null; |
206 | |
207 | $callback = function ($select) use ( |
208 | $tag, |
209 | $listId, |
210 | $publicOnly, |
211 | $andTags, |
212 | $caseSensitive |
213 | ) { |
214 | $select->columns( |
215 | ['id' => new Expression('min(resource_tags.id)'), 'list_id'] |
216 | ); |
217 | |
218 | $select->join( |
219 | ['t' => 'tags'], |
220 | 'resource_tags.tag_id = t.id', |
221 | [] |
222 | ); |
223 | $select->join( |
224 | ['l' => 'user_list'], |
225 | 'resource_tags.list_id = l.id', |
226 | [] |
227 | ); |
228 | |
229 | // Discard tags assigned to a user resource. |
230 | $select->where->isNull('resource_id'); |
231 | |
232 | // Restrict to tags by list owner |
233 | $select->where->and->equalTo( |
234 | 'resource_tags.user_id', |
235 | new Expression('l.user_id') |
236 | ); |
237 | |
238 | if ($listId) { |
239 | $select->where->and->in('resource_tags.list_id', $listId); |
240 | } |
241 | if ($publicOnly) { |
242 | $select->where->and->equalTo('public', 1); |
243 | } |
244 | if ($tag) { |
245 | if ($caseSensitive ?? $this->caseSensitive) { |
246 | $select->where->and->in('t.tag', $tag); |
247 | } else { |
248 | $lowerTags = array_map( |
249 | function ($t) { |
250 | return new Expression( |
251 | 'lower(?)', |
252 | [$t], |
253 | [Expression::TYPE_VALUE] |
254 | ); |
255 | }, |
256 | $tag |
257 | ); |
258 | $select->where->and->in( |
259 | new Expression('lower(t.tag)'), |
260 | $lowerTags |
261 | ); |
262 | } |
263 | } |
264 | $select->group('resource_tags.list_id'); |
265 | |
266 | if ($tag && $andTags) { |
267 | // Use AND operator for tags |
268 | $select->having->literal( |
269 | 'count(distinct(resource_tags.tag_id)) = ?', |
270 | count(array_unique($tag)) |
271 | ); |
272 | } |
273 | $select->order('resource_tags.list_id'); |
274 | }; |
275 | |
276 | return $this->select($callback); |
277 | } |
278 | |
279 | /** |
280 | * Get statistics on use of tags. |
281 | * |
282 | * @param bool $extended Include extended (unique/anonymous) stats. |
283 | * @param ?bool $caseSensitiveTags Should we treat tags as case-sensitive? (null for configured behavior) |
284 | * |
285 | * @return array |
286 | */ |
287 | public function getStatistics($extended = false, $caseSensitiveTags = null) |
288 | { |
289 | $select = $this->sql->select(); |
290 | $select->columns( |
291 | [ |
292 | 'users' => new Expression( |
293 | 'COUNT(DISTINCT(?))', |
294 | ['user_id'], |
295 | [Expression::TYPE_IDENTIFIER] |
296 | ), |
297 | 'resources' => new Expression( |
298 | 'COUNT(DISTINCT(?))', |
299 | ['resource_id'], |
300 | [Expression::TYPE_IDENTIFIER] |
301 | ), |
302 | 'total' => new Expression('COUNT(*)'), |
303 | ] |
304 | ); |
305 | $statement = $this->sql->prepareStatementForSqlObject($select); |
306 | $result = $statement->execute(); |
307 | $stats = (array)$result->current(); |
308 | if ($extended) { |
309 | $stats['unique'] = count($this->getUniqueTags(caseSensitive: $caseSensitiveTags)); |
310 | $stats['anonymous'] = $this->getAnonymousCount(); |
311 | } |
312 | return $stats; |
313 | } |
314 | |
315 | /** |
316 | * Unlink rows for the specified resource. |
317 | * |
318 | * @param string|array $resource ID (or array of IDs) of resource(s) to |
319 | * unlink (null for ALL matching resources) |
320 | * @param string $user ID of user removing links |
321 | * @param string $list ID of list to unlink (null for ALL matching |
322 | * tags, 'none' for tags not in a list, true for tags only found in a list) |
323 | * @param string|array $tag ID or array of IDs of tag(s) to unlink (null |
324 | * for ALL matching tags) |
325 | * |
326 | * @return void |
327 | * |
328 | * @deprecated Use ResourceTagsServiceInterface::destroyResourceTagsLinksForUser() or |
329 | * ResourceTagsServiceInterface::destroyNonListResourceTagsLinksForUser() or |
330 | * ResourceTagsServiceInterface::destroyAllListResourceTagsLinksForUser() |
331 | */ |
332 | public function destroyResourceLinks($resource, $user, $list = null, $tag = null) |
333 | { |
334 | $callback = function ($select) use ($resource, $user, $list, $tag) { |
335 | $select->where->equalTo('user_id', $user); |
336 | if (null !== $resource) { |
337 | $select->where->in('resource_id', (array)$resource); |
338 | } |
339 | if (null !== $list) { |
340 | if (true === $list) { |
341 | // special case -- if $list is set to boolean true, we |
342 | // want to only delete tags that are associated with lists. |
343 | $select->where->isNotNull('list_id'); |
344 | } elseif ('none' === $list) { |
345 | // special case -- if $list is set to the string "none", we |
346 | // want to delete tags that are not associated with lists. |
347 | $select->where->isNull('list_id'); |
348 | } else { |
349 | $select->where->equalTo('list_id', $list); |
350 | } |
351 | } |
352 | if (null !== $tag) { |
353 | if (is_array($tag)) { |
354 | $select->where->in('tag_id', $tag); |
355 | } else { |
356 | $select->where->equalTo('tag_id', $tag); |
357 | } |
358 | } |
359 | }; |
360 | $this->processDestroyLinks($callback); |
361 | } |
362 | |
363 | /** |
364 | * Unlink rows for the specified user list. |
365 | * |
366 | * @param string $list ID of list to unlink |
367 | * @param string $user ID of user removing links |
368 | * @param string|array $tag ID or array of IDs of tag(s) to unlink (null |
369 | * for ALL matching tags) |
370 | * |
371 | * @return void |
372 | * |
373 | * @deprecated Use ResourceTagsServiceInterface::destroyUserListLinks() |
374 | */ |
375 | public function destroyListLinks($list, $user, $tag = null) |
376 | { |
377 | $callback = function ($select) use ($user, $list, $tag) { |
378 | $select->where->equalTo('user_id', $user); |
379 | // retrieve tags assigned to a user list |
380 | // and filter out user resource tags |
381 | // (resource_id is NULL for list tags). |
382 | $select->where->isNull('resource_id'); |
383 | $select->where->equalTo('list_id', $list); |
384 | |
385 | if (null !== $tag) { |
386 | if (is_array($tag)) { |
387 | $select->where->in('tag_id', $tag); |
388 | } else { |
389 | $select->where->equalTo('tag_id', $tag); |
390 | } |
391 | } |
392 | }; |
393 | $this->processDestroyLinks($callback); |
394 | } |
395 | |
396 | /** |
397 | * Process link rows marked to be destroyed. |
398 | * |
399 | * @param Object $callback Callback function for selecting deleted rows. |
400 | * |
401 | * @return void |
402 | * |
403 | * @deprecated |
404 | */ |
405 | protected function processDestroyLinks($callback) |
406 | { |
407 | // Get a list of all tag IDs being deleted; we'll use these for |
408 | // orphan-checking: |
409 | $potentialOrphans = $this->select($callback); |
410 | |
411 | // Now delete the unwanted rows: |
412 | $this->delete($callback); |
413 | |
414 | // Check for orphans: |
415 | if (count($potentialOrphans) > 0) { |
416 | $ids = []; |
417 | foreach ($potentialOrphans as $current) { |
418 | $ids[] = $current->tag_id; |
419 | } |
420 | $checkResults = $this->checkForTags(array_unique($ids)); |
421 | if (count($checkResults['missing']) > 0) { |
422 | $tagTable = $this->getDbTable('Tags'); |
423 | $tagTable->deleteByIdArray($checkResults['missing']); |
424 | } |
425 | } |
426 | } |
427 | |
428 | /** |
429 | * Get count of anonymous tags |
430 | * |
431 | * @return int count |
432 | */ |
433 | public function getAnonymousCount() |
434 | { |
435 | $callback = function ($select) { |
436 | $select->where->isNull('user_id'); |
437 | }; |
438 | return count($this->select($callback)); |
439 | } |
440 | |
441 | /** |
442 | * Assign anonymous tags to the specified user ID. |
443 | * |
444 | * @param int $id User ID to own anonymous tags. |
445 | * |
446 | * @return void |
447 | */ |
448 | public function assignAnonymousTags($id) |
449 | { |
450 | $callback = function ($select) { |
451 | $select->where->isNull('user_id'); |
452 | }; |
453 | $this->update(['user_id' => $id], $callback); |
454 | } |
455 | |
456 | /** |
457 | * Gets unique resources from the table |
458 | * |
459 | * @param string $userId ID of user |
460 | * @param string $resourceId ID of the resource |
461 | * @param string $tagId ID of the tag |
462 | * |
463 | * @return \Laminas\Db\ResultSet\AbstractResultSet |
464 | */ |
465 | public function getUniqueResources( |
466 | $userId = null, |
467 | $resourceId = null, |
468 | $tagId = null |
469 | ) { |
470 | $callback = function ($select) use ($userId, $resourceId, $tagId) { |
471 | $select->columns( |
472 | [ |
473 | 'resource_id' => new Expression( |
474 | 'MAX(?)', |
475 | ['resource_tags.resource_id'], |
476 | [Expression::TYPE_IDENTIFIER] |
477 | ), |
478 | 'tag_id' => new Expression( |
479 | 'MAX(?)', |
480 | ['resource_tags.tag_id'], |
481 | [Expression::TYPE_IDENTIFIER] |
482 | ), |
483 | 'list_id' => new Expression( |
484 | 'MAX(?)', |
485 | ['resource_tags.list_id'], |
486 | [Expression::TYPE_IDENTIFIER] |
487 | ), |
488 | 'user_id' => new Expression( |
489 | 'MAX(?)', |
490 | ['resource_tags.user_id'], |
491 | [Expression::TYPE_IDENTIFIER] |
492 | ), |
493 | 'id' => new Expression( |
494 | 'MAX(?)', |
495 | ['resource_tags.id'], |
496 | [Expression::TYPE_IDENTIFIER] |
497 | ), |
498 | ] |
499 | ); |
500 | $select->join( |
501 | ['r' => 'resource'], |
502 | 'resource_tags.resource_id = r.id', |
503 | ['title' => 'title'] |
504 | ); |
505 | if (null !== $userId) { |
506 | $select->where->equalTo('resource_tags.user_id', $userId); |
507 | } |
508 | if (null !== $resourceId) { |
509 | $select->where->equalTo('resource_tags.resource_id', $resourceId); |
510 | } |
511 | if (null !== $tagId) { |
512 | $select->where->equalTo('resource_tags.tag_id', $tagId); |
513 | } |
514 | $select->group(['resource_id', 'title']); |
515 | $select->order(['title']); |
516 | }; |
517 | return $this->select($callback); |
518 | } |
519 | |
520 | /** |
521 | * Gets unique tags from the table |
522 | * |
523 | * @param string $userId ID of user |
524 | * @param string $resourceId ID of the resource |
525 | * @param string $tagId ID of the tag |
526 | * @param ?bool $caseSensitive Should tags be case sensitive? (null to use configured default) |
527 | * |
528 | * @return \Laminas\Db\ResultSet\AbstractResultSet |
529 | */ |
530 | public function getUniqueTags($userId = null, $resourceId = null, $tagId = null, $caseSensitive = null) |
531 | { |
532 | $callback = function ($select) use ($userId, $resourceId, $tagId, $caseSensitive) { |
533 | $select->columns( |
534 | [ |
535 | 'resource_id' => new Expression( |
536 | 'MAX(?)', |
537 | ['resource_tags.resource_id'], |
538 | [Expression::TYPE_IDENTIFIER] |
539 | ), |
540 | 'tag_id' => new Expression( |
541 | 'MAX(?)', |
542 | ['resource_tags.tag_id'], |
543 | [Expression::TYPE_IDENTIFIER] |
544 | ), |
545 | 'list_id' => new Expression( |
546 | 'MAX(?)', |
547 | ['resource_tags.list_id'], |
548 | [Expression::TYPE_IDENTIFIER] |
549 | ), |
550 | 'user_id' => new Expression( |
551 | 'MAX(?)', |
552 | ['resource_tags.user_id'], |
553 | [Expression::TYPE_IDENTIFIER] |
554 | ), |
555 | 'id' => new Expression( |
556 | 'MAX(?)', |
557 | ['resource_tags.id'], |
558 | [Expression::TYPE_IDENTIFIER] |
559 | ), |
560 | ] |
561 | ); |
562 | $select->join( |
563 | ['t' => 'tags'], |
564 | 'resource_tags.tag_id = t.id', |
565 | [ |
566 | 'tag' => ($caseSensitive ?? $this->caseSensitive) ? 'tag' : new Expression('lower(tag)'), |
567 | ] |
568 | ); |
569 | if (null !== $userId) { |
570 | $select->where->equalTo('resource_tags.user_id', $userId); |
571 | } |
572 | if (null !== $resourceId) { |
573 | $select->where->equalTo('resource_tags.resource_id', $resourceId); |
574 | } |
575 | if (null !== $tagId) { |
576 | $select->where->equalTo('resource_tags.tag_id', $tagId); |
577 | } |
578 | $select->group(['tag_id', 'tag']); |
579 | $select->order([new Expression('lower(tag)'), 'tag']); |
580 | }; |
581 | return $this->select($callback); |
582 | } |
583 | |
584 | /** |
585 | * Gets unique users from the table |
586 | * |
587 | * @param string $userId ID of user |
588 | * @param string $resourceId ID of the resource |
589 | * @param string $tagId ID of the tag |
590 | * |
591 | * @return \Laminas\Db\ResultSet\AbstractResultSet |
592 | */ |
593 | public function getUniqueUsers($userId = null, $resourceId = null, $tagId = null) |
594 | { |
595 | $callback = function ($select) use ($userId, $resourceId, $tagId) { |
596 | $select->columns( |
597 | [ |
598 | 'resource_id' => new Expression( |
599 | 'MAX(?)', |
600 | ['resource_tags.resource_id'], |
601 | [Expression::TYPE_IDENTIFIER] |
602 | ), |
603 | 'tag_id' => new Expression( |
604 | 'MAX(?)', |
605 | ['resource_tags.tag_id'], |
606 | [Expression::TYPE_IDENTIFIER] |
607 | ), |
608 | 'list_id' => new Expression( |
609 | 'MAX(?)', |
610 | ['resource_tags.list_id'], |
611 | [Expression::TYPE_IDENTIFIER] |
612 | ), |
613 | 'user_id' => new Expression( |
614 | 'MAX(?)', |
615 | ['resource_tags.user_id'], |
616 | [Expression::TYPE_IDENTIFIER] |
617 | ), |
618 | 'id' => new Expression( |
619 | 'MAX(?)', |
620 | ['resource_tags.id'], |
621 | [Expression::TYPE_IDENTIFIER] |
622 | ), |
623 | ] |
624 | ); |
625 | $select->join( |
626 | ['u' => 'user'], |
627 | 'resource_tags.user_id = u.id', |
628 | ['username' => 'username'] |
629 | ); |
630 | if (null !== $userId) { |
631 | $select->where->equalTo('resource_tags.user_id', $userId); |
632 | } |
633 | if (null !== $resourceId) { |
634 | $select->where->equalTo('resource_tags.resource_id', $resourceId); |
635 | } |
636 | if (null !== $tagId) { |
637 | $select->where->equalTo('resource_tags.tag_id', $tagId); |
638 | } |
639 | $select->group(['user_id', 'username']); |
640 | $select->order(['username']); |
641 | }; |
642 | return $this->select($callback); |
643 | } |
644 | |
645 | /** |
646 | * Given an array for sorting database results, make sure the tag field is |
647 | * sorted in a case-insensitive fashion. |
648 | * |
649 | * @param array $order Order settings |
650 | * |
651 | * @return array |
652 | */ |
653 | protected function formatTagOrder($order) |
654 | { |
655 | if (empty($order)) { |
656 | return $order; |
657 | } |
658 | $newOrder = []; |
659 | foreach ((array)$order as $current) { |
660 | $newOrder[] = $current == 'tag' |
661 | ? new Expression('lower(tag)') : $current; |
662 | } |
663 | return $newOrder; |
664 | } |
665 | |
666 | /** |
667 | * Get Resource Tags |
668 | * |
669 | * @param string $userId ID of user |
670 | * @param string $resourceId ID of the resource |
671 | * @param string $tagId ID of the tag |
672 | * @param string $order The order in which to return the data |
673 | * @param string $page The page number to select |
674 | * @param string $limit The number of items to fetch |
675 | * @param ?bool $caseSensitive Should tags be case sensitive? (null to use configured default) |
676 | * |
677 | * @return \Laminas\Paginator\Paginator |
678 | */ |
679 | public function getResourceTags( |
680 | $userId = null, |
681 | $resourceId = null, |
682 | $tagId = null, |
683 | $order = null, |
684 | $page = null, |
685 | $limit = 20, |
686 | $caseSensitive = null |
687 | ) { |
688 | $order = (null !== $order) |
689 | ? [$order] |
690 | : ['username', 'tag', 'title']; |
691 | |
692 | $sql = $this->getSql(); |
693 | $select = $sql->select(); |
694 | $select->join( |
695 | ['t' => 'tags'], |
696 | 'resource_tags.tag_id = t.id', |
697 | [ |
698 | 'tag' => ($caseSensitive ?? $this->caseSensitive) ? 'tag' : new Expression('lower(tag)'), |
699 | ] |
700 | ); |
701 | $select->join( |
702 | ['u' => 'user'], |
703 | 'resource_tags.user_id = u.id', |
704 | ['username' => 'username'] |
705 | ); |
706 | $select->join( |
707 | ['r' => 'resource'], |
708 | 'resource_tags.resource_id = r.id', |
709 | ['title' => 'title'] |
710 | ); |
711 | if (null !== $userId) { |
712 | $select->where->equalTo('resource_tags.user_id', $userId); |
713 | } |
714 | if (null !== $resourceId) { |
715 | $select->where->equalTo('resource_tags.resource_id', $resourceId); |
716 | } |
717 | if (null !== $tagId) { |
718 | $select->where->equalTo('resource_tags.tag_id', $tagId); |
719 | } |
720 | $select->order($this->formatTagOrder($order)); |
721 | |
722 | if (null !== $page) { |
723 | $select->limit($limit); |
724 | $select->offset($limit * ($page - 1)); |
725 | } |
726 | |
727 | $adapter = new \Laminas\Paginator\Adapter\LaminasDb\DbSelect($select, $sql); |
728 | $paginator = new \Laminas\Paginator\Paginator($adapter); |
729 | $paginator->setItemCountPerPage($limit); |
730 | if (null !== $page) { |
731 | $paginator->setCurrentPageNumber($page); |
732 | } |
733 | return $paginator; |
734 | } |
735 | |
736 | /** |
737 | * Delete a group of tags. |
738 | * |
739 | * @param array $ids IDs of tags to delete. |
740 | * |
741 | * @return int Count of $ids |
742 | */ |
743 | public function deleteByIdArray($ids) |
744 | { |
745 | // Do nothing if we have no IDs to delete! |
746 | if (empty($ids)) { |
747 | return; |
748 | } |
749 | |
750 | $callback = function ($select) use ($ids) { |
751 | $select->where->in('id', $ids); |
752 | }; |
753 | $this->delete($callback); |
754 | return count($ids); |
755 | } |
756 | |
757 | /** |
758 | * Get a list of duplicate rows (this sometimes happens after merging IDs, |
759 | * for example after a Summon resource ID changes). |
760 | * |
761 | * @return mixed |
762 | */ |
763 | public function getDuplicates() |
764 | { |
765 | $callback = function ($select) { |
766 | $select->columns( |
767 | [ |
768 | 'resource_id' => new Expression( |
769 | 'MIN(?)', |
770 | ['resource_id'], |
771 | [Expression::TYPE_IDENTIFIER] |
772 | ), |
773 | 'tag_id' => new Expression( |
774 | 'MIN(?)', |
775 | ['tag_id'], |
776 | [Expression::TYPE_IDENTIFIER] |
777 | ), |
778 | 'list_id' => new Expression( |
779 | 'MIN(?)', |
780 | ['list_id'], |
781 | [Expression::TYPE_IDENTIFIER] |
782 | ), |
783 | 'user_id' => new Expression( |
784 | 'MIN(?)', |
785 | ['user_id'], |
786 | [Expression::TYPE_IDENTIFIER] |
787 | ), |
788 | 'cnt' => new Expression( |
789 | 'COUNT(?)', |
790 | ['resource_id'], |
791 | [Expression::TYPE_IDENTIFIER] |
792 | ), |
793 | 'id' => new Expression( |
794 | 'MIN(?)', |
795 | ['id'], |
796 | [Expression::TYPE_IDENTIFIER] |
797 | ), |
798 | ] |
799 | ); |
800 | $select->group(['resource_id', 'tag_id', 'list_id', 'user_id']); |
801 | $select->having('COUNT(resource_id) > 1'); |
802 | }; |
803 | return $this->select($callback); |
804 | } |
805 | |
806 | /** |
807 | * Deduplicate rows (sometimes necessary after merging foreign key IDs). |
808 | * |
809 | * @return void |
810 | */ |
811 | public function deduplicate() |
812 | { |
813 | foreach ($this->getDuplicates() as $dupe) { |
814 | $callback = function ($select) use ($dupe) { |
815 | // match on all relevant IDs in duplicate group |
816 | $select->where( |
817 | [ |
818 | 'resource_id' => $dupe['resource_id'], |
819 | 'tag_id' => $dupe['tag_id'], |
820 | 'list_id' => $dupe['list_id'], |
821 | 'user_id' => $dupe['user_id'], |
822 | ] |
823 | ); |
824 | // getDuplicates returns the minimum id in the set, so we want to |
825 | // delete all of the duplicates with a higher id value. |
826 | $select->where->greaterThan('id', $dupe['id']); |
827 | }; |
828 | $this->delete($callback); |
829 | } |
830 | } |
831 | } |