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