3 namespace Wallabag\CoreBundle\Repository
;
5 use Doctrine\ORM\EntityRepository
;
6 use Doctrine\ORM\QueryBuilder
;
7 use Pagerfanta\Adapter\DoctrineORMAdapter
;
8 use Pagerfanta\Pagerfanta
;
9 use Wallabag\CoreBundle\Entity\Entry
;
10 use Wallabag\CoreBundle\Entity\Tag
;
12 class EntryRepository
extends EntityRepository
15 * Retrieves all entries for a user.
19 * @return QueryBuilder
21 public function getBuilderForAllByUser($userId)
24 ->getSortedQueryBuilderByUser($userId)
29 * Retrieves unread entries for a user.
33 * @return QueryBuilder
35 public function getBuilderForUnreadByUser($userId)
38 ->getSortedQueryBuilderByUser($userId)
39 ->andWhere('e.isArchived = false')
44 * Retrieves read entries for a user.
48 * @return QueryBuilder
50 public function getBuilderForArchiveByUser($userId)
53 ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
54 ->andWhere('e.isArchived = true')
59 * Retrieves starred entries for a user.
63 * @return QueryBuilder
65 public function getBuilderForStarredByUser($userId)
68 ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
69 ->andWhere('e.isStarred = true')
74 * Retrieves entries filtered with a search term for a user.
78 * @param string $currentRoute
80 * @return QueryBuilder
82 public function getBuilderForSearchByUser($userId, $term, $currentRoute)
85 ->getSortedQueryBuilderByUser($userId);
87 if ('starred' === $currentRoute) {
88 $qb->andWhere('e.isStarred = true');
89 } elseif ('unread' === $currentRoute) {
90 $qb->andWhere('e.isArchived = false');
91 } elseif ('archive' === $currentRoute) {
92 $qb->andWhere('e.isArchived = true');
95 // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
97 ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
98 ->leftJoin('e.tags', 't')
105 * Retrieve a sorted list of untagged entries for a user.
109 * @return QueryBuilder
111 public function getBuilderForUntaggedByUser($userId)
113 return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
117 * Retrieve untagged entries for a user.
121 * @return QueryBuilder
123 public function getRawBuilderForUntaggedByUser($userId)
125 return $this->getQueryBuilderByUser($userId)
126 ->leftJoin('e.tags', 't')
127 ->andWhere('t.id is null');
134 * @param bool $isArchived
135 * @param bool $isStarred
136 * @param bool $isPublic
137 * @param string $sort
138 * @param string $order
140 * @param string $tags
144 public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
146 $qb = $this->createQueryBuilder('e')
147 ->leftJoin('e.tags', 't')
148 ->where('e.user = :userId')->setParameter('userId', $userId);
150 if (null !== $isArchived) {
151 $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
154 if (null !== $isStarred) {
155 $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
158 if (null !== $isPublic) {
159 $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
163 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \
DateTime(date('Y-m-d H:i:s', $since)));
166 if (\
is_string($tags) && '' !== $tags) {
167 foreach (explode(',', $tags) as $i => $tag) {
168 $entryAlias = 'e' . $i;
169 $tagAlias = 't' . $i;
171 // Complexe queries to ensure multiple tags are associated to an entry
172 // https://stackoverflow.com/a/6638146/569101
173 $qb->andWhere($qb->expr()->in(
175 $this->createQueryBuilder($entryAlias)
176 ->select($entryAlias . '.id')
177 ->leftJoin($entryAlias . '.tags', $tagAlias)
178 ->where($tagAlias . '.label = :label' . $i)
182 // bound parameter to the main query builder
183 $qb->setParameter('label' . $i, $tag);
187 if ('created' === $sort) {
188 $qb->orderBy('e.id', $order);
189 } elseif ('updated' === $sort) {
190 $qb->orderBy('e.updatedAt', $order);
191 } elseif ('archived' === $sort) {
192 $qb->orderBy('e.archivedAt', $order);
195 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
197 return new Pagerfanta($pagerAdapter);
201 * Fetch an entry with a tag. Only used for tests.
207 public function findOneWithTags($userId)
209 $qb = $this->createQueryBuilder('e')
210 ->innerJoin('e.tags', 't')
211 ->innerJoin('e.user', 'u')
212 ->addSelect('t', 'u')
213 ->where('e.user = :userId')->setParameter('userId', $userId)
216 return $qb->getQuery()->getResult();
220 * Find distinct language for a given user.
221 * Used to build the filter language list.
223 * @param int $userId User id
227 public function findDistinctLanguageByUser($userId)
229 $results = $this->createQueryBuilder('e')
230 ->select('e.language')
231 ->where('e.user = :userId')->setParameter('userId', $userId)
232 ->andWhere('e.language IS NOT NULL')
233 ->groupBy('e.language')
234 ->orderBy('e.language', ' ASC')
239 foreach ($results as $result) {
240 $languages[$result['language']] = $result['language'];
247 * Used only in test case to get the right entry associated to the right user.
249 * @param string $username
253 public function findOneByUsernameAndNotArchived($username)
255 return $this->createQueryBuilder('e')
256 ->leftJoin('e.user', 'u')
257 ->where('u.username = :username')->setParameter('username', $username)
258 ->andWhere('e.isArchived = false')
265 * Remove a tag from all user entries.
267 * 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.
268 * 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:
270 * 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
275 public function removeTag($userId, Tag
$tag)
277 $entries = $this->getSortedQueryBuilderByUser($userId)
278 ->innerJoin('e.tags', 't')
279 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
283 foreach ($entries as $entry) {
284 $entry->removeTag($tag);
287 $this->getEntityManager()->flush();
291 * Remove tags from all user entries.
294 * @param Array<Tag> $tags
296 public function removeTags($userId, $tags)
298 foreach ($tags as $tag) {
299 $this->removeTag($userId, $tag);
304 * Find all entries that are attached to a give tag id.
311 public function findAllByTagId($userId, $tagId)
313 return $this->getSortedQueryBuilderByUser($userId)
314 ->innerJoin('e.tags', 't')
315 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
321 * Find an entry by its url and its owner.
322 * If it exists, return the entry otherwise return false.
329 public function findByUrlAndUserId($url, $userId)
331 $res = $this->createQueryBuilder('e')
332 ->where('e.url = :url')->setParameter('url', urldecode($url))
333 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
338 return current($res);
345 * Count all entries for a user.
351 public function countAllEntriesByUser($userId)
353 $qb = $this->createQueryBuilder('e')
355 ->where('e.user = :userId')->setParameter('userId', $userId)
358 return (int) $qb->getQuery()->getSingleScalarResult();
362 * Remove all entries for a user id.
363 * Used when a user want to reset all informations.
367 public function removeAllByUserId($userId)
369 $this->getEntityManager()
370 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
371 ->setParameter('userId', $userId)
375 public function removeArchivedByUserId($userId)
377 $this->getEntityManager()
378 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
379 ->setParameter('userId', $userId)
384 * Get id and url from all entries
385 * Used for the clean-duplicates command.
387 public function findAllEntriesIdAndUrlByUserId($userId)
389 $qb = $this->createQueryBuilder('e')
390 ->select('e.id, e.url')
391 ->where('e.user = :userid')->setParameter(':userid', $userId);
393 return $qb->getQuery()->getArrayResult();
401 public function findAllEntriesIdByUserId($userId = null)
403 $qb = $this->createQueryBuilder('e')
406 if (null !== $userId) {
407 $qb->where('e.user = :userid')->setParameter(':userid', $userId);
410 return $qb->getQuery()->getArrayResult();
414 * Find all entries by url and owner.
421 public function findAllByUrlAndUserId($url, $userId)
423 return $this->createQueryBuilder('e')
424 ->where('e.url = :url')->setParameter('url', urldecode($url))
425 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
431 * Returns a random entry, filtering by status.
434 * @param string $status can be unread, archive or starred
436 * @throws \Doctrine\ORM\NoResultException
437 * @throws \Doctrine\ORM\NonUniqueResultException
441 public function getRandomEntry($userId, $status = '')
443 $max = $this->getEntityManager()
444 ->createQuery('SELECT MAX(e.id) FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
445 ->setParameter('userId', $userId)
446 ->getSingleScalarResult();
448 $qb = $this->createQueryBuilder('e')
449 ->where('e.user = :user_id')->setParameter('user_id', $userId);
451 if ('unread' === $status) {
452 $qb->andWhere('e.isArchived = false');
455 if ('archive' === $status) {
456 $qb->andWhere('e.isArchived = true');
459 if ('starred' === $status) {
460 $qb->andWhere('e.isStarred = true');
463 if ('untagged' === $status) {
464 $qb->leftJoin('e.tags', 't');
465 $qb->andWhere('t.id is null');
468 return $qb->andWhere('e.id >= :rand')
469 ->setParameter('rand', rand(0, $max))
476 * Return a query builder to be used by other getBuilderFor* method.
480 * @return QueryBuilder
482 private function getQueryBuilderByUser($userId)
484 return $this->createQueryBuilder('e')
485 ->andWhere('e.user = :userId')->setParameter('userId', $userId);
489 * Return a sorted query builder to be used by other getBuilderFor* method.
492 * @param string $sortBy
493 * @param string $direction
495 * @return QueryBuilder
497 private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
499 return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
503 * Return the given QueryBuilder with an orderBy() call.
505 * @param QueryBuilder $qb
506 * @param string $sortBy
507 * @param string $direction
509 * @return QueryBuilder
511 private function sortQueryBuilder(QueryBuilder
$qb, $sortBy = 'createdAt', $direction = 'desc')
513 return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);