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 * Return a query builder to used by other getBuilderFor* method.
18 * @return QueryBuilder
20 private function getBuilderByUser($userId)
22 return $this->createQueryBuilder('e')
23 ->andWhere('e.user = :userId')->setParameter('userId', $userId)
24 ->orderBy('e.createdAt', 'desc')
29 * Retrieves all entries for a user.
33 * @return QueryBuilder
35 public function getBuilderForAllByUser($userId)
38 ->getBuilderByUser($userId)
43 * Retrieves unread entries for a user.
47 * @return QueryBuilder
49 public function getBuilderForUnreadByUser($userId)
52 ->getBuilderByUser($userId)
53 ->andWhere('e.isArchived = false')
58 * Retrieves read entries for a user.
62 * @return QueryBuilder
64 public function getBuilderForArchiveByUser($userId)
67 ->getBuilderByUser($userId)
68 ->andWhere('e.isArchived = true')
73 * Retrieves starred entries for a user.
77 * @return QueryBuilder
79 public function getBuilderForStarredByUser($userId)
82 ->getBuilderByUser($userId)
83 ->andWhere('e.isStarred = true')
88 * Retrieves entries filtered with a search term for a user.
92 * @param strint $currentRoute
94 * @return QueryBuilder
96 public function getBuilderForSearchByUser($userId, $term, $currentRoute)
99 ->getBuilderByUser($userId);
101 if ('starred' === $currentRoute) {
102 $qb->andWhere('e.isStarred = true');
103 } elseif ('unread' === $currentRoute) {
104 $qb->andWhere('e.isArchived = false');
105 } elseif ('archive' === $currentRoute) {
106 $qb->andWhere('e.isArchived = true');
109 // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
111 ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%'.$term.'%')
112 ->leftJoin('e.tags', 't')
119 * Retrieves untagged entries for a user.
123 * @return QueryBuilder
125 public function getBuilderForUntaggedByUser($userId)
128 ->getBuilderByUser($userId)
129 ->andWhere('size(e.tags) = 0');
136 * @param bool $isArchived
137 * @param bool $isStarred
138 * @param string $sort
139 * @param string $order
141 * @param string $tags
145 public function findEntries($userId, $isArchived = null, $isStarred = 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);
160 $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \
DateTime(date('Y-m-d H:i:s', $since)));
164 foreach (explode(',', $tags) as $tag) {
165 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
169 if ('created' === $sort) {
170 $qb->orderBy('e.id', $order);
171 } elseif ('updated' === $sort) {
172 $qb->orderBy('e.updatedAt', $order);
175 $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
177 return new Pagerfanta($pagerAdapter);
181 * Fetch an entry with a tag. Only used for tests.
187 public function findOneWithTags($userId)
189 $qb = $this->createQueryBuilder('e')
190 ->innerJoin('e.tags', 't')
191 ->innerJoin('e.user', 'u')
192 ->addSelect('t', 'u')
193 ->where('e.user=:userId')->setParameter('userId', $userId)
196 return $qb->getQuery()->getResult();
200 * Find distinct language for a given user.
201 * Used to build the filter language list.
203 * @param int $userId User id
207 public function findDistinctLanguageByUser($userId)
209 $results = $this->createQueryBuilder('e')
210 ->select('e.language')
211 ->where('e.user = :userId')->setParameter('userId', $userId)
212 ->andWhere('e.language IS NOT NULL')
213 ->groupBy('e.language')
214 ->orderBy('e.language', ' ASC')
219 foreach ($results as $result) {
220 $languages[$result['language']] = $result['language'];
227 * Used only in test case to get the right entry associated to the right user.
229 * @param string $username
233 public function findOneByUsernameAndNotArchived($username)
235 return $this->createQueryBuilder('e')
236 ->leftJoin('e.user', 'u')
237 ->where('u.username = :username')->setParameter('username', $username)
238 ->andWhere('e.isArchived = false')
245 * Remove a tag from all user entries.
247 * 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.
248 * 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:
250 * 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
255 public function removeTag($userId, Tag
$tag)
257 $entries = $this->getBuilderByUser($userId)
258 ->innerJoin('e.tags', 't')
259 ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
263 foreach ($entries as $entry) {
264 $entry->removeTag($tag);
267 $this->getEntityManager()->flush();
271 * Remove tags from all user entries.
274 * @param Array<Tag> $tags
276 public function removeTags($userId, $tags)
278 foreach ($tags as $tag) {
279 $this->removeTag($userId, $tag);
284 * Find all entries that are attached to a give tag id.
291 public function findAllByTagId($userId, $tagId)
293 return $this->getBuilderByUser($userId)
294 ->innerJoin('e.tags', 't')
295 ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
301 * Find an entry by its url and its owner.
302 * If it exists, return the entry otherwise return false.
309 public function findByUrlAndUserId($url, $userId)
311 $res = $this->createQueryBuilder('e')
312 ->where('e.url = :url')->setParameter('url', urldecode($url))
313 ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
318 return current($res);
325 * Count all entries for a user.
331 public function countAllEntriesByUsername($userId)
333 $qb = $this->createQueryBuilder('e')
335 ->where('e.user=:userId')->setParameter('userId', $userId)
338 return $qb->getQuery()->getSingleScalarResult();
342 * Count all entries for a tag and a user.
349 public function countAllEntriesByUserIdAndTagId($userId, $tagId)
351 $qb = $this->createQueryBuilder('e')
352 ->select('count(e.id)')
353 ->leftJoin('e.tags', 't')
354 ->where('e.user=:userId')->setParameter('userId', $userId)
355 ->andWhere('t.id=:tagId')->setParameter('tagId', $tagId)
358 return $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)