Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.69% covered (danger)
0.69%
1 / 145
5.26% covered (danger)
5.26%
1 / 19
CRAP
0.00% covered (danger)
0.00%
0 / 1
ResourceTags
0.69% covered (danger)
0.69%
1 / 145
5.26% covered (danger)
5.26%
1 / 19
4332.50
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
1 / 1
100.00% covered (success)
100.00%
1 / 1
1
 createLink
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
6
 checkForTags
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
20
 getResourcesForTag
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
12
 getListsForTag
0.00% covered (danger)
0.00%
0 / 11
0.00% covered (danger)
0.00%
0 / 1
72
 getStatistics
0.00% covered (danger)
0.00%
0 / 23
0.00% covered (danger)
0.00%
0 / 1
6
 destroyResourceLinks
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
56
 destroyListLinks
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
12
 processDestroyLinks
0.00% covered (danger)
0.00%
0 / 10
0.00% covered (danger)
0.00%
0 / 1
20
 getAnonymousCount
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
2
 assignAnonymousTags
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
2
 getUniqueResources
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
20
 getUniqueTags
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
30
 getUniqueUsers
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
20
 formatTagOrder
0.00% covered (danger)
0.00%
0 / 7
0.00% covered (danger)
0.00%
0 / 1
20
 getResourceTags
0.00% covered (danger)
0.00%
0 / 38
0.00% covered (danger)
0.00%
0 / 1
72
 deleteByIdArray
0.00% covered (danger)
0.00%
0 / 6
0.00% covered (danger)
0.00%
0 / 1
6
 getDuplicates
0.00% covered (danger)
0.00%
0 / 3
0.00% covered (danger)
0.00%
0 / 1
2
 deduplicate
0.00% covered (danger)
0.00%
0 / 4
0.00% covered (danger)
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
30namespace VuFind\Db\Table;
31
32use DateTime;
33use Laminas\Db\Adapter\Adapter;
34use Laminas\Db\Sql\Expression;
35use Laminas\Db\Sql\Select;
36use VuFind\Db\Row\RowGateway;
37use VuFind\Db\Service\DbServiceAwareInterface;
38use VuFind\Db\Service\DbServiceAwareTrait;
39use VuFind\Db\Service\ResourceTagsServiceInterface;
40
41use function count;
42use function in_array;
43use 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 */
54class 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}