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
;
13 class EntryRepository
extends EntityRepository
16 * Retrieves all entries for a user.
20 * @return QueryBuilder
22 public function getBuilderForAllByUser($userId)
25 ->getSortedQueryBuilderByUser($userId)
30 * Retrieves unread entries for a user.
34 * @return QueryBuilder
36 public function getBuilderForUnreadByUser($userId)
39 ->getSortedQueryBuilderByUser($userId)
40 ->andWhere('e.isArchived = false')
45 * Retrieves read entries for a user.
49 * @return QueryBuilder
51 public function getBuilderForArchiveByUser($userId)
54 ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
55 ->andWhere('e.isArchived = true')
60 * Retrieves starred entries for a user.
64 * @return QueryBuilder
66 public function getBuilderForStarredByUser($userId)
69 ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
70 ->andWhere('e.isStarred = true')
75 * Retrieves entries filtered with a search term for a user.
79 * @param string $currentRoute
81 * @return QueryBuilder
83 public function getBuilderForSearchByUser($userId, $term, $currentRoute)
86 ->getSortedQueryBuilderByUser($userId);
88 if ('starred' === $currentRoute) {
89 $qb->andWhere('e.isStarred = true');
90 } elseif ('unread' === $currentRoute) {
91 $qb->andWhere('e.isArchived = false');
92 } elseif ('archive' === $currentRoute) {
93 $qb->andWhere('e.isArchived = true');
96 // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
98 ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
99 ->leftJoin('e.tags', 't')
106 * Retrieve a sorted list of untagged entries for a user.
110 * @return QueryBuilder
112 public function getBuilderForUntaggedByUser($userId)
114 return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
118 * Retrieve untagged entries for a user.
122 * @return QueryBuilder
124 public function getRawBuilderForUntaggedByUser($userId)
126 return $this->getQueryBuilderByUser($userId)
127 ->leftJoin('e.tags', 't')
128 ->andWhere('t.id is null');
135 * @param bool $isArchived
136 * @param bool $isStarred
137 * @param bool $isPublic
138 * @param string $sort
139 * @param string $order
141 * @param string $tags
145 public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '')
147 $qb = $this->createQueryBuilder('e')
148 ->leftJoin('e.tags', 't')
149 ->where('e.user = :userId')->setParameter('userId', $userId);
151 if (null !== $isArchived) {
152 $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
155 if (null !== $isStarred) {
156 $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
159 if (null !== $isPublic) {
160 $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
164 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \
DateTime(date('Y-m-d H:i:s', $since)));
167 if (\
is_string($tags) && '' !== $tags) {
168 foreach (explode(',', $tags) as $i => $tag) {
169 $entryAlias = 'e' . $i;
170 $tagAlias = 't' . $i;
172 // Complexe queries to ensure multiple tags are associated to an entry
173 // https://stackoverflow.com/a/6638146/569101
174 $qb->andWhere($qb->expr()->in(
176 $this->createQueryBuilder($entryAlias)
177 ->select($entryAlias . '.id')
178 ->leftJoin($entryAlias . '.tags', $tagAlias)
179 ->where($tagAlias . '.label = :label' . $i)
183 // bound parameter to the main query builder
184 $qb->setParameter('label' . $i, $tag);
188 if (!\
in_array(strtolower($order), ['asc', 'desc'], true)) {
189 throw new \
Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
192 if ('created' === $sort) {
193 $qb->orderBy('e.id', $order);
194 } elseif ('updated' === $sort) {
195 $qb->orderBy('e.updatedAt', $order);
196 } elseif ('archived' === $sort) {
197 $qb->orderBy('e.archivedAt', $order);
200 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
202 return new Pagerfanta($pagerAdapter);
206 * Fetch an entry with a tag. Only used for tests.
212 public function findOneWithTags($userId)
214 $qb = $this->createQueryBuilder('e')
215 ->innerJoin('e.tags', 't')
216 ->innerJoin('e.user', 'u')
217 ->addSelect('t', 'u')
218 ->where('e.user = :userId')->setParameter('userId', $userId)
221 return $qb->getQuery()->getResult();
225 * Find distinct language for a given user.
226 * Used to build the filter language list.
228 * @param int $userId User id
232 public function findDistinctLanguageByUser($userId)
234 $results = $this->createQueryBuilder('e')
235 ->select('e.language')
236 ->where('e.user = :userId')->setParameter('userId', $userId)
237 ->andWhere('e.language IS NOT NULL')
238 ->groupBy('e.language')
239 ->orderBy('e.language', ' ASC')
244 foreach ($results as $result) {
245 $languages[$result['language']] = $result['language'];
252 * Used only in test case to get the right entry associated to the right user.
254 * @param string $username
258 public function findOneByUsernameAndNotArchived($username)
260 return $this->createQueryBuilder('e')
261 ->leftJoin('e.user', 'u')
262 ->where('u.username = :username')->setParameter('username', $username)
263 ->andWhere('e.isArchived = false')
270 * Remove a tag from all user entries.
272 * 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.
273 * 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:
275 * 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
280 public function removeTag($userId, Tag
$tag)
282 $entries = $this->getSortedQueryBuilderByUser($userId)
283 ->innerJoin('e.tags', 't')
284 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
288 foreach ($entries as $entry) {
289 $entry->removeTag($tag);
292 $this->getEntityManager()->flush();
296 * Remove tags from all user entries.
299 * @param Array<Tag> $tags
301 public function removeTags($userId, $tags)
303 foreach ($tags as $tag) {
304 $this->removeTag($userId, $tag);
309 * Find all entries that are attached to a give tag id.
316 public function findAllByTagId($userId, $tagId)
318 return $this->getSortedQueryBuilderByUser($userId)
319 ->innerJoin('e.tags', 't')
320 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
326 * Find an entry by its url and its owner.
327 * If it exists, return the entry otherwise return false.
334 public function findByUrlAndUserId($url, $userId)
336 $res = $this->createQueryBuilder('e')
337 ->where('e.url = :url')->setParameter('url', urldecode($url))
338 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
343 return current($res);
350 * Count all entries for a user.
356 public function countAllEntriesByUser($userId)
358 $qb = $this->createQueryBuilder('e')
360 ->where('e.user = :userId')->setParameter('userId', $userId)
363 return (int) $qb->getQuery()->getSingleScalarResult();
367 * Remove all entries for a user id.
368 * Used when a user want to reset all informations.
372 public function removeAllByUserId($userId)
374 $this->getEntityManager()
375 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
376 ->setParameter('userId', $userId)
380 public function removeArchivedByUserId($userId)
382 $this->getEntityManager()
383 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
384 ->setParameter('userId', $userId)
389 * Get id and url from all entries
390 * Used for the clean-duplicates command.
392 public function findAllEntriesIdAndUrlByUserId($userId)
394 $qb = $this->createQueryBuilder('e')
395 ->select('e.id, e.url')
396 ->where('e.user = :userid')->setParameter(':userid', $userId);
398 return $qb->getQuery()->getArrayResult();
406 public function findAllEntriesIdByUserId($userId = null)
408 $qb = $this->createQueryBuilder('e')
411 if (null !== $userId) {
412 $qb->where('e.user = :userid')->setParameter(':userid', $userId);
415 return $qb->getQuery()->getArrayResult();
419 * Find all entries by url and owner.
426 public function findAllByUrlAndUserId($url, $userId)
428 return $this->createQueryBuilder('e')
429 ->where('e.url = :url')->setParameter('url', urldecode($url))
430 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
436 * Returns a random entry, filtering by status.
439 * @param string $type Can be unread, archive, starred, etc
441 * @throws NoResultException
445 public function getRandomEntry($userId, $type = '')
447 $qb = $this->getQueryBuilderByUser($userId)
452 $qb->andWhere('e.isArchived = false');
455 $qb->andWhere('e.isArchived = true');
458 $qb->andWhere('e.isStarred = true');
461 $qb->leftJoin('e.tags', 't');
462 $qb->andWhere('t.id is null');
466 $ids = $qb->getQuery()->getArrayResult();
469 throw new NoResultException();
472 // random select one in the list
473 $randomId = $ids[mt_rand(0, \
count($ids) - 1)]['id'];
475 return $this->find($randomId);
479 * Return a query builder to be used by other getBuilderFor* method.
483 * @return QueryBuilder
485 private function getQueryBuilderByUser($userId)
487 return $this->createQueryBuilder('e')
488 ->andWhere('e.user = :userId')->setParameter('userId', $userId);
492 * Return a sorted query builder to be used by other getBuilderFor* method.
495 * @param string $sortBy
496 * @param string $direction
498 * @return QueryBuilder
500 private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
502 return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
506 * Return the given QueryBuilder with an orderBy() call.
508 * @param QueryBuilder $qb
509 * @param string $sortBy
510 * @param string $direction
512 * @return QueryBuilder
514 private function sortQueryBuilder(QueryBuilder
$qb, $sortBy = 'createdAt', $direction = 'desc')
516 return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);