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