X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=src%2FWallabag%2FCoreBundle%2FRepository%2FEntryRepository.php;h=749d2338c3b183314d7e9428546cb5f5c90e3d27;hb=b7c5fda512f29c99db69cb090fafb6f79abf6004;hp=9bda4e15e81fcfd8d7e33635607fefa7db01c723;hpb=1112e54772c9308ee3d7417869b5b8ef9b2b9812;p=github%2Fwallabag%2Fwallabag.git diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index 9bda4e15..749d2338 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php @@ -3,28 +3,14 @@ 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 { - /** - * Return a query builder to used by other getBuilderFor* method. - * - * @param int $userId - * - * @return QueryBuilder - */ - private function getBuilderByUser($userId) - { - return $this->createQueryBuilder('e') - ->andWhere('e.user = :userId')->setParameter('userId', $userId) - ->orderBy('e.createdAt', 'desc') - ; - } - /** * Retrieves all entries for a user. * @@ -35,7 +21,7 @@ class EntryRepository extends EntityRepository public function getBuilderForAllByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ; } @@ -49,7 +35,7 @@ class EntryRepository extends EntityRepository public function getBuilderForUnreadByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ->andWhere('e.isArchived = false') ; } @@ -64,7 +50,7 @@ class EntryRepository extends EntityRepository public function getBuilderForArchiveByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ->andWhere('e.isArchived = true') ; } @@ -79,7 +65,7 @@ class EntryRepository extends EntityRepository public function getBuilderForStarredByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc') ->andWhere('e.isStarred = true') ; } @@ -89,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'); @@ -108,7 +94,7 @@ class EntryRepository extends EntityRepository // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive $qb - ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%'.$term.'%') + ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%') ->leftJoin('e.tags', 't') ->groupBy('e.id'); @@ -125,7 +111,7 @@ class EntryRepository extends EntityRepository public function getBuilderForUntaggedByUser($userId) { return $this - ->getBuilderByUser($userId) + ->getSortedQueryBuilderByUser($userId) ->andWhere('size(e.tags) = 0'); } @@ -141,13 +127,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); @@ -158,16 +144,31 @@ class EntryRepository extends EntityRepository } if (null !== $isPublic) { - $qb->andWhere('e.uid IS '.(true === $isPublic ? 'NOT' : '').' NULL'); + $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL'); } if ($since > 0) { $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); } } @@ -187,7 +188,7 @@ class EntryRepository extends EntityRepository * * @param int $userId * - * @return Entry + * @return array */ public function findOneWithTags($userId) { @@ -195,7 +196,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(); @@ -259,7 +260,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() @@ -295,7 +296,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() @@ -319,7 +320,7 @@ class EntryRepository extends EntityRepository ->getQuery() ->getResult(); - if (count($res)) { + if (\count($res)) { return current($res); } @@ -337,30 +338,10 @@ class EntryRepository extends EntityRepository { $qb = $this->createQueryBuilder('e') ->select('count(e)') - ->where('e.user=:userId')->setParameter('userId', $userId) - ; - - return $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 $qb->getQuery()->getSingleScalarResult(); + return (int) $qb->getQuery()->getSingleScalarResult(); } /** @@ -389,7 +370,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') @@ -398,6 +379,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. * @@ -414,4 +412,46 @@ class EntryRepository extends EntityRepository ->getQuery() ->getResult(); } + + /** + * 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 + * @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); + } }