src/Wallabag/CoreBundle/Repository/EntryRepository.php

   1 <?php
   2
   3 namespace Wallabag\CoreBundle\Repository;
   4
   5 use Doctrine\ORM\EntityRepository;
   6 use Doctrine\ORM\QueryBuilder;
   7 use Pagerfanta\Adapter\DoctrineORMAdapter;
   8 use Pagerfanta\Pagerfanta;
   9 use Wallabag\CoreBundle\Entity\Entry;
  10 use Wallabag\CoreBundle\Entity\Tag;
  11
  12 class EntryRepository extends EntityRepository
  13 {
  14     /**
  15      * Retrieves all entries for a user.
  16      *
  17      * @param int $userId
  18      *
  19      * @return QueryBuilder
  20      */
  21     public function getBuilderForAllByUser($userId)
  22     {
  23         return $this
  24             ->getSortedQueryBuilderByUser($userId)
  25         ;
  26     }
  27
  28     /**
  29      * Retrieves unread entries for a user.
  30      *
  31      * @param int $userId
  32      *
  33      * @return QueryBuilder
  34      */
  35     public function getBuilderForUnreadByUser($userId)
  36     {
  37         return $this
  38             ->getSortedQueryBuilderByUser($userId)
  39             ->andWhere('e.isArchived = false')
  40         ;
  41     }
  42
  43     /**
  44      * Retrieves read entries for a user.
  45      *
  46      * @param int $userId
  47      *
  48      * @return QueryBuilder
  49      */
  50     public function getBuilderForArchiveByUser($userId)
  51     {
  52         return $this
  53             ->getSortedQueryBuilderByUser($userId)
  54             ->andWhere('e.isArchived = true')
  55         ;
  56     }
  57
  58     /**
  59      * Retrieves starred entries for a user.
  60      *
  61      * @param int $userId
  62      *
  63      * @return QueryBuilder
  64      */
  65     public function getBuilderForStarredByUser($userId)
  66     {
  67         return $this
  68             ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
  69             ->andWhere('e.isStarred = true')
  70         ;
  71     }
  72
  73     /**
  74      * Retrieves entries filtered with a search term for a user.
  75      *
  76      * @param int    $userId
  77      * @param string $term
  78      * @param string $currentRoute
  79      *
  80      * @return QueryBuilder
  81      */
  82     public function getBuilderForSearchByUser($userId, $term, $currentRoute)
  83     {
  84         $qb = $this
  85             ->getSortedQueryBuilderByUser($userId);
  86
  87         if ('starred' === $currentRoute) {
  88             $qb->andWhere('e.isStarred = true');
  89         } elseif ('unread' === $currentRoute) {
  90             $qb->andWhere('e.isArchived = false');
  91         } elseif ('archive' === $currentRoute) {
  92             $qb->andWhere('e.isArchived = true');
  93         }
  94
  95         // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
  96         $qb
  97             ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
  98             ->leftJoin('e.tags', 't')
  99             ->groupBy('e.id');
 100
 101         return $qb;
 102     }
 103
 104     /**
 105      * Retrieves untagged entries for a user.
 106      *
 107      * @param int $userId
 108      *
 109      * @return QueryBuilder
 110      */
 111     public function getBuilderForUntaggedByUser($userId)
 112     {
 113         return $this
 114             ->getSortedQueryBuilderByUser($userId)
 115             ->andWhere('size(e.tags) = 0');
 116     }
 117
 118     /**
 119      * Find Entries.
 120      *
 121      * @param int    $userId
 122      * @param bool   $isArchived
 123      * @param bool   $isStarred
 124      * @param bool   $isPublic
 125      * @param string $sort
 126      * @param string $order
 127      * @param int    $since
 128      * @param string $tags
 129      *
 130      * @return Pagerfanta
 131      */
 132     public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
 133     {
 134         $qb = $this->createQueryBuilder('e')
 135             ->leftJoin('e.tags', 't')
 136             ->where('e.user = :userId')->setParameter('userId', $userId);
 137
 138         if (null !== $isArchived) {
 139             $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
 140         }
 141
 142         if (null !== $isStarred) {
 143             $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
 144         }
 145
 146         if (null !== $isPublic) {
 147             $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
 148         }
 149
 150         if ($since > 0) {
 151             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 152         }
 153
 154         if (\is_string($tags) && '' !== $tags) {
 155             foreach (explode(',', $tags) as $i => $tag) {
 156                 $entryAlias = 'e' . $i;
 157                 $tagAlias = 't' . $i;
 158
 159                 // Complexe queries to ensure multiple tags are associated to an entry
 160                 // https://stackoverflow.com/a/6638146/569101
 161                 $qb->andWhere($qb->expr()->in(
 162                     'e.id',
 163                     $this->createQueryBuilder($entryAlias)
 164                         ->select($entryAlias . '.id')
 165                         ->leftJoin($entryAlias . '.tags', $tagAlias)
 166                         ->where($tagAlias . '.label = :label' . $i)
 167                         ->getDQL()
 168                 ));
 169
 170                 // bound parameter to the main query builder
 171                 $qb->setParameter('label' . $i, $tag);
 172             }
 173         }
 174
 175         if ('created' === $sort) {
 176             $qb->orderBy('e.id', $order);
 177         } elseif ('updated' === $sort) {
 178             $qb->orderBy('e.updatedAt', $order);
 179         }
 180
 181         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 182
 183         return new Pagerfanta($pagerAdapter);
 184     }
 185
 186     /**
 187      * Fetch an entry with a tag. Only used for tests.
 188      *
 189      * @param int $userId
 190      *
 191      * @return array
 192      */
 193     public function findOneWithTags($userId)
 194     {
 195         $qb = $this->createQueryBuilder('e')
 196             ->innerJoin('e.tags', 't')
 197             ->innerJoin('e.user', 'u')
 198             ->addSelect('t', 'u')
 199             ->where('e.user = :userId')->setParameter('userId', $userId)
 200         ;
 201
 202         return $qb->getQuery()->getResult();
 203     }
 204
 205     /**
 206      * Find distinct language for a given user.
 207      * Used to build the filter language list.
 208      *
 209      * @param int $userId User id
 210      *
 211      * @return array
 212      */
 213     public function findDistinctLanguageByUser($userId)
 214     {
 215         $results = $this->createQueryBuilder('e')
 216             ->select('e.language')
 217             ->where('e.user = :userId')->setParameter('userId', $userId)
 218             ->andWhere('e.language IS NOT NULL')
 219             ->groupBy('e.language')
 220             ->orderBy('e.language', ' ASC')
 221             ->getQuery()
 222             ->getResult();
 223
 224         $languages = [];
 225         foreach ($results as $result) {
 226             $languages[$result['language']] = $result['language'];
 227         }
 228
 229         return $languages;
 230     }
 231
 232     /**
 233      * Used only in test case to get the right entry associated to the right user.
 234      *
 235      * @param string $username
 236      *
 237      * @return Entry
 238      */
 239     public function findOneByUsernameAndNotArchived($username)
 240     {
 241         return $this->createQueryBuilder('e')
 242             ->leftJoin('e.user', 'u')
 243             ->where('u.username = :username')->setParameter('username', $username)
 244             ->andWhere('e.isArchived = false')
 245             ->setMaxResults(1)
 246             ->getQuery()
 247             ->getSingleResult();
 248     }
 249
 250     /**
 251      * Remove a tag from all user entries.
 252      *
 253      * 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.
 254      * 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:
 255      *
 256      * 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
 257      *
 258      * @param int $userId
 259      * @param Tag $tag
 260      */
 261     public function removeTag($userId, Tag $tag)
 262     {
 263         $entries = $this->getSortedQueryBuilderByUser($userId)
 264             ->innerJoin('e.tags', 't')
 265             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 266             ->getQuery()
 267             ->getResult();
 268
 269         foreach ($entries as $entry) {
 270             $entry->removeTag($tag);
 271         }
 272
 273         $this->getEntityManager()->flush();
 274     }
 275
 276     /**
 277      * Remove tags from all user entries.
 278      *
 279      * @param int        $userId
 280      * @param Array<Tag> $tags
 281      */
 282     public function removeTags($userId, $tags)
 283     {
 284         foreach ($tags as $tag) {
 285             $this->removeTag($userId, $tag);
 286         }
 287     }
 288
 289     /**
 290      * Find all entries that are attached to a give tag id.
 291      *
 292      * @param int $userId
 293      * @param int $tagId
 294      *
 295      * @return array
 296      */
 297     public function findAllByTagId($userId, $tagId)
 298     {
 299         return $this->getSortedQueryBuilderByUser($userId)
 300             ->innerJoin('e.tags', 't')
 301             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 302             ->getQuery()
 303             ->getResult();
 304     }
 305
 306     /**
 307      * Find an entry by its url and its owner.
 308      * If it exists, return the entry otherwise return false.
 309      *
 310      * @param $url
 311      * @param $userId
 312      *
 313      * @return Entry|bool
 314      */
 315     public function findByUrlAndUserId($url, $userId)
 316     {
 317         $res = $this->createQueryBuilder('e')
 318             ->where('e.url = :url')->setParameter('url', urldecode($url))
 319             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 320             ->getQuery()
 321             ->getResult();
 322
 323         if (\count($res)) {
 324             return current($res);
 325         }
 326
 327         return false;
 328     }
 329
 330     /**
 331      * Count all entries for a user.
 332      *
 333      * @param int $userId
 334      *
 335      * @return int
 336      */
 337     public function countAllEntriesByUser($userId)
 338     {
 339         $qb = $this->createQueryBuilder('e')
 340             ->select('count(e)')
 341             ->where('e.user = :userId')->setParameter('userId', $userId)
 342         ;
 343
 344         return (int) $qb->getQuery()->getSingleScalarResult();
 345     }
 346
 347     /**
 348      * Remove all entries for a user id.
 349      * Used when a user want to reset all informations.
 350      *
 351      * @param int $userId
 352      */
 353     public function removeAllByUserId($userId)
 354     {
 355         $this->getEntityManager()
 356             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
 357             ->setParameter('userId', $userId)
 358             ->execute();
 359     }
 360
 361     public function removeArchivedByUserId($userId)
 362     {
 363         $this->getEntityManager()
 364             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
 365             ->setParameter('userId', $userId)
 366             ->execute();
 367     }
 368
 369     /**
 370      * Get id and url from all entries
 371      * Used for the clean-duplicates command.
 372      */
 373     public function findAllEntriesIdAndUrlByUserId($userId)
 374     {
 375         $qb = $this->createQueryBuilder('e')
 376             ->select('e.id, e.url')
 377             ->where('e.user = :userid')->setParameter(':userid', $userId);
 378
 379         return $qb->getQuery()->getArrayResult();
 380     }
 381
 382     /**
 383      * @param int $userId
 384      *
 385      * @return array
 386      */
 387     public function findAllEntriesIdByUserId($userId = null)
 388     {
 389         $qb = $this->createQueryBuilder('e')
 390             ->select('e.id');
 391
 392         if (null !== $userId) {
 393             $qb->where('e.user = :userid')->setParameter(':userid', $userId);
 394         }
 395
 396         return $qb->getQuery()->getArrayResult();
 397     }
 398
 399     /**
 400      * Find all entries by url and owner.
 401      *
 402      * @param $url
 403      * @param $userId
 404      *
 405      * @return array
 406      */
 407     public function findAllByUrlAndUserId($url, $userId)
 408     {
 409         return $this->createQueryBuilder('e')
 410             ->where('e.url = :url')->setParameter('url', urldecode($url))
 411             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 412             ->getQuery()
 413             ->getResult();
 414     }
 415
 416     /**
 417      * Return a query builder to be used by other getBuilderFor* method.
 418      *
 419      * @param int    $userId
 420      *
 421      * @return QueryBuilder
 422      */
 423     private function getQueryBuilderByUser($userId)
 424     {
 425         return $this->createQueryBuilder('e')
 426             ->andWhere('e.user = :userId')->setParameter('userId', $userId);
 427     }
 428
 429     /**
 430      * Return a sorted query builder to be used by other getBuilderFor* method.
 431      *
 432      * @param int    $userId
 433      * @param string $sortBy
 434      * @param string $direction
 435      *
 436      * @return QueryBuilder
 437      */
 438     private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
 439     {
 440         return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId));
 441     }
 442
 443     /**
 444      * Return the given QueryBuilder with an orderBy() call
 445      *
 446      * @param QueryBuilder $qb
 447      * @param string $sortBy
 448      * @param string $direction
 449      *
 450      * @return QueryBuilder
 451      */
 452     private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
 453     {
 454         return $qb
 455             ->orderBy(sprintf('e.%s', $sortBy), $direction);
 456     }
 457 }