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      * Retrieves untagged entries for a user.
  92      *
  93      * @param int $userId
  94      *
  95      * @return QueryBuilder
  96      */
  97     public function getBuilderForUntaggedByUser($userId)
  98     {
  99         return $this
 100             ->getBuilderByUser($userId)
 101             ->leftJoin('e.tags', 't')
 102             ->groupBy('e.id')
 103             ->having('count(t.id) = 0');
 104     }
 105
 106     /**
 107      * Find Entries.
 108      *
 109      * @param int    $userId
 110      * @param bool   $isArchived
 111      * @param bool   $isStarred
 112      * @param string $sort
 113      * @param string $order
 114      * @param int    $since
 115      * @param string $tags
 116      *
 117      * @return array
 118      */
 119     public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
 120     {
 121         $qb = $this->createQueryBuilder('e')
 122             ->leftJoin('e.tags', 't')
 123             ->where('e.user =:userId')->setParameter('userId', $userId);
 124
 125         if (null !== $isArchived) {
 126             $qb->andWhere('e.isArchived =:isArchived')->setParameter('isArchived', (bool) $isArchived);
 127         }
 128
 129         if (null !== $isStarred) {
 130             $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred);
 131         }
 132
 133         if ($since >= 0) {
 134             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 135         }
 136
 137         if ('' !== $tags) {
 138             foreach (explode(',', $tags) as $tag) {
 139                 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
 140             }
 141         }
 142
 143         if ('created' === $sort) {
 144             $qb->orderBy('e.id', $order);
 145         } elseif ('updated' === $sort) {
 146             $qb->orderBy('e.updatedAt', $order);
 147         }
 148
 149         $pagerAdapter = new DoctrineORMAdapter($qb);
 150
 151         return new Pagerfanta($pagerAdapter);
 152     }
 153
 154     /**
 155      * Fetch an entry with a tag. Only used for tests.
 156      *
 157      * @param int $userId
 158      *
 159      * @return Entry
 160      */
 161     public function findOneWithTags($userId)
 162     {
 163         $qb = $this->createQueryBuilder('e')
 164             ->innerJoin('e.tags', 't')
 165             ->innerJoin('e.user', 'u')
 166             ->addSelect('t', 'u')
 167             ->where('e.user=:userId')->setParameter('userId', $userId)
 168         ;
 169
 170         return $qb->getQuery()->getResult();
 171     }
 172
 173     /**
 174      * Find distinct language for a given user.
 175      * Used to build the filter language list.
 176      *
 177      * @param int $userId User id
 178      *
 179      * @return array
 180      */
 181     public function findDistinctLanguageByUser($userId)
 182     {
 183         $results = $this->createQueryBuilder('e')
 184             ->select('e.language')
 185             ->where('e.user = :userId')->setParameter('userId', $userId)
 186             ->andWhere('e.language IS NOT NULL')
 187             ->groupBy('e.language')
 188             ->orderBy('e.language', ' ASC')
 189             ->getQuery()
 190             ->getResult();
 191
 192         $languages = [];
 193         foreach ($results as $result) {
 194             $languages[$result['language']] = $result['language'];
 195         }
 196
 197         return $languages;
 198     }
 199
 200     /**
 201      * Used only in test case to get the right entry associated to the right user.
 202      *
 203      * @param string $username
 204      *
 205      * @return Entry
 206      */
 207     public function findOneByUsernameAndNotArchived($username)
 208     {
 209         return $this->createQueryBuilder('e')
 210             ->leftJoin('e.user', 'u')
 211             ->where('u.username = :username')->setParameter('username', $username)
 212             ->andWhere('e.isArchived = false')
 213             ->setMaxResults(1)
 214             ->getQuery()
 215             ->getSingleResult();
 216     }
 217
 218     /**
 219      * Remove a tag from all user entries.
 220      *
 221      * 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.
 222      * 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:
 223      *
 224      * 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
 225      *
 226      * @param int $userId
 227      * @param Tag $tag
 228      */
 229     public function removeTag($userId, Tag $tag)
 230     {
 231         $entries = $this->getBuilderByUser($userId)
 232             ->innerJoin('e.tags', 't')
 233             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 234             ->getQuery()
 235             ->getResult();
 236
 237         foreach ($entries as $entry) {
 238             $entry->removeTag($tag);
 239         }
 240
 241         $this->getEntityManager()->flush();
 242     }
 243
 244     /**
 245      * Remove tags from all user entries.
 246      *
 247      * @param int        $userId
 248      * @param Array<Tag> $tags
 249      */
 250     public function removeTags($userId, $tags)
 251     {
 252         foreach ($tags as $tag) {
 253             $this->removeTag($userId, $tag);
 254         }
 255     }
 256
 257     /**
 258      * Find all entries that are attached to a give tag id.
 259      *
 260      * @param int $userId
 261      * @param int $tagId
 262      *
 263      * @return array
 264      */
 265     public function findAllByTagId($userId, $tagId)
 266     {
 267         return $this->getBuilderByUser($userId)
 268             ->innerJoin('e.tags', 't')
 269             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 270             ->getQuery()
 271             ->getResult();
 272     }
 273
 274     /**
 275      * Find an entry by its url and its owner.
 276      * If it exists, return the entry otherwise return false.
 277      *
 278      * @param $url
 279      * @param $userId
 280      *
 281      * @return Entry|bool
 282      */
 283     public function findByUrlAndUserId($url, $userId)
 284     {
 285         $res = $this->createQueryBuilder('e')
 286             ->where('e.url = :url')->setParameter('url', $url)
 287             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 288             ->getQuery()
 289             ->getResult();
 290
 291         if (count($res)) {
 292             return current($res);
 293         }
 294
 295         return false;
 296     }
 297
 298     /**
 299      * Count all entries for a user.
 300      *
 301      * @param int $userId
 302      *
 303      * @return int
 304      */
 305     public function countAllEntriesByUsername($userId)
 306     {
 307         $qb = $this->createQueryBuilder('e')
 308             ->select('count(e)')
 309             ->where('e.user=:userId')->setParameter('userId', $userId)
 310         ;
 311
 312         return $qb->getQuery()->getSingleScalarResult();
 313     }
 314
 315     public function setLifeTime($lifeTime)
 316     {
 317         $this->lifeTime = $lifeTime;
 318     }
 319
 320     /**
 321      * Enable cache for a query.
 322      *
 323      * @param Query $query
 324      *
 325      * @return Query
 326      */
 327     public function enableCache(Query $query)
 328     {
 329         $query->useQueryCache(true);
 330         $query->useResultCache(true);
 331         $query->setResultCacheLifetime($this->lifeTime);
 332
 333         return $query;
 334     }
 335 }