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