1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
<?php
namespace Wallabag\CoreBundle\Repository;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\QueryBuilder;
use Wallabag\CoreBundle\Entity\Tag;
class TagRepository extends EntityRepository
{
/**
* Count all tags per user.
*
* @param int $userId
* @param int $cacheLifeTime Duration of the cache for this query
*
* @return int
*/
public function countAllTags($userId, $cacheLifeTime = null)
{
$query = $this->createQueryBuilder('t')
->select('t.slug')
->leftJoin('t.entries', 'e')
->where('e.user = :userId')->setParameter('userId', $userId)
->groupBy('t.slug')
->getQuery();
if (null !== $cacheLifeTime) {
$query->useQueryCache(true);
$query->useResultCache(true);
$query->setResultCacheLifetime($cacheLifeTime);
}
return \count($query->getArrayResult());
}
/**
* Find all tags per user.
* Instead of just left joined on the Entry table, we select only id and group by id to avoid tag multiplication in results.
* Once we have all tags id, we can safely request them one by one.
* This'll still be fastest than the previous query.
*
* @param int $userId
*
* @return array
*/
public function findAllTags($userId)
{
$ids = $this->getQueryBuilderByUser($userId)
->select('t.id')
->getQuery()
->getArrayResult();
$tags = [];
foreach ($ids as $id) {
$tags[] = $this->find($id);
}
return $tags;
}
/**
* Find all tags (flat) per user with nb entries.
*
* @param int $userId
*
* @return array
*/
public function findAllFlatTagsWithNbEntries($userId)
{
return $this->getQueryBuilderByUser($userId)
->select('t.id, t.label, t.slug, count(e.id) as nbEntries')
->distinct(true)
->getQuery()
->getArrayResult();
}
public function findByLabelsAndUser($labels, $userId)
{
$qb = $this->getQueryBuilderByUser($userId)
->select('t.id');
$ids = $qb->andWhere($qb->expr()->in('t.label', $labels))
->getQuery()
->getArrayResult();
$tags = [];
foreach ($ids as $id) {
$tags[] = $this->find($id);
}
return $tags;
}
/**
* Used only in test case to get a tag for our entry.
*
* @return Tag
*/
public function findOneByEntryAndTagLabel($entry, $label)
{
return $this->createQueryBuilder('t')
->leftJoin('t.entries', 'e')
->where('e.id = :entryId')->setParameter('entryId', $entry->getId())
->andWhere('t.label = :label')->setParameter('label', $label)
->setMaxResults(1)
->getQuery()
->getSingleResult();
}
public function findForArchivedArticlesByUser($userId)
{
$ids = $this->getQueryBuilderByUser($userId)
->select('t.id')
->andWhere('e.isArchived = true')
->getQuery()
->getArrayResult();
$tags = [];
foreach ($ids as $id) {
$tags[] = $this->find($id);
}
return $tags;
}
/**
* Retrieve a sorted list of tags used by a user.
*
* @param int $userId
*
* @return QueryBuilder
*/
private function getQueryBuilderByUser($userId)
{
return $this->createQueryBuilder('t')
->leftJoin('t.entries', 'e')
->where('e.user = :userId')->setParameter('userId', $userId)
->groupBy('t.id')
->orderBy('t.slug');
}
}
|
