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 ->getBuilderByUser($userId)
29 * Retrieves unread entries for a user.
33 * @return QueryBuilder
35 public function getBuilderForUnreadByUser($userId)
38 ->getBuilderByUser($userId)
39 ->andWhere('e.isArchived = false')
44 * Retrieves read entries for a user.
48 * @return QueryBuilder
50 public function getBuilderForArchiveByUser($userId)
53 ->getBuilderByUser($userId)
54 ->andWhere('e.isArchived = true')
59 * Retrieves starred entries for a user.
63 * @return QueryBuilder
65 public function getBuilderForStarredByUser($userId)
68 ->getBuilderByUser($userId)
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 ->getBuilderByUser($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 * Retrieves untagged entries for a user.
109 * @return QueryBuilder
111 public function getBuilderForUntaggedByUser($userId)
114 ->getBuilderByUser($userId)
115 ->andWhere('size(e.tags) = 0');
122 * @param bool $isArchived
123 * @param bool $isStarred
124 * @param bool $isPublic
125 * @param string $sort
126 * @param string $order
128 * @param string $tags
132 public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
134 $qb = $this->createQueryBuilder('e')
135 ->leftJoin('e.tags', 't')
136 ->where('e.user =:userId')->setParameter('userId', $userId);
138 if (null !== $isArchived) {
139 $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
142 if (null !== $isStarred) {
143 $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
146 if (null !== $isPublic) {
147 $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
151 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \
DateTime(date('Y-m-d H:i:s', $since)));
155 foreach (explode(',', $tags) as $tag) {
156 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
160 if ('created' === $sort) {
161 $qb->orderBy('e.id', $order);
162 } elseif ('updated' === $sort) {
163 $qb->orderBy('e.updatedAt', $order);
166 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
168 return new Pagerfanta($pagerAdapter);
172 * Fetch an entry with a tag. Only used for tests.
178 public function findOneWithTags($userId)
180 $qb = $this->createQueryBuilder('e')
181 ->innerJoin('e.tags', 't')
182 ->innerJoin('e.user', 'u')
183 ->addSelect('t', 'u')
184 ->where('e.user=:userId')->setParameter('userId', $userId)
187 return $qb->getQuery()->getResult();
191 * Find distinct language for a given user.
192 * Used to build the filter language list.
194 * @param int $userId User id
198 public function findDistinctLanguageByUser($userId)
200 $results = $this->createQueryBuilder('e')
201 ->select('e.language')
202 ->where('e.user = :userId')->setParameter('userId', $userId)
203 ->andWhere('e.language IS NOT NULL')
204 ->groupBy('e.language')
205 ->orderBy('e.language', ' ASC')
210 foreach ($results as $result) {
211 $languages[$result['language']] = $result['language'];
218 * Used only in test case to get the right entry associated to the right user.
220 * @param string $username
224 public function findOneByUsernameAndNotArchived($username)
226 return $this->createQueryBuilder('e')
227 ->leftJoin('e.user', 'u')
228 ->where('u.username = :username')->setParameter('username', $username)
229 ->andWhere('e.isArchived = false')
236 * Remove a tag from all user entries.
238 * 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.
239 * 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:
241 * 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
246 public function removeTag($userId, Tag
$tag)
248 $entries = $this->getBuilderByUser($userId)
249 ->innerJoin('e.tags', 't')
250 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
254 foreach ($entries as $entry) {
255 $entry->removeTag($tag);
258 $this->getEntityManager()->flush();
262 * Remove tags from all user entries.
265 * @param Array<Tag> $tags
267 public function removeTags($userId, $tags)
269 foreach ($tags as $tag) {
270 $this->removeTag($userId, $tag);
275 * Find all entries that are attached to a give tag id.
282 public function findAllByTagId($userId, $tagId)
284 return $this->getBuilderByUser($userId)
285 ->innerJoin('e.tags', 't')
286 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
292 * Find an entry by its url and its owner.
293 * If it exists, return the entry otherwise return false.
300 public function findByUrlAndUserId($url, $userId)
302 $res = $this->createQueryBuilder('e')
303 ->where('e.url = :url')->setParameter('url', urldecode($url))
304 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
309 return current($res);
316 * Count all entries for a user.
322 public function countAllEntriesByUser($userId)
324 $qb = $this->createQueryBuilder('e')
326 ->where('e.user=:userId')->setParameter('userId', $userId)
329 return (int) $qb->getQuery()->getSingleScalarResult();
333 * Remove all entries for a user id.
334 * Used when a user want to reset all informations.
338 public function removeAllByUserId($userId)
340 $this->getEntityManager()
341 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
342 ->setParameter('userId', $userId)
346 public function removeArchivedByUserId($userId)
348 $this->getEntityManager()
349 ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
350 ->setParameter('userId', $userId)
355 * Get id and url from all entries
356 * Used for the clean-duplicates command.
358 public function findAllEntriesIdAndUrlByUserId($userId)
360 $qb = $this->createQueryBuilder('e')
361 ->select('e.id, e.url')
362 ->where('e.user = :userid')->setParameter(':userid', $userId);
364 return $qb->getQuery()->getArrayResult();
372 public function findAllEntriesIdByUserId($userId = null)
374 $qb = $this->createQueryBuilder('e')
377 if (null !== $userId) {
378 $qb->where('e.user = :userid')->setParameter(':userid', $userId);
381 return $qb->getQuery()->getArrayResult();
385 * Find all entries by url and owner.
392 public function findAllByUrlAndUserId($url, $userId)
394 return $this->createQueryBuilder('e')
395 ->where('e.url = :url')->setParameter('url', urldecode($url))
396 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
402 * Return a query builder to used by other getBuilderFor* method.
406 * @return QueryBuilder
408 private function getBuilderByUser($userId)
410 return $this->createQueryBuilder('e')
411 ->andWhere('e.user = :userId')->setParameter('userId', $userId)
412 ->orderBy('e.createdAt', 'desc')