src/Wallabag/CoreBundle/Repository/EntryRepository.php

   1 <?php
   2
   3 namespace Wallabag\CoreBundle\Repository;
   4
   5 use Doctrine\ORM\EntityRepository;
   6 use Doctrine\ORM\Query;
   7 use Pagerfanta\Adapter\DoctrineORMAdapter;
   8 use Pagerfanta\Pagerfanta;
   9 use Wallabag\CoreBundle\Entity\Tag;
  10
  11 class EntryRepository extends EntityRepository
  12 {
  13     private $lifeTime;
  14
  15     /**
  16      * Return a query builder to used by other getBuilderFor* method.
  17      *
  18      * @param int $userId
  19      *
  20      * @return QueryBuilder
  21      */
  22     private function getBuilderByUser($userId)
  23     {
  24         return $this->createQueryBuilder('e')
  25             ->leftJoin('e.user', 'u')
  26             ->andWhere('u.id = :userId')->setParameter('userId', $userId)
  27             ->orderBy('e.id', 'desc')
  28         ;
  29     }
  30
  31     /**
  32      * Retrieves all entries for a user.
  33      *
  34      * @param int $userId
  35      *
  36      * @return QueryBuilder
  37      */
  38     public function getBuilderForAllByUser($userId)
  39     {
  40         return $this
  41             ->getBuilderByUser($userId)
  42         ;
  43     }
  44
  45     /**
  46      * Retrieves unread entries for a user.
  47      *
  48      * @param int $userId
  49      *
  50      * @return QueryBuilder
  51      */
  52     public function getBuilderForUnreadByUser($userId)
  53     {
  54         return $this
  55             ->getBuilderByUser($userId)
  56             ->andWhere('e.isArchived = false')
  57         ;
  58     }
  59
  60     /**
  61      * Retrieves read entries for a user.
  62      *
  63      * @param int $userId
  64      *
  65      * @return QueryBuilder
  66      */
  67     public function getBuilderForArchiveByUser($userId)
  68     {
  69         return $this
  70             ->getBuilderByUser($userId)
  71             ->andWhere('e.isArchived = true')
  72         ;
  73     }
  74
  75     /**
  76      * Retrieves starred entries for a user.
  77      *
  78      * @param int $userId
  79      *
  80      * @return QueryBuilder
  81      */
  82     public function getBuilderForStarredByUser($userId)
  83     {
  84         return $this
  85             ->getBuilderByUser($userId)
  86             ->andWhere('e.isStarred = true')
  87         ;
  88     }
  89
  90     /**
  91      * Find Entries.
  92      *
  93      * @param int    $userId
  94      * @param bool   $isArchived
  95      * @param bool   $isStarred
  96      * @param string $sort
  97      * @param string $order
  98      * @param int    $since
  99      * @param string $tags
 100      *
 101      * @return array
 102      */
 103     public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
 104     {
 105         $qb = $this->createQueryBuilder('e')
 106             ->leftJoin('e.tags', 't')
 107             ->where('e.user =:userId')->setParameter('userId', $userId);
 108
 109         if (null !== $isArchived) {
 110             $qb->andWhere('e.isArchived =:isArchived')->setParameter('isArchived', (bool) $isArchived);
 111         }
 112
 113         if (null !== $isStarred) {
 114             $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred);
 115         }
 116
 117         if ($since >= 0) {
 118             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 119         }
 120
 121         if ('' !== $tags) {
 122             foreach (explode(',', $tags) as $tag) {
 123                 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
 124             }
 125         }
 126
 127         if ('created' === $sort) {
 128             $qb->orderBy('e.id', $order);
 129         } elseif ('updated' === $sort) {
 130             $qb->orderBy('e.updatedAt', $order);
 131         }
 132
 133         $pagerAdapter = new DoctrineORMAdapter($qb);
 134
 135         return new Pagerfanta($pagerAdapter);
 136     }
 137
 138     /**
 139      * Fetch an entry with a tag. Only used for tests.
 140      *
 141      * @param int $userId
 142      *
 143      * @return Entry
 144      */
 145     public function findOneWithTags($userId)
 146     {
 147         $qb = $this->createQueryBuilder('e')
 148             ->innerJoin('e.tags', 't')
 149             ->innerJoin('e.user', 'u')
 150             ->addSelect('t', 'u')
 151             ->where('e.user=:userId')->setParameter('userId', $userId)
 152         ;
 153
 154         return $qb->getQuery()->getResult();
 155     }
 156
 157     /**
 158      * Find distinct language for a given user.
 159      * Used to build the filter language list.
 160      *
 161      * @param int $userId User id
 162      *
 163      * @return array
 164      */
 165     public function findDistinctLanguageByUser($userId)
 166     {
 167         $results = $this->createQueryBuilder('e')
 168             ->select('e.language')
 169             ->where('e.user = :userId')->setParameter('userId', $userId)
 170             ->andWhere('e.language IS NOT NULL')
 171             ->groupBy('e.language')
 172             ->orderBy('e.language', ' ASC')
 173             ->getQuery()
 174             ->getResult();
 175
 176         $languages = [];
 177         foreach ($results as $result) {
 178             $languages[$result['language']] = $result['language'];
 179         }
 180
 181         return $languages;
 182     }
 183
 184     /**
 185      * Used only in test case to get the right entry associated to the right user.
 186      *
 187      * @param string $username
 188      *
 189      * @return Entry
 190      */
 191     public function findOneByUsernameAndNotArchived($username)
 192     {
 193         return $this->createQueryBuilder('e')
 194             ->leftJoin('e.user', 'u')
 195             ->where('u.username = :username')->setParameter('username', $username)
 196             ->andWhere('e.isArchived = false')
 197             ->setMaxResults(1)
 198             ->getQuery()
 199             ->getSingleResult();
 200     }
 201
 202     /**
 203      * Remove a tag from all user entries.
 204      *
 205      * 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.
 206      * 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:
 207      *
 208      * 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
 209      *
 210      * @param int $userId
 211      * @param Tag $tag
 212      */
 213     public function removeTag($userId, Tag $tag)
 214     {
 215         $entries = $this->getBuilderByUser($userId)
 216             ->innerJoin('e.tags', 't')
 217             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 218             ->getQuery()
 219             ->getResult();
 220
 221         foreach ($entries as $entry) {
 222             $entry->removeTag($tag);
 223         }
 224
 225         $this->getEntityManager()->flush();
 226     }
 227
 228     /**
 229      * Find all entries that are attached to a give tag id.
 230      *
 231      * @param int $userId
 232      * @param int $tagId
 233      *
 234      * @return array
 235      */
 236     public function findAllByTagId($userId, $tagId)
 237     {
 238         return $this->getBuilderByUser($userId)
 239             ->innerJoin('e.tags', 't')
 240             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 241             ->getQuery()
 242             ->getResult();
 243     }
 244
 245     /**
 246      * Find an entry by its url and its owner.
 247      * If it exists, return the entry otherwise return false.
 248      *
 249      * @param $url
 250      * @param $userId
 251      *
 252      * @return Entry|bool
 253      */
 254     public function findByUrlAndUserId($url, $userId)
 255     {
 256         $res = $this->createQueryBuilder('e')
 257             ->where('e.url = :url')->setParameter('url', $url)
 258             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 259             ->getQuery()
 260             ->getResult();
 261
 262         if (count($res)) {
 263             return current($res);
 264         }
 265
 266         return false;
 267     }
 268
 269     /**
 270      * Count all entries for a user.
 271      *
 272      * @param int $userId
 273      *
 274      * @return int
 275      */
 276     public function countAllEntriesByUsername($userId)
 277     {
 278         $qb = $this->createQueryBuilder('e')
 279             ->select('count(e)')
 280             ->where('e.user=:userId')->setParameter('userId', $userId)
 281         ;
 282
 283         return $qb->getQuery()->getSingleScalarResult();
 284     }
 285
 286     public function setLifeTime($lifeTime)
 287     {
 288         $this->lifeTime = $lifeTime;
 289     }
 290
 291     /**
 292      * Enable cache for a query.
 293      *
 294      * @param Query $query
 295      *
 296      * @return Query
 297      */
 298     public function enableCache(Query $query)
 299     {
 300         $query->useQueryCache(true);
 301         $query->useResultCache(true);
 302         $query->setResultCacheLifetime($this->lifeTime);
 303
 304         return $query;
 305     }
 306 }