]> git.immae.eu Git - github/wallabag/wallabag.git/blob - src/Wallabag/CoreBundle/Repository/EntryRepository.php
Merge pull request #2454 from wallabag/fix-baggy-small-width
[github/wallabag/wallabag.git] / src / Wallabag / CoreBundle / Repository / EntryRepository.php
1 <?php
2
3 namespace Wallabag\CoreBundle\Repository;
4
5 use Doctrine\ORM\EntityRepository;
6 use Doctrine\ORM\Query;
7 use Pagerfanta\Adapter\DoctrineORMAdapter;
8 use Pagerfanta\Pagerfanta;
9 use Wallabag\CoreBundle\Entity\Tag;
10
11 class EntryRepository extends EntityRepository
12 {
13 /**
14 * Return a query builder to used by other getBuilderFor* method.
15 *
16 * @param int $userId
17 *
18 * @return QueryBuilder
19 */
20 private function getBuilderByUser($userId)
21 {
22 return $this->createQueryBuilder('e')
23 ->leftJoin('e.user', 'u')
24 ->andWhere('u.id = :userId')->setParameter('userId', $userId)
25 ->orderBy('e.id', 'desc')
26 ;
27 }
28
29 /**
30 * Retrieves all entries for a user.
31 *
32 * @param int $userId
33 *
34 * @return QueryBuilder
35 */
36 public function getBuilderForAllByUser($userId)
37 {
38 return $this
39 ->getBuilderByUser($userId)
40 ;
41 }
42
43 /**
44 * Retrieves unread entries for a user.
45 *
46 * @param int $userId
47 *
48 * @return QueryBuilder
49 */
50 public function getBuilderForUnreadByUser($userId)
51 {
52 return $this
53 ->getBuilderByUser($userId)
54 ->andWhere('e.isArchived = false')
55 ;
56 }
57
58 /**
59 * Retrieves read entries for a user.
60 *
61 * @param int $userId
62 *
63 * @return QueryBuilder
64 */
65 public function getBuilderForArchiveByUser($userId)
66 {
67 return $this
68 ->getBuilderByUser($userId)
69 ->andWhere('e.isArchived = true')
70 ;
71 }
72
73 /**
74 * Retrieves starred entries for a user.
75 *
76 * @param int $userId
77 *
78 * @return QueryBuilder
79 */
80 public function getBuilderForStarredByUser($userId)
81 {
82 return $this
83 ->getBuilderByUser($userId)
84 ->andWhere('e.isStarred = true')
85 ;
86 }
87
88 /**
89 * Retrieves untagged entries for a user.
90 *
91 * @param int $userId
92 *
93 * @return QueryBuilder
94 */
95 public function getBuilderForUntaggedByUser($userId)
96 {
97 return $this
98 ->getBuilderByUser($userId)
99 ->leftJoin('e.tags', 't')
100 ->groupBy('e.id')
101 ->having('count(t.id) = 0');
102 }
103
104 /**
105 * Find Entries.
106 *
107 * @param int $userId
108 * @param bool $isArchived
109 * @param bool $isStarred
110 * @param string $sort
111 * @param string $order
112 * @param int $since
113 * @param string $tags
114 *
115 * @return array
116 */
117 public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
118 {
119 $qb = $this->createQueryBuilder('e')
120 ->leftJoin('e.tags', 't')
121 ->where('e.user =:userId')->setParameter('userId', $userId);
122
123 if (null !== $isArchived) {
124 $qb->andWhere('e.isArchived =:isArchived')->setParameter('isArchived', (bool) $isArchived);
125 }
126
127 if (null !== $isStarred) {
128 $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred);
129 }
130
131 if ($since > 0) {
132 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
133 }
134
135 if ('' !== $tags) {
136 foreach (explode(',', $tags) as $tag) {
137 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
138 }
139 }
140
141 if ('created' === $sort) {
142 $qb->orderBy('e.id', $order);
143 } elseif ('updated' === $sort) {
144 $qb->orderBy('e.updatedAt', $order);
145 }
146
147 $pagerAdapter = new DoctrineORMAdapter($qb);
148
149 return new Pagerfanta($pagerAdapter);
150 }
151
152 /**
153 * Fetch an entry with a tag. Only used for tests.
154 *
155 * @param int $userId
156 *
157 * @return Entry
158 */
159 public function findOneWithTags($userId)
160 {
161 $qb = $this->createQueryBuilder('e')
162 ->innerJoin('e.tags', 't')
163 ->innerJoin('e.user', 'u')
164 ->addSelect('t', 'u')
165 ->where('e.user=:userId')->setParameter('userId', $userId)
166 ;
167
168 return $qb->getQuery()->getResult();
169 }
170
171 /**
172 * Find distinct language for a given user.
173 * Used to build the filter language list.
174 *
175 * @param int $userId User id
176 *
177 * @return array
178 */
179 public function findDistinctLanguageByUser($userId)
180 {
181 $results = $this->createQueryBuilder('e')
182 ->select('e.language')
183 ->where('e.user = :userId')->setParameter('userId', $userId)
184 ->andWhere('e.language IS NOT NULL')
185 ->groupBy('e.language')
186 ->orderBy('e.language', ' ASC')
187 ->getQuery()
188 ->getResult();
189
190 $languages = [];
191 foreach ($results as $result) {
192 $languages[$result['language']] = $result['language'];
193 }
194
195 return $languages;
196 }
197
198 /**
199 * Used only in test case to get the right entry associated to the right user.
200 *
201 * @param string $username
202 *
203 * @return Entry
204 */
205 public function findOneByUsernameAndNotArchived($username)
206 {
207 return $this->createQueryBuilder('e')
208 ->leftJoin('e.user', 'u')
209 ->where('u.username = :username')->setParameter('username', $username)
210 ->andWhere('e.isArchived = false')
211 ->setMaxResults(1)
212 ->getQuery()
213 ->getSingleResult();
214 }
215
216 /**
217 * Remove a tag from all user entries.
218 *
219 * We need to loop on each entry attached to the given tag to remove it, since Doctrine doesn't know EntryTag entity because it's a ManyToMany relation.
220 * It could be faster with one query but I don't know how to retrieve the table name `entry_tag` which can have a prefix:
221 *
222 * DELETE et FROM entry_tag et WHERE et.entry_id IN ( SELECT e.id FROM entry e WHERE e.user_id = :userId ) AND et.tag_id = :tagId
223 *
224 * @param int $userId
225 * @param Tag $tag
226 */
227 public function removeTag($userId, Tag $tag)
228 {
229 $entries = $this->getBuilderByUser($userId)
230 ->innerJoin('e.tags', 't')
231 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
232 ->getQuery()
233 ->getResult();
234
235 foreach ($entries as $entry) {
236 $entry->removeTag($tag);
237 }
238
239 $this->getEntityManager()->flush();
240 }
241
242 /**
243 * Remove tags from all user entries.
244 *
245 * @param int $userId
246 * @param Array<Tag> $tags
247 */
248 public function removeTags($userId, $tags)
249 {
250 foreach ($tags as $tag) {
251 $this->removeTag($userId, $tag);
252 }
253 }
254
255 /**
256 * Find all entries that are attached to a give tag id.
257 *
258 * @param int $userId
259 * @param int $tagId
260 *
261 * @return array
262 */
263 public function findAllByTagId($userId, $tagId)
264 {
265 return $this->getBuilderByUser($userId)
266 ->innerJoin('e.tags', 't')
267 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
268 ->getQuery()
269 ->getResult();
270 }
271
272 /**
273 * Find an entry by its url and its owner.
274 * If it exists, return the entry otherwise return false.
275 *
276 * @param $url
277 * @param $userId
278 *
279 * @return Entry|bool
280 */
281 public function findByUrlAndUserId($url, $userId)
282 {
283 $res = $this->createQueryBuilder('e')
284 ->where('e.url = :url')->setParameter('url', urldecode($url))
285 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
286 ->getQuery()
287 ->getResult();
288
289 if (count($res)) {
290 return current($res);
291 }
292
293 return false;
294 }
295
296 /**
297 * Count all entries for a user.
298 *
299 * @param int $userId
300 *
301 * @return int
302 */
303 public function countAllEntriesByUsername($userId)
304 {
305 $qb = $this->createQueryBuilder('e')
306 ->select('count(e)')
307 ->where('e.user=:userId')->setParameter('userId', $userId)
308 ;
309
310 return $qb->getQuery()->getSingleScalarResult();
311 }
312
313 /**
314 * Count all entries for a tag and a user.
315 *
316 * @param int $userId
317 * @param int $tagId
318 *
319 * @return int
320 */
321 public function countAllEntriesByUserIdAndTagId($userId, $tagId)
322 {
323 $qb = $this->createQueryBuilder('e')
324 ->select('count(e.id)')
325 ->leftJoin('e.tags', 't')
326 ->where('e.user=:userId')->setParameter('userId', $userId)
327 ->andWhere('t.id=:tagId')->setParameter('tagId', $tagId)
328 ;
329
330 return $qb->getQuery()->getSingleScalarResult();
331 }
332 }