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