X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=src%2FWallabag%2FCoreBundle%2FRepository%2FEntryRepository.php;h=879e66719a60ef39d561d5f65424c8d2a7da96a6;hb=9a57653aec85b0f5220436d5cb76545e66c24a11;hp=ecc159fc0463b8585c75d90e1a8ba53d76e99d80;hpb=3af5d41759c13420faa6f426491f1e95e5754908;p=github%2Fwallabag%2Fwallabag.git diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index ecc159fc..879e6671 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php @@ -21,7 +21,7 @@ class EntryRepository extends EntityRepository public function getBuilderForAllByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ; } @@ -35,7 +35,7 @@ class EntryRepository extends EntityRepository public function getBuilderForUnreadByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ->andWhere('e.isArchived = false') ; } @@ -50,7 +50,7 @@ class EntryRepository extends EntityRepository public function getBuilderForArchiveByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc') ->andWhere('e.isArchived = true') ; } @@ -65,7 +65,7 @@ class EntryRepository extends EntityRepository public function getBuilderForStarredByUser($userId) { return $this - ->getBuilderByUser($userId, 'starredAt', 'desc') + ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc') ->andWhere('e.isStarred = true') ; } @@ -82,7 +82,7 @@ class EntryRepository extends EntityRepository public function getBuilderForSearchByUser($userId, $term, $currentRoute) { $qb = $this - ->getBuilderByUser($userId); + ->getSortedQueryBuilderByUser($userId); if ('starred' === $currentRoute) { $qb->andWhere('e.isStarred = true'); @@ -102,7 +102,7 @@ class EntryRepository extends EntityRepository } /** - * Retrieves untagged entries for a user. + * Retrieve a sorted list of untagged entries for a user. * * @param int $userId * @@ -110,9 +110,21 @@ class EntryRepository extends EntityRepository */ public function getBuilderForUntaggedByUser($userId) { - return $this - ->getBuilderByUser($userId) - ->andWhere('size(e.tags) = 0'); + return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId)); + } + + /** + * Retrieve untagged entries for a user. + * + * @param int $userId + * + * @return QueryBuilder + */ + public function getRawBuilderForUntaggedByUser($userId) + { + return $this->getQueryBuilderByUser($userId) + ->leftJoin('e.tags', 't') + ->andWhere('t.id is null'); } /** @@ -129,11 +141,11 @@ class EntryRepository extends EntityRepository * * @return Pagerfanta */ - public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '') + public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '') { $qb = $this->createQueryBuilder('e') ->leftJoin('e.tags', 't') - ->where('e.user =:userId')->setParameter('userId', $userId); + ->where('e.user = :userId')->setParameter('userId', $userId); if (null !== $isArchived) { $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived); @@ -151,16 +163,37 @@ class EntryRepository extends EntityRepository $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since))); } - if ('' !== $tags) { - foreach (explode(',', $tags) as $tag) { - $qb->andWhere('t.label = :label')->setParameter('label', $tag); + if (\is_string($tags) && '' !== $tags) { + foreach (explode(',', $tags) as $i => $tag) { + $entryAlias = 'e' . $i; + $tagAlias = 't' . $i; + + // Complexe queries to ensure multiple tags are associated to an entry + // https://stackoverflow.com/a/6638146/569101 + $qb->andWhere($qb->expr()->in( + 'e.id', + $this->createQueryBuilder($entryAlias) + ->select($entryAlias . '.id') + ->leftJoin($entryAlias . '.tags', $tagAlias) + ->where($tagAlias . '.label = :label' . $i) + ->getDQL() + )); + + // bound parameter to the main query builder + $qb->setParameter('label' . $i, $tag); } } + if (!\in_array(strtolower($order), ['asc', 'desc'], true)) { + throw new \Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc'); + } + if ('created' === $sort) { $qb->orderBy('e.id', $order); } elseif ('updated' === $sort) { $qb->orderBy('e.updatedAt', $order); + } elseif ('archived' === $sort) { + $qb->orderBy('e.archivedAt', $order); } $pagerAdapter = new DoctrineORMAdapter($qb, true, false); @@ -181,7 +214,7 @@ class EntryRepository extends EntityRepository ->innerJoin('e.tags', 't') ->innerJoin('e.user', 'u') ->addSelect('t', 'u') - ->where('e.user=:userId')->setParameter('userId', $userId) + ->where('e.user = :userId')->setParameter('userId', $userId) ; return $qb->getQuery()->getResult(); @@ -245,7 +278,7 @@ class EntryRepository extends EntityRepository */ public function removeTag($userId, Tag $tag) { - $entries = $this->getBuilderByUser($userId) + $entries = $this->getSortedQueryBuilderByUser($userId) ->innerJoin('e.tags', 't') ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId()) ->getQuery() @@ -281,7 +314,7 @@ class EntryRepository extends EntityRepository */ public function findAllByTagId($userId, $tagId) { - return $this->getBuilderByUser($userId) + return $this->getSortedQueryBuilderByUser($userId) ->innerJoin('e.tags', 't') ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId) ->getQuery() @@ -305,7 +338,7 @@ class EntryRepository extends EntityRepository ->getQuery() ->getResult(); - if (count($res)) { + if (\count($res)) { return current($res); } @@ -323,7 +356,7 @@ class EntryRepository extends EntityRepository { $qb = $this->createQueryBuilder('e') ->select('count(e)') - ->where('e.user=:userId')->setParameter('userId', $userId) + ->where('e.user = :userId')->setParameter('userId', $userId) ; return (int) $qb->getQuery()->getSingleScalarResult(); @@ -399,7 +432,68 @@ class EntryRepository extends EntityRepository } /** - * Return a query builder to used by other getBuilderFor* method. + * Returns a random entry, filtering by status. + * + * @param $userId + * @param string $type can be unread, archive, starred, etc + * + * @throws \Doctrine\ORM\NoResultException + * @throws \Doctrine\ORM\NonUniqueResultException + * + * @see https://github.com/doctrine/doctrine2/issues/5479#issuecomment-403862934 + * + * @return Entry + */ + public function getRandomEntry($userId, $type = '') + { + $qb = $this->getQueryBuilderByUser($userId) + ->select('MIN(e.id)', 'MAX(e.id)'); + + switch ($type) { + case 'unread': + $qb->andWhere('e.isArchived = false'); + break; + case 'archive': + $qb->andWhere('e.isArchived = true'); + break; + case 'starred': + $qb->andWhere('e.isStarred = true'); + break; + case 'untagged': + $qb->leftJoin('e.tags', 't'); + $qb->andWhere('t.id is null'); + break; + } + + $idLimits = $qb + ->getQuery() + ->getOneOrNullResult(); + $randomPossibleIds = rand($idLimits[1], $idLimits[2]); + + return $qb + ->select('e') + ->andWhere('e.id >= :random_id') + ->setParameter('random_id', $randomPossibleIds) + ->setMaxResults(1) + ->getQuery() + ->getSingleResult(); + } + + /** + * Return a query builder to be used by other getBuilderFor* method. + * + * @param int $userId + * + * @return QueryBuilder + */ + private function getQueryBuilderByUser($userId) + { + return $this->createQueryBuilder('e') + ->andWhere('e.user = :userId')->setParameter('userId', $userId); + } + + /** + * Return a sorted query builder to be used by other getBuilderFor* method. * * @param int $userId * @param string $sortBy @@ -407,10 +501,22 @@ class EntryRepository extends EntityRepository * * @return QueryBuilder */ - private function getBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc') + private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc') { - return $this->createQueryBuilder('e') - ->andWhere('e.user = :userId')->setParameter('userId', $userId) - ->orderBy(sprintf('e.%s', $sortBy), $direction); + return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction); + } + + /** + * Return the given QueryBuilder with an orderBy() call. + * + * @param QueryBuilder $qb + * @param string $sortBy + * @param string $direction + * + * @return QueryBuilder + */ + private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc') + { + return $qb->orderBy(sprintf('e.%s', $sortBy), $direction); } }