diff options
Diffstat (limited to 'src/Wallabag/CoreBundle/Repository')
3 files changed, 196 insertions, 50 deletions
diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index 4071301d..b5e35eff 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php | |||
@@ -3,29 +3,15 @@ | |||
3 | namespace Wallabag\CoreBundle\Repository; | 3 | namespace Wallabag\CoreBundle\Repository; |
4 | 4 | ||
5 | use Doctrine\ORM\EntityRepository; | 5 | use Doctrine\ORM\EntityRepository; |
6 | use Doctrine\ORM\Query; | 6 | use Doctrine\ORM\QueryBuilder; |
7 | use Pagerfanta\Adapter\DoctrineORMAdapter; | 7 | use Pagerfanta\Adapter\DoctrineORMAdapter; |
8 | use Pagerfanta\Pagerfanta; | 8 | use Pagerfanta\Pagerfanta; |
9 | use Wallabag\CoreBundle\Entity\Entry; | ||
9 | use Wallabag\CoreBundle\Entity\Tag; | 10 | use Wallabag\CoreBundle\Entity\Tag; |
10 | 11 | ||
11 | class EntryRepository extends EntityRepository | 12 | class EntryRepository extends EntityRepository |
12 | { | 13 | { |
13 | /** | 14 | /** |
14 | * Return a query builder to used by other getBuilderFor* method. | ||
15 | * | ||
16 | * @param int $userId | ||
17 | * | ||
18 | * @return QueryBuilder | ||
19 | */ | ||
20 | private function getBuilderByUser($userId) | ||
21 | { | ||
22 | return $this->createQueryBuilder('e') | ||
23 | ->andWhere('e.user = :userId')->setParameter('userId', $userId) | ||
24 | ->orderBy('e.createdAt', 'desc') | ||
25 | ; | ||
26 | } | ||
27 | |||
28 | /** | ||
29 | * Retrieves all entries for a user. | 15 | * Retrieves all entries for a user. |
30 | * | 16 | * |
31 | * @param int $userId | 17 | * @param int $userId |
@@ -79,7 +65,7 @@ class EntryRepository extends EntityRepository | |||
79 | public function getBuilderForStarredByUser($userId) | 65 | public function getBuilderForStarredByUser($userId) |
80 | { | 66 | { |
81 | return $this | 67 | return $this |
82 | ->getBuilderByUser($userId) | 68 | ->getBuilderByUser($userId, 'starredAt', 'desc') |
83 | ->andWhere('e.isStarred = true') | 69 | ->andWhere('e.isStarred = true') |
84 | ; | 70 | ; |
85 | } | 71 | } |
@@ -89,7 +75,7 @@ class EntryRepository extends EntityRepository | |||
89 | * | 75 | * |
90 | * @param int $userId | 76 | * @param int $userId |
91 | * @param string $term | 77 | * @param string $term |
92 | * @param strint $currentRoute | 78 | * @param string $currentRoute |
93 | * | 79 | * |
94 | * @return QueryBuilder | 80 | * @return QueryBuilder |
95 | */ | 81 | */ |
@@ -108,7 +94,7 @@ class EntryRepository extends EntityRepository | |||
108 | 94 | ||
109 | // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive | 95 | // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive |
110 | $qb | 96 | $qb |
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.'%') | 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 . '%') |
112 | ->leftJoin('e.tags', 't') | 98 | ->leftJoin('e.tags', 't') |
113 | ->groupBy('e.id'); | 99 | ->groupBy('e.id'); |
114 | 100 | ||
@@ -135,34 +121,54 @@ class EntryRepository extends EntityRepository | |||
135 | * @param int $userId | 121 | * @param int $userId |
136 | * @param bool $isArchived | 122 | * @param bool $isArchived |
137 | * @param bool $isStarred | 123 | * @param bool $isStarred |
124 | * @param bool $isPublic | ||
138 | * @param string $sort | 125 | * @param string $sort |
139 | * @param string $order | 126 | * @param string $order |
140 | * @param int $since | 127 | * @param int $since |
141 | * @param string $tags | 128 | * @param string $tags |
142 | * | 129 | * |
143 | * @return array | 130 | * @return Pagerfanta |
144 | */ | 131 | */ |
145 | public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '') | 132 | public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '') |
146 | { | 133 | { |
147 | $qb = $this->createQueryBuilder('e') | 134 | $qb = $this->createQueryBuilder('e') |
148 | ->leftJoin('e.tags', 't') | 135 | ->leftJoin('e.tags', 't') |
149 | ->where('e.user =:userId')->setParameter('userId', $userId); | 136 | ->where('e.user = :userId')->setParameter('userId', $userId); |
150 | 137 | ||
151 | if (null !== $isArchived) { | 138 | if (null !== $isArchived) { |
152 | $qb->andWhere('e.isArchived =:isArchived')->setParameter('isArchived', (bool) $isArchived); | 139 | $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived); |
153 | } | 140 | } |
154 | 141 | ||
155 | if (null !== $isStarred) { | 142 | if (null !== $isStarred) { |
156 | $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred); | 143 | $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred); |
144 | } | ||
145 | |||
146 | if (null !== $isPublic) { | ||
147 | $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL'); | ||
157 | } | 148 | } |
158 | 149 | ||
159 | if ($since > 0) { | 150 | if ($since > 0) { |
160 | $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since))); | 151 | $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since))); |
161 | } | 152 | } |
162 | 153 | ||
163 | if ('' !== $tags) { | 154 | if (is_string($tags) && '' !== $tags) { |
164 | foreach (explode(',', $tags) as $tag) { | 155 | foreach (explode(',', $tags) as $i => $tag) { |
165 | $qb->andWhere('t.label = :label')->setParameter('label', $tag); | 156 | $entryAlias = 'e' . $i; |
157 | $tagAlias = 't' . $i; | ||
158 | |||
159 | // Complexe queries to ensure multiple tags are associated to an entry | ||
160 | // https://stackoverflow.com/a/6638146/569101 | ||
161 | $qb->andWhere($qb->expr()->in( | ||
162 | 'e.id', | ||
163 | $this->createQueryBuilder($entryAlias) | ||
164 | ->select($entryAlias . '.id') | ||
165 | ->leftJoin($entryAlias . '.tags', $tagAlias) | ||
166 | ->where($tagAlias . '.label = :label' . $i) | ||
167 | ->getDQL() | ||
168 | )); | ||
169 | |||
170 | // bound parameter to the main query builder | ||
171 | $qb->setParameter('label' . $i, $tag); | ||
166 | } | 172 | } |
167 | } | 173 | } |
168 | 174 | ||
@@ -182,7 +188,7 @@ class EntryRepository extends EntityRepository | |||
182 | * | 188 | * |
183 | * @param int $userId | 189 | * @param int $userId |
184 | * | 190 | * |
185 | * @return Entry | 191 | * @return array |
186 | */ | 192 | */ |
187 | public function findOneWithTags($userId) | 193 | public function findOneWithTags($userId) |
188 | { | 194 | { |
@@ -190,7 +196,7 @@ class EntryRepository extends EntityRepository | |||
190 | ->innerJoin('e.tags', 't') | 196 | ->innerJoin('e.tags', 't') |
191 | ->innerJoin('e.user', 'u') | 197 | ->innerJoin('e.user', 'u') |
192 | ->addSelect('t', 'u') | 198 | ->addSelect('t', 'u') |
193 | ->where('e.user=:userId')->setParameter('userId', $userId) | 199 | ->where('e.user = :userId')->setParameter('userId', $userId) |
194 | ; | 200 | ; |
195 | 201 | ||
196 | return $qb->getQuery()->getResult(); | 202 | return $qb->getQuery()->getResult(); |
@@ -328,47 +334,98 @@ class EntryRepository extends EntityRepository | |||
328 | * | 334 | * |
329 | * @return int | 335 | * @return int |
330 | */ | 336 | */ |
331 | public function countAllEntriesByUsername($userId) | 337 | public function countAllEntriesByUser($userId) |
332 | { | 338 | { |
333 | $qb = $this->createQueryBuilder('e') | 339 | $qb = $this->createQueryBuilder('e') |
334 | ->select('count(e)') | 340 | ->select('count(e)') |
335 | ->where('e.user=:userId')->setParameter('userId', $userId) | 341 | ->where('e.user = :userId')->setParameter('userId', $userId) |
336 | ; | 342 | ; |
337 | 343 | ||
338 | return $qb->getQuery()->getSingleScalarResult(); | 344 | return (int) $qb->getQuery()->getSingleScalarResult(); |
339 | } | 345 | } |
340 | 346 | ||
341 | /** | 347 | /** |
342 | * Count all entries for a tag and a user. | 348 | * Remove all entries for a user id. |
349 | * Used when a user want to reset all informations. | ||
343 | * | 350 | * |
344 | * @param int $userId | 351 | * @param int $userId |
345 | * @param int $tagId | 352 | */ |
353 | public function removeAllByUserId($userId) | ||
354 | { | ||
355 | $this->getEntityManager() | ||
356 | ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId') | ||
357 | ->setParameter('userId', $userId) | ||
358 | ->execute(); | ||
359 | } | ||
360 | |||
361 | public function removeArchivedByUserId($userId) | ||
362 | { | ||
363 | $this->getEntityManager() | ||
364 | ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE') | ||
365 | ->setParameter('userId', $userId) | ||
366 | ->execute(); | ||
367 | } | ||
368 | |||
369 | /** | ||
370 | * Get id and url from all entries | ||
371 | * Used for the clean-duplicates command. | ||
372 | */ | ||
373 | public function findAllEntriesIdAndUrlByUserId($userId) | ||
374 | { | ||
375 | $qb = $this->createQueryBuilder('e') | ||
376 | ->select('e.id, e.url') | ||
377 | ->where('e.user = :userid')->setParameter(':userid', $userId); | ||
378 | |||
379 | return $qb->getQuery()->getArrayResult(); | ||
380 | } | ||
381 | |||
382 | /** | ||
383 | * @param int $userId | ||
346 | * | 384 | * |
347 | * @return int | 385 | * @return array |
348 | */ | 386 | */ |
349 | public function countAllEntriesByUserIdAndTagId($userId, $tagId) | 387 | public function findAllEntriesIdByUserId($userId = null) |
350 | { | 388 | { |
351 | $qb = $this->createQueryBuilder('e') | 389 | $qb = $this->createQueryBuilder('e') |
352 | ->select('count(e.id)') | 390 | ->select('e.id'); |
353 | ->leftJoin('e.tags', 't') | ||
354 | ->where('e.user=:userId')->setParameter('userId', $userId) | ||
355 | ->andWhere('t.id=:tagId')->setParameter('tagId', $tagId) | ||
356 | ; | ||
357 | 391 | ||
358 | return $qb->getQuery()->getSingleScalarResult(); | 392 | if (null !== $userId) { |
393 | $qb->where('e.user = :userid')->setParameter(':userid', $userId); | ||
394 | } | ||
395 | |||
396 | return $qb->getQuery()->getArrayResult(); | ||
359 | } | 397 | } |
360 | 398 | ||
361 | /** | 399 | /** |
362 | * Remove all entries for a user id. | 400 | * Find all entries by url and owner. |
363 | * Used when a user want to reset all informations. | ||
364 | * | 401 | * |
365 | * @param int $userId | 402 | * @param $url |
403 | * @param $userId | ||
404 | * | ||
405 | * @return array | ||
366 | */ | 406 | */ |
367 | public function removeAllByUserId($userId) | 407 | public function findAllByUrlAndUserId($url, $userId) |
368 | { | 408 | { |
369 | $this->getEntityManager() | 409 | return $this->createQueryBuilder('e') |
370 | ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId') | 410 | ->where('e.url = :url')->setParameter('url', urldecode($url)) |
371 | ->setParameter('userId', $userId) | 411 | ->andWhere('e.user = :user_id')->setParameter('user_id', $userId) |
372 | ->execute(); | 412 | ->getQuery() |
413 | ->getResult(); | ||
414 | } | ||
415 | |||
416 | /** | ||
417 | * Return a query builder to used by other getBuilderFor* method. | ||
418 | * | ||
419 | * @param int $userId | ||
420 | * @param string $sortBy | ||
421 | * @param string $direction | ||
422 | * | ||
423 | * @return QueryBuilder | ||
424 | */ | ||
425 | private function getBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc') | ||
426 | { | ||
427 | return $this->createQueryBuilder('e') | ||
428 | ->andWhere('e.user = :userId')->setParameter('userId', $userId) | ||
429 | ->orderBy(sprintf('e.%s', $sortBy), $direction); | ||
373 | } | 430 | } |
374 | } | 431 | } |
diff --git a/src/Wallabag/CoreBundle/Repository/SiteCredentialRepository.php b/src/Wallabag/CoreBundle/Repository/SiteCredentialRepository.php new file mode 100644 index 00000000..36906761 --- /dev/null +++ b/src/Wallabag/CoreBundle/Repository/SiteCredentialRepository.php | |||
@@ -0,0 +1,47 @@ | |||
1 | <?php | ||
2 | |||
3 | namespace Wallabag\CoreBundle\Repository; | ||
4 | |||
5 | use Wallabag\CoreBundle\Helper\CryptoProxy; | ||
6 | |||
7 | /** | ||
8 | * SiteCredentialRepository. | ||
9 | */ | ||
10 | class SiteCredentialRepository extends \Doctrine\ORM\EntityRepository | ||
11 | { | ||
12 | private $cryptoProxy; | ||
13 | |||
14 | public function setCrypto(CryptoProxy $cryptoProxy) | ||
15 | { | ||
16 | $this->cryptoProxy = $cryptoProxy; | ||
17 | } | ||
18 | |||
19 | /** | ||
20 | * Retrieve one username/password for the given host and userId. | ||
21 | * | ||
22 | * @param string $host | ||
23 | * @param int $userId | ||
24 | * | ||
25 | * @return null|array | ||
26 | */ | ||
27 | public function findOneByHostAndUser($host, $userId) | ||
28 | { | ||
29 | $res = $this->createQueryBuilder('s') | ||
30 | ->select('s.username', 's.password') | ||
31 | ->where('s.host = :hostname')->setParameter('hostname', $host) | ||
32 | ->andWhere('s.user = :userId')->setParameter('userId', $userId) | ||
33 | ->setMaxResults(1) | ||
34 | ->getQuery() | ||
35 | ->getOneOrNullResult(); | ||
36 | |||
37 | if (null === $res) { | ||
38 | return; | ||
39 | } | ||
40 | |||
41 | // decrypt user & password before returning them | ||
42 | $res['username'] = $this->cryptoProxy->decrypt($res['username']); | ||
43 | $res['password'] = $this->cryptoProxy->decrypt($res['password']); | ||
44 | |||
45 | return $res; | ||
46 | } | ||
47 | } | ||
diff --git a/src/Wallabag/CoreBundle/Repository/TagRepository.php b/src/Wallabag/CoreBundle/Repository/TagRepository.php index 2182df25..5c45211f 100644 --- a/src/Wallabag/CoreBundle/Repository/TagRepository.php +++ b/src/Wallabag/CoreBundle/Repository/TagRepository.php | |||
@@ -3,6 +3,7 @@ | |||
3 | namespace Wallabag\CoreBundle\Repository; | 3 | namespace Wallabag\CoreBundle\Repository; |
4 | 4 | ||
5 | use Doctrine\ORM\EntityRepository; | 5 | use Doctrine\ORM\EntityRepository; |
6 | use Wallabag\CoreBundle\Entity\Tag; | ||
6 | 7 | ||
7 | class TagRepository extends EntityRepository | 8 | class TagRepository extends EntityRepository |
8 | { | 9 | { |
@@ -62,6 +63,27 @@ class TagRepository extends EntityRepository | |||
62 | } | 63 | } |
63 | 64 | ||
64 | /** | 65 | /** |
66 | * Find all tags (flat) per user with nb entries. | ||
67 | * | ||
68 | * @param int $userId | ||
69 | * | ||
70 | * @return array | ||
71 | */ | ||
72 | public function findAllFlatTagsWithNbEntries($userId) | ||
73 | { | ||
74 | return $this->createQueryBuilder('t') | ||
75 | ->select('t.id, t.label, t.slug, count(e.id) as nbEntries') | ||
76 | ->distinct(true) | ||
77 | ->leftJoin('t.entries', 'e') | ||
78 | ->where('e.user = :userId') | ||
79 | ->groupBy('t.id') | ||
80 | ->orderBy('t.slug') | ||
81 | ->setParameter('userId', $userId) | ||
82 | ->getQuery() | ||
83 | ->getArrayResult(); | ||
84 | } | ||
85 | |||
86 | /** | ||
65 | * Used only in test case to get a tag for our entry. | 87 | * Used only in test case to get a tag for our entry. |
66 | * | 88 | * |
67 | * @return Tag | 89 | * @return Tag |
@@ -76,4 +98,24 @@ class TagRepository extends EntityRepository | |||
76 | ->getQuery() | 98 | ->getQuery() |
77 | ->getSingleResult(); | 99 | ->getSingleResult(); |
78 | } | 100 | } |
101 | |||
102 | public function findForArchivedArticlesByUser($userId) | ||
103 | { | ||
104 | $ids = $this->createQueryBuilder('t') | ||
105 | ->select('t.id') | ||
106 | ->leftJoin('t.entries', 'e') | ||
107 | ->where('e.user = :userId')->setParameter('userId', $userId) | ||
108 | ->andWhere('e.isArchived = true') | ||
109 | ->groupBy('t.id') | ||
110 | ->orderBy('t.slug') | ||
111 | ->getQuery() | ||
112 | ->getArrayResult(); | ||
113 | |||
114 | $tags = []; | ||
115 | foreach ($ids as $id) { | ||
116 | $tags[] = $this->find($id); | ||
117 | } | ||
118 | |||
119 | return $tags; | ||
120 | } | ||
79 | } | 121 | } |