X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=src%2FWallabag%2FCoreBundle%2FRepository%2FEntryRepository.php;h=34123eea699bff74a0ea1e7b126fd390a8b0dbf0;hb=b8115ff46b15a28021612b695de8785456f8b7a6;hp=c27ee90cc197c45ed9733db448b1d1f29f795741;hpb=71e1cbc8eb5928d393b0772055d6b711e90a09b3;p=github%2Fwallabag%2Fwallabag.git diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index c27ee90c..34123eea 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php @@ -3,9 +3,10 @@ namespace Wallabag\CoreBundle\Repository; use Doctrine\ORM\EntityRepository; -use Doctrine\ORM\Query; +use Doctrine\ORM\QueryBuilder; use Pagerfanta\Adapter\DoctrineORMAdapter; use Pagerfanta\Pagerfanta; +use Wallabag\CoreBundle\Entity\Entry; use Wallabag\CoreBundle\Entity\Tag; class EntryRepository extends EntityRepository @@ -20,7 +21,7 @@ class EntryRepository extends EntityRepository public function getBuilderForAllByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ; } @@ -34,7 +35,7 @@ class EntryRepository extends EntityRepository public function getBuilderForUnreadByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ->andWhere('e.isArchived = false') ; } @@ -49,7 +50,7 @@ class EntryRepository extends EntityRepository public function getBuilderForArchiveByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ->andWhere('e.isArchived = true') ; } @@ -64,7 +65,7 @@ class EntryRepository extends EntityRepository public function getBuilderForStarredByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc') ->andWhere('e.isStarred = true') ; } @@ -74,14 +75,14 @@ class EntryRepository extends EntityRepository * * @param int $userId * @param string $term - * @param strint $currentRoute + * @param string $currentRoute * * @return QueryBuilder */ public function getBuilderForSearchByUser($userId, $term, $currentRoute) { $qb = $this - ->getBuilderByUser($userId); + ->getSortedQueryBuilderByUser($userId); if ('starred' === $currentRoute) { $qb->andWhere('e.isStarred = true'); @@ -101,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,8 +111,21 @@ class EntryRepository extends EntityRepository public function getBuilderForUntaggedByUser($userId) { return $this - ->getBuilderByUser($userId) - ->andWhere('size(e.tags) = 0'); + ->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'); } /** @@ -126,13 +140,13 @@ class EntryRepository extends EntityRepository * @param int $since * @param string $tags * - * @return array + * @return Pagerfanta */ 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); @@ -150,9 +164,24 @@ 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); } } @@ -172,7 +201,7 @@ class EntryRepository extends EntityRepository * * @param int $userId * - * @return Entry + * @return array */ public function findOneWithTags($userId) { @@ -180,7 +209,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(); @@ -244,7 +273,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() @@ -280,7 +309,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() @@ -304,7 +333,7 @@ class EntryRepository extends EntityRepository ->getQuery() ->getResult(); - if (count($res)) { + if (\count($res)) { return current($res); } @@ -322,27 +351,7 @@ class EntryRepository extends EntityRepository { $qb = $this->createQueryBuilder('e') ->select('count(e)') - ->where('e.user=:userId')->setParameter('userId', $userId) - ; - - return (int) $qb->getQuery()->getSingleScalarResult(); - } - - /** - * Count all entries for a tag and a user. - * - * @param int $userId - * @param int $tagId - * - * @return int - */ - public function countAllEntriesByUserIdAndTagId($userId, $tagId) - { - $qb = $this->createQueryBuilder('e') - ->select('count(e.id)') - ->leftJoin('e.tags', 't') - ->where('e.user=:userId')->setParameter('userId', $userId) - ->andWhere('t.id=:tagId')->setParameter('tagId', $tagId) + ->where('e.user = :userId')->setParameter('userId', $userId) ; return (int) $qb->getQuery()->getSingleScalarResult(); @@ -374,7 +383,7 @@ class EntryRepository extends EntityRepository * Get id and url from all entries * Used for the clean-duplicates command. */ - public function getAllEntriesIdAndUrl($userId) + public function findAllEntriesIdAndUrlByUserId($userId) { $qb = $this->createQueryBuilder('e') ->select('e.id, e.url') @@ -383,6 +392,23 @@ class EntryRepository extends EntityRepository return $qb->getQuery()->getArrayResult(); } + /** + * @param int $userId + * + * @return array + */ + public function findAllEntriesIdByUserId($userId = null) + { + $qb = $this->createQueryBuilder('e') + ->select('e.id'); + + if (null !== $userId) { + $qb->where('e.user = :userid')->setParameter(':userid', $userId); + } + + return $qb->getQuery()->getArrayResult(); + } + /** * Find all entries by url and owner. * @@ -401,17 +427,44 @@ class EntryRepository extends EntityRepository } /** - * Return a query builder to used by other getBuilderFor* method. + * Return a query builder to be used by other getBuilderFor* method. * * @param int $userId * * @return QueryBuilder */ - private function getBuilderByUser($userId) + private function getQueryBuilderByUser($userId) { return $this->createQueryBuilder('e') - ->andWhere('e.user = :userId')->setParameter('userId', $userId) - ->orderBy('e.createdAt', 'desc') - ; + ->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 + * @param string $direction + * + * @return QueryBuilder + */ + private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc') + { + return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId)); + } + + /** + * 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); } }