src/Wallabag/CoreBundle/Repository/EntryRepository.php

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