3 namespace Wallabag\CoreBundle\Repository
;
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
;
14 class EntryRepository
extends EntityRepository
17 * Retrieves all entries for a user.
21 * @return QueryBuilder
23 public function getBuilderForAllByUser($userId)
26 ->getSortedQueryBuilderByUser($userId)
31 * Retrieves unread entries for a user.
35 * @return QueryBuilder
37 public function getBuilderForUnreadByUser($userId)
40 ->getSortedQueryBuilderByUser($userId)
41 ->andWhere('e.isArchived = false')
46 * Retrieves read entries for a user.
50 * @return QueryBuilder
52 public function getBuilderForArchiveByUser($userId)
55 ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
56 ->andWhere('e.isArchived = true')
61 * Retrieves starred entries for a user.
65 * @return QueryBuilder
67 public function getBuilderForStarredByUser($userId)
70 ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
71 ->andWhere('e.isStarred = true')
76 * Retrieves entries filtered with a search term for a user.
80 * @param string $currentRoute
82 * @return QueryBuilder
84 public function getBuilderForSearchByUser($userId, $term, $currentRoute)
87 ->getSortedQueryBuilderByUser($userId);
89 if ('starred' === $currentRoute) {
90 $qb->andWhere('e.isStarred = true');
91 } elseif ('unread' === $currentRoute) {
92 $qb->andWhere('e.isArchived = false');
93 } elseif ('archive' === $currentRoute) {
94 $qb->andWhere('e.isArchived = true');
97 // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
99 ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
100 ->leftJoin('e.tags', 't')
107 * Retrieve a sorted list of untagged entries for a user.
111 * @return QueryBuilder
113 public function getBuilderForUntaggedByUser($userId)
115 return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
119 * Retrieve entries with annotations for a user.
123 * @return QueryBuilder
125 public function getBuilderForAnnotationsByUser($userId)
128 ->getSortedQueryBuilderByUser($userId)
129 ->innerJoin('e.annotations', 'a')
134 * Retrieve untagged entries for a user.
138 * @return QueryBuilder
140 public function getRawBuilderForUntaggedByUser($userId)
142 return $this->getQueryBuilderByUser($userId)
143 ->leftJoin('e.tags', 't')
144 ->andWhere('t.id is null');
148 * Retrieve the number of untagged entries for a user.
154 public function countUntaggedEntriesByUser($userId)
156 return (int) $this->getRawBuilderForUntaggedByUser($userId)
157 ->select('count(e.id)')
159 ->getSingleScalarResult();
166 * @param bool $isArchived
167 * @param bool $isStarred
168 * @param bool $isPublic
169 * @param string $sort
170 * @param string $order
172 * @param string $tags
173 * @param string $detail 'metadata' or 'full'. Include content field if 'full'
175 * @todo Breaking change: replace default detail=full by detail=metadata in a future version
179 public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '', $detail = 'full')
181 if (!\
in_array(strtolower($detail), ['full', 'metadata'], true)) {
182 throw new \
Exception('Detail "' . $detail . '" parameter is wrong, allowed: full or metadata');
185 $qb = $this->createQueryBuilder('e')
186 ->leftJoin('e.tags', 't')
187 ->where('e.user = :userId')->setParameter('userId', $userId);
189 if ('metadata' === $detail) {
190 $fieldNames = $this->getClassMetadata()->getFieldNames();
191 $fields = array_filter($fieldNames, function ($k) {
192 return 'content' !== $k;
194 $qb->select(sprintf('partial e.{%s}', implode(',', $fields)));
197 if (null !== $isArchived) {
198 $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
201 if (null !== $isStarred) {
202 $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
205 if (null !== $isPublic) {
206 $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
210 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \
DateTime(date('Y-m-d H:i:s', $since)));
213 if (\
is_string($tags) && '' !== $tags) {
214 foreach (explode(',', $tags) as $i => $tag) {
215 $entryAlias = 'e' . $i;
216 $tagAlias = 't' . $i;
218 // Complexe queries to ensure multiple tags are associated to an entry
219 // https://stackoverflow.com/a/6638146/569101
220 $qb->andWhere($qb->expr()->in(
222 $this->createQueryBuilder($entryAlias)
223 ->select($entryAlias . '.id')
224 ->leftJoin($entryAlias . '.tags', $tagAlias)
225 ->where($tagAlias . '.label = :label' . $i)
229 // bound parameter to the main query builder
230 $qb->setParameter('label' . $i, $tag);
234 if (!\
in_array(strtolower($order), ['asc', 'desc'], true)) {
235 throw new \
Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
238 if ('created' === $sort) {
239 $qb->orderBy('e.id', $order);
240 } elseif ('updated' === $sort) {
241 $qb->orderBy('e.updatedAt', $order);
242 } elseif ('archived' === $sort) {
243 $qb->orderBy('e.archivedAt', $order);
246 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
248 return new Pagerfanta($pagerAdapter);
252 * Fetch an entry with a tag. Only used for tests.
258 public function findOneWithTags($userId)
260 $qb = $this->createQueryBuilder('e')
261 ->innerJoin('e.tags', 't')
262 ->innerJoin('e.user', 'u')
263 ->addSelect('t', 'u')
264 ->where('e.user = :userId')->setParameter('userId', $userId)
267 return $qb->getQuery()->getResult();
271 * Find distinct language for a given user.
272 * Used to build the filter language list.
274 * @param int $userId User id
278 public function findDistinctLanguageByUser($userId)
280 $results = $this->createQueryBuilder('e')
281 ->select('e.language')
282 ->where('e.user = :userId')->setParameter('userId', $userId)
283 ->andWhere('e.language IS NOT NULL')
284 ->groupBy('e.language')
285 ->orderBy('e.language', ' ASC')
290 foreach ($results as $result) {
291 $languages[$result['language']] = $result['language'];
298 * Used only in test case to get the right entry associated to the right user.
300 * @param string $username
304 public function findOneByUsernameAndNotArchived($username)
306 return $this->createQueryBuilder('e')
307 ->leftJoin('e.user', 'u')
308 ->where('u.username = :username')->setParameter('username', $username)
309 ->andWhere('e.isArchived = false')
316 * Remove a tag from all user entries.
318 * 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.
319 * 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:
321 * 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
325 public function removeTag($userId, Tag
$tag)
327 $entries = $this->getSortedQueryBuilderByUser($userId)
328 ->innerJoin('e.tags', 't')
329 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
333 foreach ($entries as $entry) {
334 $entry->removeTag($tag);
337 $this->getEntityManager()->flush();
341 * Remove tags from all user entries.
344 * @param Array<Tag> $tags
346 public function removeTags($userId, $tags)
348 foreach ($tags as $tag) {
349 $this->removeTag($userId, $tag);
354 * Find all entries that are attached to a give tag id.
361 public function findAllByTagId($userId, $tagId)
363 return $this->getSortedQueryBuilderByUser($userId)
364 ->innerJoin('e.tags', 't')
365 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
371 * Find an entry by its url and its owner.
372 * If it exists, return the entry otherwise return false.
377 * @return Entry|false
379 public function findByUrlAndUserId($url, $userId)
381 return $this->findByHashedUrlAndUserId(
382 UrlHasher
::hashUrl($url),
388 * Find an entry by its hashed url and its owner.
389 * If it exists, return the entry otherwise return false.
391 * @param string $hashedUrl Url hashed using sha1
394 * @return Entry|false
396 public function findByHashedUrlAndUserId($hashedUrl, $userId)
398 // try first using hashed_url (to use the database index)
399 $res = $this->createQueryBuilder('e')
400 ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl)
401 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
406 return current($res);
409 // then try using hashed_given_url (to use the database index)
410 $res = $this->createQueryBuilder('e')
411 ->where('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl)
412 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
417 return current($res);
424 * Count all entries for a user.
430 public function countAllEntriesByUser($userId)
432 $qb = $this->createQueryBuilder('e')
434 ->where('e.user = :userId')->setParameter('userId', $userId)
437 return (int) $qb->getQuery()->getSingleScalarResult();
441 * Remove all entries for a user id.
442 * Used when a user want to reset all informations.
446 public function removeAllByUserId($userId)
448 $this->getEntityManager()
449 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
450 ->setParameter('userId', $userId)
454 public function removeArchivedByUserId($userId)
456 $this->getEntityManager()
457 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
458 ->setParameter('userId', $userId)
463 * Get id and url from all entries
464 * Used for the clean-duplicates command.
466 public function findAllEntriesIdAndUrlByUserId($userId)
468 $qb = $this->createQueryBuilder('e')
469 ->select('e.id, e.url')
470 ->where('e.user = :userid')->setParameter(':userid', $userId);
472 return $qb->getQuery()->getArrayResult();
480 public function findAllEntriesIdByUserId($userId = null)
482 $qb = $this->createQueryBuilder('e')
485 if (null !== $userId) {
486 $qb->where('e.user = :userid')->setParameter(':userid', $userId);
489 return $qb->getQuery()->getArrayResult();
493 * Find all entries by url and owner.
500 public function findAllByUrlAndUserId($url, $userId)
502 return $this->createQueryBuilder('e')
503 ->where('e.url = :url')->setParameter('url', urldecode($url))
504 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
510 * Returns a random entry, filtering by status.
513 * @param string $type Can be unread, archive, starred, etc
515 * @throws NoResultException
519 public function getRandomEntry($userId, $type = '')
521 $qb = $this->getQueryBuilderByUser($userId)
526 $qb->andWhere('e.isArchived = false');
529 $qb->andWhere('e.isArchived = true');
532 $qb->andWhere('e.isStarred = true');
535 $qb->leftJoin('e.tags', 't');
536 $qb->andWhere('t.id is null');
538 case 'with_annotations':
539 $qb->leftJoin('e.annotations', 'a');
540 $qb->andWhere('a.id is not null');
544 $ids = $qb->getQuery()->getArrayResult();
547 throw new NoResultException();
550 // random select one in the list
551 $randomId = $ids[mt_rand(0, \
count($ids) - 1)]['id'];
553 return $this->find($randomId);
557 * Return a query builder to be used by other getBuilderFor* method.
561 * @return QueryBuilder
563 private function getQueryBuilderByUser($userId)
565 return $this->createQueryBuilder('e')
566 ->andWhere('e.user = :userId')->setParameter('userId', $userId);
570 * Return a sorted query builder to be used by other getBuilderFor* method.
573 * @param string $sortBy
574 * @param string $direction
576 * @return QueryBuilder
578 private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
580 return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
584 * Return the given QueryBuilder with an orderBy() call.
586 * @param string $sortBy
587 * @param string $direction
589 * @return QueryBuilder
591 private function sortQueryBuilder(QueryBuilder
$qb, $sortBy = 'createdAt', $direction = 'desc')
593 return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);