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