X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=src%2FWallabag%2FCoreBundle%2FRepository%2FEntryRepository.php;h=4071301de0206c1dfbc409b1971d7bd9ff0131c9;hb=eac09b48b08f5cbc0d219da000026c38a265583a;hp=2286317c177097f53ff8cf389e1dd05a72dfc0a5;hpb=02d17813a11d27e0232c38d1adf037cefdb176c1;p=github%2Fwallabag%2Fwallabag.git diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index 2286317c..4071301d 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php @@ -3,8 +3,10 @@ namespace Wallabag\CoreBundle\Repository; use Doctrine\ORM\EntityRepository; +use Doctrine\ORM\Query; use Pagerfanta\Adapter\DoctrineORMAdapter; use Pagerfanta\Pagerfanta; +use Wallabag\CoreBundle\Entity\Tag; class EntryRepository extends EntityRepository { @@ -18,9 +20,8 @@ class EntryRepository extends EntityRepository private function getBuilderByUser($userId) { return $this->createQueryBuilder('e') - ->leftJoin('e.user', 'u') - ->andWhere('u.id = :userId')->setParameter('userId', $userId) - ->orderBy('e.id', 'desc') + ->andWhere('e.user = :userId')->setParameter('userId', $userId) + ->orderBy('e.createdAt', 'desc') ; } @@ -83,6 +84,51 @@ class EntryRepository extends EntityRepository ; } + /** + * Retrieves entries filtered with a search term for a user. + * + * @param int $userId + * @param string $term + * @param strint $currentRoute + * + * @return QueryBuilder + */ + public function getBuilderForSearchByUser($userId, $term, $currentRoute) + { + $qb = $this + ->getBuilderByUser($userId); + + if ('starred' === $currentRoute) { + $qb->andWhere('e.isStarred = true'); + } elseif ('unread' === $currentRoute) { + $qb->andWhere('e.isArchived = false'); + } elseif ('archive' === $currentRoute) { + $qb->andWhere('e.isArchived = true'); + } + + // 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.'%') + ->leftJoin('e.tags', 't') + ->groupBy('e.id'); + + return $qb; + } + + /** + * Retrieves untagged entries for a user. + * + * @param int $userId + * + * @return QueryBuilder + */ + public function getBuilderForUntaggedByUser($userId) + { + return $this + ->getBuilderByUser($userId) + ->andWhere('size(e.tags) = 0'); + } + /** * Find Entries. * @@ -91,12 +137,15 @@ class EntryRepository extends EntityRepository * @param bool $isStarred * @param string $sort * @param string $order + * @param int $since + * @param string $tags * * @return array */ - public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC') + public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '') { $qb = $this->createQueryBuilder('e') + ->leftJoin('e.tags', 't') ->where('e.user =:userId')->setParameter('userId', $userId); if (null !== $isArchived) { @@ -107,13 +156,23 @@ class EntryRepository extends EntityRepository $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred); } + 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 ('created' === $sort) { $qb->orderBy('e.id', $order); } elseif ('updated' === $sort) { $qb->orderBy('e.updatedAt', $order); } - $pagerAdapter = new DoctrineORMAdapter($qb); + $pagerAdapter = new DoctrineORMAdapter($qb, true, false); return new Pagerfanta($pagerAdapter); } @@ -121,6 +180,8 @@ class EntryRepository extends EntityRepository /** * Fetch an entry with a tag. Only used for tests. * + * @param int $userId + * * @return Entry */ public function findOneWithTags($userId) @@ -154,7 +215,7 @@ class EntryRepository extends EntityRepository ->getQuery() ->getResult(); - $languages = array(); + $languages = []; foreach ($results as $result) { $languages[$result['language']] = $result['language']; } @@ -163,9 +224,9 @@ class EntryRepository extends EntityRepository } /** - * Used only in test case to get the right entry associated to the right user + * Used only in test case to get the right entry associated to the right user. * - * @param string $username + * @param string $username * * @return Entry */ @@ -179,4 +240,135 @@ class EntryRepository extends EntityRepository ->getQuery() ->getSingleResult(); } + + /** + * Remove a tag from all user entries. + * + * 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. + * 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: + * + * 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 + * + * @param int $userId + * @param Tag $tag + */ + public function removeTag($userId, Tag $tag) + { + $entries = $this->getBuilderByUser($userId) + ->innerJoin('e.tags', 't') + ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId()) + ->getQuery() + ->getResult(); + + foreach ($entries as $entry) { + $entry->removeTag($tag); + } + + $this->getEntityManager()->flush(); + } + + /** + * Remove tags from all user entries. + * + * @param int $userId + * @param Array $tags + */ + public function removeTags($userId, $tags) + { + foreach ($tags as $tag) { + $this->removeTag($userId, $tag); + } + } + + /** + * Find all entries that are attached to a give tag id. + * + * @param int $userId + * @param int $tagId + * + * @return array + */ + public function findAllByTagId($userId, $tagId) + { + return $this->getBuilderByUser($userId) + ->innerJoin('e.tags', 't') + ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId) + ->getQuery() + ->getResult(); + } + + /** + * Find an entry by its url and its owner. + * If it exists, return the entry otherwise return false. + * + * @param $url + * @param $userId + * + * @return Entry|bool + */ + public function findByUrlAndUserId($url, $userId) + { + $res = $this->createQueryBuilder('e') + ->where('e.url = :url')->setParameter('url', urldecode($url)) + ->andWhere('e.user = :user_id')->setParameter('user_id', $userId) + ->getQuery() + ->getResult(); + + if (count($res)) { + return current($res); + } + + return false; + } + + /** + * Count all entries for a user. + * + * @param int $userId + * + * @return int + */ + public function countAllEntriesByUsername($userId) + { + $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) + ; + + return $qb->getQuery()->getSingleScalarResult(); + } + + /** + * Remove all entries for a user id. + * Used when a user want to reset all informations. + * + * @param int $userId + */ + public function removeAllByUserId($userId) + { + $this->getEntityManager() + ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId') + ->setParameter('userId', $userId) + ->execute(); + } }