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.
20 * @param string $sortBy Field to sort
21 * @param string $direction Direction of the order
23 * @return QueryBuilder
25 public function getBuilderForAllByUser($userId, $sortBy = 'id', $direction = 'DESC')
27 $sortBy = $sortBy ?: 'id';
30 ->getSortedQueryBuilderByUser($userId, $sortBy, $direction)
35 * Retrieves unread entries for a user.
38 * @param string $sortBy Field to sort
39 * @param string $direction Direction of the order
41 * @return QueryBuilder
43 public function getBuilderForUnreadByUser($userId, $sortBy = 'id', $direction = 'DESC')
45 $sortBy = $sortBy ?: 'id';
48 ->getSortedQueryBuilderByUser($userId, $sortBy, $direction)
49 ->andWhere('e.isArchived = false')
54 * Retrieves read entries for a user.
57 * @param string $sortBy Field to sort
58 * @param string $direction Direction of the order
60 * @return QueryBuilder
62 public function getBuilderForArchiveByUser($userId, $sortBy = 'archivedAt', $direction = 'DESC')
64 $sortBy = $sortBy ?: 'archivedAt';
67 ->getSortedQueryBuilderByUser($userId, $sortBy, $direction)
68 ->andWhere('e.isArchived = true')
73 * Retrieves starred entries for a user.
76 * @param string $sortBy Field to sort
77 * @param string $direction Direction of the order
79 * @return QueryBuilder
81 public function getBuilderForStarredByUser($userId, $sortBy = 'starredAt', $direction = 'DESC')
83 $sortBy = $sortBy ?: 'starredAt';
86 ->getSortedQueryBuilderByUser($userId, $sortBy, $direction)
87 ->andWhere('e.isStarred = true')
92 * Retrieves entries filtered with a search term for a user.
96 * @param string $currentRoute
98 * @return QueryBuilder
100 public function getBuilderForSearchByUser($userId, $term, $currentRoute)
103 ->getSortedQueryBuilderByUser($userId);
105 if ('starred' === $currentRoute) {
106 $qb->andWhere('e.isStarred = true');
107 } elseif ('unread' === $currentRoute) {
108 $qb->andWhere('e.isArchived = false');
109 } elseif ('archive' === $currentRoute) {
110 $qb->andWhere('e.isArchived = true');
113 // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
115 ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
116 ->leftJoin('e.tags', 't')
123 * Retrieve a sorted list of untagged entries for a user.
127 * @return QueryBuilder
129 public function getBuilderForUntaggedByUser($userId)
131 return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
135 * Retrieve untagged entries for a user.
139 * @return QueryBuilder
141 public function getRawBuilderForUntaggedByUser($userId)
143 return $this->getQueryBuilderByUser($userId)
144 ->leftJoin('e.tags', 't')
145 ->andWhere('t.id is null');
149 * Retrieve the number of untagged entries for a user.
155 public function countUntaggedEntriesByUser($userId)
157 return (int) $this->getRawBuilderForUntaggedByUser($userId)
158 ->select('count(e.id)')
160 ->getSingleScalarResult();
167 * @param bool $isArchived
168 * @param bool $isStarred
169 * @param bool $isPublic
170 * @param string $sort
171 * @param string $order
173 * @param string $tags
174 * @param string $detail 'metadata' or 'full'. Include content field if 'full'
176 * @todo Breaking change: replace default detail=full by detail=metadata in a future version
180 public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '', $detail = 'full')
182 if (!\
in_array(strtolower($detail), ['full', 'metadata'], true)) {
183 throw new \
Exception('Detail "' . $detail . '" parameter is wrong, allowed: full or metadata');
186 $qb = $this->createQueryBuilder('e')
187 ->leftJoin('e.tags', 't')
188 ->where('e.user = :userId')->setParameter('userId', $userId);
190 if ('metadata' === $detail) {
191 $fieldNames = $this->getClassMetadata()->getFieldNames();
192 $fields = array_filter($fieldNames, function ($k) {
193 return 'content' !== $k;
195 $qb->select(sprintf('partial e.{%s}', implode(',', $fields)));
198 if (null !== $isArchived) {
199 $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
202 if (null !== $isStarred) {
203 $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
206 if (null !== $isPublic) {
207 $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
211 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \
DateTime(date('Y-m-d H:i:s', $since)));
214 if (\
is_string($tags) && '' !== $tags) {
215 foreach (explode(',', $tags) as $i => $tag) {
216 $entryAlias = 'e' . $i;
217 $tagAlias = 't' . $i;
219 // Complexe queries to ensure multiple tags are associated to an entry
220 // https://stackoverflow.com/a/6638146/569101
221 $qb->andWhere($qb->expr()->in(
223 $this->createQueryBuilder($entryAlias)
224 ->select($entryAlias . '.id')
225 ->leftJoin($entryAlias . '.tags', $tagAlias)
226 ->where($tagAlias . '.label = :label' . $i)
230 // bound parameter to the main query builder
231 $qb->setParameter('label' . $i, $tag);
235 if (!\
in_array(strtolower($order), ['asc', 'desc'], true)) {
236 throw new \
Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
239 if ('created' === $sort) {
240 $qb->orderBy('e.id', $order);
241 } elseif ('updated' === $sort) {
242 $qb->orderBy('e.updatedAt', $order);
243 } elseif ('archived' === $sort) {
244 $qb->orderBy('e.archivedAt', $order);
247 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
249 return new Pagerfanta($pagerAdapter);
253 * Fetch an entry with a tag. Only used for tests.
259 public function findOneWithTags($userId)
261 $qb = $this->createQueryBuilder('e')
262 ->innerJoin('e.tags', 't')
263 ->innerJoin('e.user', 'u')
264 ->addSelect('t', 'u')
265 ->where('e.user = :userId')->setParameter('userId', $userId)
268 return $qb->getQuery()->getResult();
272 * Find distinct language for a given user.
273 * Used to build the filter language list.
275 * @param int $userId User id
279 public function findDistinctLanguageByUser($userId)
281 $results = $this->createQueryBuilder('e')
282 ->select('e.language')
283 ->where('e.user = :userId')->setParameter('userId', $userId)
284 ->andWhere('e.language IS NOT NULL')
285 ->groupBy('e.language')
286 ->orderBy('e.language', ' ASC')
291 foreach ($results as $result) {
292 $languages[$result['language']] = $result['language'];
299 * Used only in test case to get the right entry associated to the right user.
301 * @param string $username
305 public function findOneByUsernameAndNotArchived($username)
307 return $this->createQueryBuilder('e')
308 ->leftJoin('e.user', 'u')
309 ->where('u.username = :username')->setParameter('username', $username)
310 ->andWhere('e.isArchived = false')
317 * Remove a tag from all user entries.
319 * 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.
320 * 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:
322 * 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
326 public function removeTag($userId, Tag
$tag)
328 $entries = $this->getSortedQueryBuilderByUser($userId)
329 ->innerJoin('e.tags', 't')
330 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
334 foreach ($entries as $entry) {
335 $entry->removeTag($tag);
338 $this->getEntityManager()->flush();
342 * Remove tags from all user entries.
345 * @param Array<Tag> $tags
347 public function removeTags($userId, $tags)
349 foreach ($tags as $tag) {
350 $this->removeTag($userId, $tag);
355 * Find all entries that are attached to a give tag id.
362 public function findAllByTagId($userId, $tagId)
364 return $this->getSortedQueryBuilderByUser($userId)
365 ->innerJoin('e.tags', 't')
366 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
372 * Find an entry by its url and its owner.
373 * If it exists, return the entry otherwise return false.
378 * @return Entry|false
380 public function findByUrlAndUserId($url, $userId)
382 return $this->findByHashedUrlAndUserId(
383 UrlHasher
::hashUrl($url),
389 * Find an entry by its hashed url and its owner.
390 * If it exists, return the entry otherwise return false.
392 * @param string $hashedUrl Url hashed using sha1
395 * @return Entry|false
397 public function findByHashedUrlAndUserId($hashedUrl, $userId)
399 // try first using hashed_url (to use the database index)
400 $res = $this->createQueryBuilder('e')
401 ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl)
402 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
407 return current($res);
410 // then try using hashed_given_url (to use the database index)
411 $res = $this->createQueryBuilder('e')
412 ->where('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl)
413 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
418 return current($res);
425 * Count all entries for a user.
431 public function countAllEntriesByUser($userId)
433 $qb = $this->createQueryBuilder('e')
435 ->where('e.user = :userId')->setParameter('userId', $userId)
438 return (int) $qb->getQuery()->getSingleScalarResult();
442 * Remove all entries for a user id.
443 * Used when a user want to reset all informations.
447 public function removeAllByUserId($userId)
449 $this->getEntityManager()
450 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
451 ->setParameter('userId', $userId)
455 public function removeArchivedByUserId($userId)
457 $this->getEntityManager()
458 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
459 ->setParameter('userId', $userId)
464 * Get id and url from all entries
465 * Used for the clean-duplicates command.
467 public function findAllEntriesIdAndUrlByUserId($userId)
469 $qb = $this->createQueryBuilder('e')
470 ->select('e.id, e.url')
471 ->where('e.user = :userid')->setParameter(':userid', $userId);
473 return $qb->getQuery()->getArrayResult();
481 public function findAllEntriesIdByUserId($userId = null)
483 $qb = $this->createQueryBuilder('e')
486 if (null !== $userId) {
487 $qb->where('e.user = :userid')->setParameter(':userid', $userId);
490 return $qb->getQuery()->getArrayResult();
494 * Find all entries by url and owner.
501 public function findAllByUrlAndUserId($url, $userId)
503 return $this->createQueryBuilder('e')
504 ->where('e.url = :url')->setParameter('url', urldecode($url))
505 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
511 * Returns a random entry, filtering by status.
514 * @param string $type Can be unread, archive, starred, etc
516 * @throws NoResultException
520 public function getRandomEntry($userId, $type = '')
522 $qb = $this->getQueryBuilderByUser($userId)
527 $qb->andWhere('e.isArchived = false');
530 $qb->andWhere('e.isArchived = true');
533 $qb->andWhere('e.isStarred = true');
536 $qb->leftJoin('e.tags', 't');
537 $qb->andWhere('t.id is null');
541 $ids = $qb->getQuery()->getArrayResult();
544 throw new NoResultException();
547 // random select one in the list
548 $randomId = $ids[mt_rand(0, \
count($ids) - 1)]['id'];
550 return $this->find($randomId);
554 * Return a query builder to be used by other getBuilderFor* method.
558 * @return QueryBuilder
560 private function getQueryBuilderByUser($userId)
562 return $this->createQueryBuilder('e')
563 ->andWhere('e.user = :userId')->setParameter('userId', $userId);
567 * Return a sorted query builder to be used by other getBuilderFor* method.
570 * @param string $sortBy
571 * @param string $direction
573 * @return QueryBuilder
575 private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
577 return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
581 * Return the given QueryBuilder with an orderBy() call.
583 * @param string $sortBy
584 * @param string $direction
586 * @return QueryBuilder
588 private function sortQueryBuilder(QueryBuilder
$qb, $sortBy = 'createdAt', $direction = 'desc')
590 // in case one of these isn't defined, don't apply the orderBy
591 if (!$sortBy || !$direction) {
595 return $qb->orderBy(sprintf('e.%s', $sortBy), strtoupper($direction));