]> git.immae.eu Git - github/wallabag/wallabag.git/blob - src/Wallabag/CoreBundle/Repository/EntryRepository.php
Fixed repository to retrieve articles
[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\NoResultException;
7 use Doctrine\ORM\QueryBuilder;
8 use Pagerfanta\Adapter\DoctrineORMAdapter;
9 use Pagerfanta\Pagerfanta;
10 use Wallabag\CoreBundle\Entity\Entry;
11 use Wallabag\CoreBundle\Entity\Tag;
12 use Wallabag\CoreBundle\Helper\UrlHasher;
13
14 class EntryRepository extends EntityRepository
15 {
16 /**
17 * Retrieves all entries for a user.
18 *
19 * @param int $userId
20 *
21 * @return QueryBuilder
22 */
23 public function getBuilderForAllByUser($userId)
24 {
25 return $this
26 ->getSortedQueryBuilderByUser($userId)
27 ;
28 }
29
30 /**
31 * Retrieves unread entries for a user.
32 *
33 * @param int $userId
34 *
35 * @return QueryBuilder
36 */
37 public function getBuilderForUnreadByUser($userId)
38 {
39 return $this
40 ->getSortedQueryBuilderByUser($userId)
41 ->andWhere('e.isArchived = false')
42 ;
43 }
44
45 /**
46 * Retrieves entries with the same domain.
47 *
48 * @param int $userId
49 * @param int $entryId
50 *
51 * @return QueryBuilder
52 */
53 public function getBuilderForSameDomainByUser($userId, $entryId)
54 {
55 $queryBuilder = $this->createQueryBuilder('e');
56
57 return $this
58 ->getSortedQueryBuilderByUser($userId)
59 ->andWhere('e.id <> :entryId')->setParameter('entryId', $entryId)
60 ->andWhere(
61 $queryBuilder->expr()->in(
62 'e.domainName',
63 $this
64 ->createQueryBuilder('e2')
65 ->select('e2.domainName')
66 ->where('e2.id = :entryId')->setParameter('entryId', $entryId)
67 ->getDQL()
68 )
69 );
70 }
71
72 /**
73 * Retrieves read entries for a user.
74 *
75 * @param int $userId
76 *
77 * @return QueryBuilder
78 */
79 public function getBuilderForArchiveByUser($userId)
80 {
81 return $this
82 ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
83 ->andWhere('e.isArchived = true')
84 ;
85 }
86
87 /**
88 * Retrieves starred entries for a user.
89 *
90 * @param int $userId
91 *
92 * @return QueryBuilder
93 */
94 public function getBuilderForStarredByUser($userId)
95 {
96 return $this
97 ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
98 ->andWhere('e.isStarred = true')
99 ;
100 }
101
102 /**
103 * Retrieves entries filtered with a search term for a user.
104 *
105 * @param int $userId
106 * @param string $term
107 * @param string $currentRoute
108 *
109 * @return QueryBuilder
110 */
111 public function getBuilderForSearchByUser($userId, $term, $currentRoute)
112 {
113 $qb = $this
114 ->getSortedQueryBuilderByUser($userId);
115
116 if ('starred' === $currentRoute) {
117 $qb->andWhere('e.isStarred = true');
118 } elseif ('unread' === $currentRoute) {
119 $qb->andWhere('e.isArchived = false');
120 } elseif ('archive' === $currentRoute) {
121 $qb->andWhere('e.isArchived = true');
122 }
123
124 // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
125 $qb
126 ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
127 ->leftJoin('e.tags', 't')
128 ->groupBy('e.id');
129
130 return $qb;
131 }
132
133 /**
134 * Retrieve a sorted list of untagged entries for a user.
135 *
136 * @param int $userId
137 *
138 * @return QueryBuilder
139 */
140 public function getBuilderForUntaggedByUser($userId)
141 {
142 return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
143 }
144
145 /**
146 * Retrieve untagged entries for a user.
147 *
148 * @param int $userId
149 *
150 * @return QueryBuilder
151 */
152 public function getRawBuilderForUntaggedByUser($userId)
153 {
154 return $this->getQueryBuilderByUser($userId)
155 ->leftJoin('e.tags', 't')
156 ->andWhere('t.id is null');
157 }
158
159 /**
160 * Retrieve the number of untagged entries for a user.
161 *
162 * @param int $userId
163 *
164 * @return int
165 */
166 public function countUntaggedEntriesByUser($userId)
167 {
168 return (int) $this->getRawBuilderForUntaggedByUser($userId)
169 ->select('count(e.id)')
170 ->getQuery()
171 ->getSingleScalarResult();
172 }
173
174 /**
175 * Find Entries.
176 *
177 * @param int $userId
178 * @param bool $isArchived
179 * @param bool $isStarred
180 * @param bool $isPublic
181 * @param string $sort
182 * @param string $order
183 * @param int $since
184 * @param string $tags
185 * @param string $detail 'metadata' or 'full'. Include content field if 'full'
186 *
187 * @todo Breaking change: replace default detail=full by detail=metadata in a future version
188 *
189 * @return Pagerfanta
190 */
191 public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '', $detail = 'full')
192 {
193 if (!\in_array(strtolower($detail), ['full', 'metadata'], true)) {
194 throw new \Exception('Detail "' . $detail . '" parameter is wrong, allowed: full or metadata');
195 }
196
197 $qb = $this->createQueryBuilder('e')
198 ->leftJoin('e.tags', 't')
199 ->where('e.user = :userId')->setParameter('userId', $userId);
200
201 if ('metadata' === $detail) {
202 $fieldNames = $this->getClassMetadata()->getFieldNames();
203 $fields = array_filter($fieldNames, function ($k) {
204 return 'content' !== $k;
205 });
206 $qb->select(sprintf('partial e.{%s}', implode(',', $fields)));
207 }
208
209 if (null !== $isArchived) {
210 $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
211 }
212
213 if (null !== $isStarred) {
214 $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
215 }
216
217 if (null !== $isPublic) {
218 $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
219 }
220
221 if ($since > 0) {
222 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
223 }
224
225 if (\is_string($tags) && '' !== $tags) {
226 foreach (explode(',', $tags) as $i => $tag) {
227 $entryAlias = 'e' . $i;
228 $tagAlias = 't' . $i;
229
230 // Complexe queries to ensure multiple tags are associated to an entry
231 // https://stackoverflow.com/a/6638146/569101
232 $qb->andWhere($qb->expr()->in(
233 'e.id',
234 $this->createQueryBuilder($entryAlias)
235 ->select($entryAlias . '.id')
236 ->leftJoin($entryAlias . '.tags', $tagAlias)
237 ->where($tagAlias . '.label = :label' . $i)
238 ->getDQL()
239 ));
240
241 // bound parameter to the main query builder
242 $qb->setParameter('label' . $i, $tag);
243 }
244 }
245
246 if (!\in_array(strtolower($order), ['asc', 'desc'], true)) {
247 throw new \Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
248 }
249
250 if ('created' === $sort) {
251 $qb->orderBy('e.id', $order);
252 } elseif ('updated' === $sort) {
253 $qb->orderBy('e.updatedAt', $order);
254 } elseif ('archived' === $sort) {
255 $qb->orderBy('e.archivedAt', $order);
256 }
257
258 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
259
260 return new Pagerfanta($pagerAdapter);
261 }
262
263 /**
264 * Fetch an entry with a tag. Only used for tests.
265 *
266 * @param int $userId
267 *
268 * @return array
269 */
270 public function findOneWithTags($userId)
271 {
272 $qb = $this->createQueryBuilder('e')
273 ->innerJoin('e.tags', 't')
274 ->innerJoin('e.user', 'u')
275 ->addSelect('t', 'u')
276 ->where('e.user = :userId')->setParameter('userId', $userId)
277 ;
278
279 return $qb->getQuery()->getResult();
280 }
281
282 /**
283 * Find distinct language for a given user.
284 * Used to build the filter language list.
285 *
286 * @param int $userId User id
287 *
288 * @return array
289 */
290 public function findDistinctLanguageByUser($userId)
291 {
292 $results = $this->createQueryBuilder('e')
293 ->select('e.language')
294 ->where('e.user = :userId')->setParameter('userId', $userId)
295 ->andWhere('e.language IS NOT NULL')
296 ->groupBy('e.language')
297 ->orderBy('e.language', ' ASC')
298 ->getQuery()
299 ->getResult();
300
301 $languages = [];
302 foreach ($results as $result) {
303 $languages[$result['language']] = $result['language'];
304 }
305
306 return $languages;
307 }
308
309 /**
310 * Used only in test case to get the right entry associated to the right user.
311 *
312 * @param string $username
313 *
314 * @return Entry
315 */
316 public function findOneByUsernameAndNotArchived($username)
317 {
318 return $this->createQueryBuilder('e')
319 ->leftJoin('e.user', 'u')
320 ->where('u.username = :username')->setParameter('username', $username)
321 ->andWhere('e.isArchived = false')
322 ->setMaxResults(1)
323 ->getQuery()
324 ->getSingleResult();
325 }
326
327 /**
328 * Remove a tag from all user entries.
329 *
330 * 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.
331 * 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:
332 *
333 * 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
334 *
335 * @param int $userId
336 */
337 public function removeTag($userId, Tag $tag)
338 {
339 $entries = $this->getSortedQueryBuilderByUser($userId)
340 ->innerJoin('e.tags', 't')
341 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
342 ->getQuery()
343 ->getResult();
344
345 foreach ($entries as $entry) {
346 $entry->removeTag($tag);
347 }
348
349 $this->getEntityManager()->flush();
350 }
351
352 /**
353 * Remove tags from all user entries.
354 *
355 * @param int $userId
356 * @param Array<Tag> $tags
357 */
358 public function removeTags($userId, $tags)
359 {
360 foreach ($tags as $tag) {
361 $this->removeTag($userId, $tag);
362 }
363 }
364
365 /**
366 * Find all entries that are attached to a give tag id.
367 *
368 * @param int $userId
369 * @param int $tagId
370 *
371 * @return array
372 */
373 public function findAllByTagId($userId, $tagId)
374 {
375 return $this->getSortedQueryBuilderByUser($userId)
376 ->innerJoin('e.tags', 't')
377 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
378 ->getQuery()
379 ->getResult();
380 }
381
382 /**
383 * Find an entry by its url and its owner.
384 * If it exists, return the entry otherwise return false.
385 *
386 * @param string $url
387 * @param int $userId
388 *
389 * @return Entry|false
390 */
391 public function findByUrlAndUserId($url, $userId)
392 {
393 return $this->findByHashedUrlAndUserId(
394 UrlHasher::hashUrl($url),
395 $userId
396 );
397 }
398
399 /**
400 * Find an entry by its hashed url and its owner.
401 * If it exists, return the entry otherwise return false.
402 *
403 * @param string $hashedUrl Url hashed using sha1
404 * @param int $userId
405 *
406 * @return Entry|false
407 */
408 public function findByHashedUrlAndUserId($hashedUrl, $userId)
409 {
410 // try first using hashed_url (to use the database index)
411 $res = $this->createQueryBuilder('e')
412 ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl)
413 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
414 ->getQuery()
415 ->getResult();
416
417 if (\count($res)) {
418 return current($res);
419 }
420
421 // then try using hashed_given_url (to use the database index)
422 $res = $this->createQueryBuilder('e')
423 ->where('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl)
424 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
425 ->getQuery()
426 ->getResult();
427
428 if (\count($res)) {
429 return current($res);
430 }
431
432 return false;
433 }
434
435 /**
436 * Count all entries for a user.
437 *
438 * @param int $userId
439 *
440 * @return int
441 */
442 public function countAllEntriesByUser($userId)
443 {
444 $qb = $this->createQueryBuilder('e')
445 ->select('count(e)')
446 ->where('e.user = :userId')->setParameter('userId', $userId)
447 ;
448
449 return (int) $qb->getQuery()->getSingleScalarResult();
450 }
451
452 /**
453 * Remove all entries for a user id.
454 * Used when a user want to reset all informations.
455 *
456 * @param int $userId
457 */
458 public function removeAllByUserId($userId)
459 {
460 $this->getEntityManager()
461 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
462 ->setParameter('userId', $userId)
463 ->execute();
464 }
465
466 public function removeArchivedByUserId($userId)
467 {
468 $this->getEntityManager()
469 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
470 ->setParameter('userId', $userId)
471 ->execute();
472 }
473
474 /**
475 * Get id and url from all entries
476 * Used for the clean-duplicates command.
477 */
478 public function findAllEntriesIdAndUrlByUserId($userId)
479 {
480 $qb = $this->createQueryBuilder('e')
481 ->select('e.id, e.url')
482 ->where('e.user = :userid')->setParameter(':userid', $userId);
483
484 return $qb->getQuery()->getArrayResult();
485 }
486
487 /**
488 * @param int $userId
489 *
490 * @return array
491 */
492 public function findAllEntriesIdByUserId($userId = null)
493 {
494 $qb = $this->createQueryBuilder('e')
495 ->select('e.id');
496
497 if (null !== $userId) {
498 $qb->where('e.user = :userid')->setParameter(':userid', $userId);
499 }
500
501 return $qb->getQuery()->getArrayResult();
502 }
503
504 /**
505 * Find all entries by url and owner.
506 *
507 * @param string $url
508 * @param int $userId
509 *
510 * @return array
511 */
512 public function findAllByUrlAndUserId($url, $userId)
513 {
514 return $this->createQueryBuilder('e')
515 ->where('e.url = :url')->setParameter('url', urldecode($url))
516 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
517 ->getQuery()
518 ->getResult();
519 }
520
521 /**
522 * Returns a random entry, filtering by status.
523 *
524 * @param int $userId
525 * @param string $type Can be unread, archive, starred, etc
526 *
527 * @throws NoResultException
528 *
529 * @return Entry
530 */
531 public function getRandomEntry($userId, $type = '')
532 {
533 $qb = $this->getQueryBuilderByUser($userId)
534 ->select('e.id');
535
536 switch ($type) {
537 case 'unread':
538 $qb->andWhere('e.isArchived = false');
539 break;
540 case 'archive':
541 $qb->andWhere('e.isArchived = true');
542 break;
543 case 'starred':
544 $qb->andWhere('e.isStarred = true');
545 break;
546 case 'untagged':
547 $qb->leftJoin('e.tags', 't');
548 $qb->andWhere('t.id is null');
549 break;
550 }
551
552 $ids = $qb->getQuery()->getArrayResult();
553
554 if (empty($ids)) {
555 throw new NoResultException();
556 }
557
558 // random select one in the list
559 $randomId = $ids[mt_rand(0, \count($ids) - 1)]['id'];
560
561 return $this->find($randomId);
562 }
563
564 /**
565 * Return a query builder to be used by other getBuilderFor* method.
566 *
567 * @param int $userId
568 *
569 * @return QueryBuilder
570 */
571 private function getQueryBuilderByUser($userId)
572 {
573 return $this->createQueryBuilder('e')
574 ->andWhere('e.user = :userId')->setParameter('userId', $userId);
575 }
576
577 /**
578 * Return a sorted query builder to be used by other getBuilderFor* method.
579 *
580 * @param int $userId
581 * @param string $sortBy
582 * @param string $direction
583 *
584 * @return QueryBuilder
585 */
586 private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
587 {
588 return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
589 }
590
591 /**
592 * Return the given QueryBuilder with an orderBy() call.
593 *
594 * @param string $sortBy
595 * @param string $direction
596 *
597 * @return QueryBuilder
598 */
599 private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
600 {
601 return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);
602 }
603 }