3 namespace Wallabag\CoreBundle\Repository
;
5 use Doctrine\ORM\EntityRepository
;
6 use Doctrine\ORM\Query
;
7 use Pagerfanta\Adapter\DoctrineORMAdapter
;
8 use Pagerfanta\Pagerfanta
;
9 use Wallabag\CoreBundle\Entity\Tag
;
11 class EntryRepository
extends EntityRepository
14 * Retrieves all entries for a user.
18 * @return QueryBuilder
20 public function getBuilderForAllByUser($userId)
23 ->getBuilderByUser($userId)
28 * Retrieves unread entries for a user.
32 * @return QueryBuilder
34 public function getBuilderForUnreadByUser($userId)
37 ->getBuilderByUser($userId)
38 ->andWhere('e.isArchived = false')
43 * Retrieves read entries for a user.
47 * @return QueryBuilder
49 public function getBuilderForArchiveByUser($userId)
52 ->getBuilderByUser($userId)
53 ->andWhere('e.isArchived = true')
58 * Retrieves starred entries for a user.
62 * @return QueryBuilder
64 public function getBuilderForStarredByUser($userId)
67 ->getBuilderByUser($userId)
68 ->andWhere('e.isStarred = true')
73 * Retrieves entries filtered with a search term for a user.
77 * @param strint $currentRoute
79 * @return QueryBuilder
81 public function getBuilderForSearchByUser($userId, $term, $currentRoute)
84 ->getBuilderByUser($userId);
86 if ('starred' === $currentRoute) {
87 $qb->andWhere('e.isStarred = true');
88 } elseif ('unread' === $currentRoute) {
89 $qb->andWhere('e.isArchived = false');
90 } elseif ('archive' === $currentRoute) {
91 $qb->andWhere('e.isArchived = true');
94 // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
96 ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
97 ->leftJoin('e.tags', 't')
104 * Retrieves untagged entries for a user.
108 * @return QueryBuilder
110 public function getBuilderForUntaggedByUser($userId)
113 ->getBuilderByUser($userId)
114 ->andWhere('size(e.tags) = 0');
121 * @param bool $isArchived
122 * @param bool $isStarred
123 * @param bool $isPublic
124 * @param string $sort
125 * @param string $order
127 * @param string $tags
131 public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
133 $qb = $this->createQueryBuilder('e')
134 ->leftJoin('e.tags', 't')
135 ->where('e.user =:userId')->setParameter('userId', $userId);
137 if (null !== $isArchived) {
138 $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
141 if (null !== $isStarred) {
142 $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
145 if (null !== $isPublic) {
146 $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
150 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \
DateTime(date('Y-m-d H:i:s', $since)));
154 foreach (explode(',', $tags) as $tag) {
155 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
159 if ('created' === $sort) {
160 $qb->orderBy('e.id', $order);
161 } elseif ('updated' === $sort) {
162 $qb->orderBy('e.updatedAt', $order);
165 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
167 return new Pagerfanta($pagerAdapter);
171 * Fetch an entry with a tag. Only used for tests.
177 public function findOneWithTags($userId)
179 $qb = $this->createQueryBuilder('e')
180 ->innerJoin('e.tags', 't')
181 ->innerJoin('e.user', 'u')
182 ->addSelect('t', 'u')
183 ->where('e.user=:userId')->setParameter('userId', $userId)
186 return $qb->getQuery()->getResult();
190 * Find distinct language for a given user.
191 * Used to build the filter language list.
193 * @param int $userId User id
197 public function findDistinctLanguageByUser($userId)
199 $results = $this->createQueryBuilder('e')
200 ->select('e.language')
201 ->where('e.user = :userId')->setParameter('userId', $userId)
202 ->andWhere('e.language IS NOT NULL')
203 ->groupBy('e.language')
204 ->orderBy('e.language', ' ASC')
209 foreach ($results as $result) {
210 $languages[$result['language']] = $result['language'];
217 * Used only in test case to get the right entry associated to the right user.
219 * @param string $username
223 public function findOneByUsernameAndNotArchived($username)
225 return $this->createQueryBuilder('e')
226 ->leftJoin('e.user', 'u')
227 ->where('u.username = :username')->setParameter('username', $username)
228 ->andWhere('e.isArchived = false')
235 * Remove a tag from all user entries.
237 * 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.
238 * 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:
240 * 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
245 public function removeTag($userId, Tag
$tag)
247 $entries = $this->getBuilderByUser($userId)
248 ->innerJoin('e.tags', 't')
249 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
253 foreach ($entries as $entry) {
254 $entry->removeTag($tag);
257 $this->getEntityManager()->flush();
261 * Remove tags from all user entries.
264 * @param Array<Tag> $tags
266 public function removeTags($userId, $tags)
268 foreach ($tags as $tag) {
269 $this->removeTag($userId, $tag);
274 * Find all entries that are attached to a give tag id.
281 public function findAllByTagId($userId, $tagId)
283 return $this->getBuilderByUser($userId)
284 ->innerJoin('e.tags', 't')
285 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
291 * Find an entry by its url and its owner.
292 * If it exists, return the entry otherwise return false.
299 public function findByUrlAndUserId($url, $userId)
301 $res = $this->createQueryBuilder('e')
302 ->where('e.url = :url')->setParameter('url', urldecode($url))
303 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
308 return current($res);
315 * Count all entries for a user.
321 public function countAllEntriesByUser($userId)
323 $qb = $this->createQueryBuilder('e')
325 ->where('e.user=:userId')->setParameter('userId', $userId)
328 return $qb->getQuery()->getSingleScalarResult();
332 * Count all entries for a tag and a user.
339 public function countAllEntriesByUserIdAndTagId($userId, $tagId)
341 $qb = $this->createQueryBuilder('e')
342 ->select('count(e.id)')
343 ->leftJoin('e.tags', 't')
344 ->where('e.user=:userId')->setParameter('userId', $userId)
345 ->andWhere('t.id=:tagId')->setParameter('tagId', $tagId)
348 return $qb->getQuery()->getSingleScalarResult();
352 * Remove all entries for a user id.
353 * Used when a user want to reset all informations.
357 public function removeAllByUserId($userId)
359 $this->getEntityManager()
360 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
361 ->setParameter('userId', $userId)
365 public function removeArchivedByUserId($userId)
367 $this->getEntityManager()
368 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
369 ->setParameter('userId', $userId)
374 * Get id and url from all entries
375 * Used for the clean-duplicates command.
377 public function getAllEntriesIdAndUrl($userId)
379 $qb = $this->createQueryBuilder('e')
380 ->select('e.id, e.url')
381 ->where('e.user = :userid')->setParameter(':userid', $userId);
383 return $qb->getQuery()->getArrayResult();
387 * Find all entries by url and owner.
394 public function findAllByUrlAndUserId($url, $userId)
396 return $this->createQueryBuilder('e')
397 ->where('e.url = :url')->setParameter('url', urldecode($url))
398 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
404 * Return a query builder to used by other getBuilderFor* method.
408 * @return QueryBuilder
410 private function getBuilderByUser($userId)
412 return $this->createQueryBuilder('e')
413 ->andWhere('e.user = :userId')->setParameter('userId', $userId)
414 ->orderBy('e.createdAt', 'desc')