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      * Retrieve a sorted list of 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             ->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
 115     }
 116
 117     /**
 118      * Retrieve untagged entries for a user.
 119      *
 120      * @param int $userId
 121      *
 122      * @return QueryBuilder
 123      */
 124     public function getRawBuilderForUntaggedByUser($userId)
 125     {
 126         return $this->getQueryBuilderByUser($userId)
 127             ->leftJoin('e.tags', 't')
 128             ->andWhere('t.id is null');
 129     }
 130
 131     /**
 132      * Find Entries.
 133      *
 134      * @param int    $userId
 135      * @param bool   $isArchived
 136      * @param bool   $isStarred
 137      * @param bool   $isPublic
 138      * @param string $sort
 139      * @param string $order
 140      * @param int    $since
 141      * @param string $tags
 142      *
 143      * @return Pagerfanta
 144      */
 145     public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '')
 146     {
 147         $qb = $this->createQueryBuilder('e')
 148             ->leftJoin('e.tags', 't')
 149             ->where('e.user = :userId')->setParameter('userId', $userId);
 150
 151         if (null !== $isArchived) {
 152             $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
 153         }
 154
 155         if (null !== $isStarred) {
 156             $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
 157         }
 158
 159         if (null !== $isPublic) {
 160             $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
 161         }
 162
 163         if ($since > 0) {
 164             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 165         }
 166
 167         if (\is_string($tags) && '' !== $tags) {
 168             foreach (explode(',', $tags) as $i => $tag) {
 169                 $entryAlias = 'e' . $i;
 170                 $tagAlias = 't' . $i;
 171
 172                 // Complexe queries to ensure multiple tags are associated to an entry
 173                 // https://stackoverflow.com/a/6638146/569101
 174                 $qb->andWhere($qb->expr()->in(
 175                     'e.id',
 176                     $this->createQueryBuilder($entryAlias)
 177                         ->select($entryAlias . '.id')
 178                         ->leftJoin($entryAlias . '.tags', $tagAlias)
 179                         ->where($tagAlias . '.label = :label' . $i)
 180                         ->getDQL()
 181                 ));
 182
 183                 // bound parameter to the main query builder
 184                 $qb->setParameter('label' . $i, $tag);
 185             }
 186         }
 187
 188         if (!\in_array(strtolower($order), ['asc', 'desc'], true)) {
 189             throw new \Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
 190         }
 191
 192         if ('created' === $sort) {
 193             $qb->orderBy('e.id', $order);
 194         } elseif ('updated' === $sort) {
 195             $qb->orderBy('e.updatedAt', $order);
 196         }
 197
 198         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 199
 200         return new Pagerfanta($pagerAdapter);
 201     }
 202
 203     /**
 204      * Fetch an entry with a tag. Only used for tests.
 205      *
 206      * @param int $userId
 207      *
 208      * @return array
 209      */
 210     public function findOneWithTags($userId)
 211     {
 212         $qb = $this->createQueryBuilder('e')
 213             ->innerJoin('e.tags', 't')
 214             ->innerJoin('e.user', 'u')
 215             ->addSelect('t', 'u')
 216             ->where('e.user = :userId')->setParameter('userId', $userId)
 217         ;
 218
 219         return $qb->getQuery()->getResult();
 220     }
 221
 222     /**
 223      * Find distinct language for a given user.
 224      * Used to build the filter language list.
 225      *
 226      * @param int $userId User id
 227      *
 228      * @return array
 229      */
 230     public function findDistinctLanguageByUser($userId)
 231     {
 232         $results = $this->createQueryBuilder('e')
 233             ->select('e.language')
 234             ->where('e.user = :userId')->setParameter('userId', $userId)
 235             ->andWhere('e.language IS NOT NULL')
 236             ->groupBy('e.language')
 237             ->orderBy('e.language', ' ASC')
 238             ->getQuery()
 239             ->getResult();
 240
 241         $languages = [];
 242         foreach ($results as $result) {
 243             $languages[$result['language']] = $result['language'];
 244         }
 245
 246         return $languages;
 247     }
 248
 249     /**
 250      * Used only in test case to get the right entry associated to the right user.
 251      *
 252      * @param string $username
 253      *
 254      * @return Entry
 255      */
 256     public function findOneByUsernameAndNotArchived($username)
 257     {
 258         return $this->createQueryBuilder('e')
 259             ->leftJoin('e.user', 'u')
 260             ->where('u.username = :username')->setParameter('username', $username)
 261             ->andWhere('e.isArchived = false')
 262             ->setMaxResults(1)
 263             ->getQuery()
 264             ->getSingleResult();
 265     }
 266
 267     /**
 268      * Remove a tag from all user entries.
 269      *
 270      * 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.
 271      * 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:
 272      *
 273      * 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
 274      *
 275      * @param int $userId
 276      * @param Tag $tag
 277      */
 278     public function removeTag($userId, Tag $tag)
 279     {
 280         $entries = $this->getSortedQueryBuilderByUser($userId)
 281             ->innerJoin('e.tags', 't')
 282             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 283             ->getQuery()
 284             ->getResult();
 285
 286         foreach ($entries as $entry) {
 287             $entry->removeTag($tag);
 288         }
 289
 290         $this->getEntityManager()->flush();
 291     }
 292
 293     /**
 294      * Remove tags from all user entries.
 295      *
 296      * @param int        $userId
 297      * @param Array<Tag> $tags
 298      */
 299     public function removeTags($userId, $tags)
 300     {
 301         foreach ($tags as $tag) {
 302             $this->removeTag($userId, $tag);
 303         }
 304     }
 305
 306     /**
 307      * Find all entries that are attached to a give tag id.
 308      *
 309      * @param int $userId
 310      * @param int $tagId
 311      *
 312      * @return array
 313      */
 314     public function findAllByTagId($userId, $tagId)
 315     {
 316         return $this->getSortedQueryBuilderByUser($userId)
 317             ->innerJoin('e.tags', 't')
 318             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 319             ->getQuery()
 320             ->getResult();
 321     }
 322
 323     /**
 324      * Find an entry by its url and its owner.
 325      * If it exists, return the entry otherwise return false.
 326      *
 327      * @param $url
 328      * @param $userId
 329      *
 330      * @return Entry|bool
 331      */
 332     public function findByUrlAndUserId($url, $userId)
 333     {
 334         $res = $this->createQueryBuilder('e')
 335             ->where('e.url = :url')->setParameter('url', urldecode($url))
 336             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 337             ->getQuery()
 338             ->getResult();
 339
 340         if (\count($res)) {
 341             return current($res);
 342         }
 343
 344         return false;
 345     }
 346
 347     /**
 348      * Count all entries for a user.
 349      *
 350      * @param int $userId
 351      *
 352      * @return int
 353      */
 354     public function countAllEntriesByUser($userId)
 355     {
 356         $qb = $this->createQueryBuilder('e')
 357             ->select('count(e)')
 358             ->where('e.user = :userId')->setParameter('userId', $userId)
 359         ;
 360
 361         return (int) $qb->getQuery()->getSingleScalarResult();
 362     }
 363
 364     /**
 365      * Remove all entries for a user id.
 366      * Used when a user want to reset all informations.
 367      *
 368      * @param int $userId
 369      */
 370     public function removeAllByUserId($userId)
 371     {
 372         $this->getEntityManager()
 373             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
 374             ->setParameter('userId', $userId)
 375             ->execute();
 376     }
 377
 378     public function removeArchivedByUserId($userId)
 379     {
 380         $this->getEntityManager()
 381             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
 382             ->setParameter('userId', $userId)
 383             ->execute();
 384     }
 385
 386     /**
 387      * Get id and url from all entries
 388      * Used for the clean-duplicates command.
 389      */
 390     public function findAllEntriesIdAndUrlByUserId($userId)
 391     {
 392         $qb = $this->createQueryBuilder('e')
 393             ->select('e.id, e.url')
 394             ->where('e.user = :userid')->setParameter(':userid', $userId);
 395
 396         return $qb->getQuery()->getArrayResult();
 397     }
 398
 399     /**
 400      * @param int $userId
 401      *
 402      * @return array
 403      */
 404     public function findAllEntriesIdByUserId($userId = null)
 405     {
 406         $qb = $this->createQueryBuilder('e')
 407             ->select('e.id');
 408
 409         if (null !== $userId) {
 410             $qb->where('e.user = :userid')->setParameter(':userid', $userId);
 411         }
 412
 413         return $qb->getQuery()->getArrayResult();
 414     }
 415
 416     /**
 417      * Find all entries by url and owner.
 418      *
 419      * @param $url
 420      * @param $userId
 421      *
 422      * @return array
 423      */
 424     public function findAllByUrlAndUserId($url, $userId)
 425     {
 426         return $this->createQueryBuilder('e')
 427             ->where('e.url = :url')->setParameter('url', urldecode($url))
 428             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 429             ->getQuery()
 430             ->getResult();
 431     }
 432
 433     /**
 434      * Return a query builder to be used by other getBuilderFor* method.
 435      *
 436      * @param int $userId
 437      *
 438      * @return QueryBuilder
 439      */
 440     private function getQueryBuilderByUser($userId)
 441     {
 442         return $this->createQueryBuilder('e')
 443             ->andWhere('e.user = :userId')->setParameter('userId', $userId);
 444     }
 445
 446     /**
 447      * Return a sorted query builder to be used by other getBuilderFor* method.
 448      *
 449      * @param int    $userId
 450      * @param string $sortBy
 451      * @param string $direction
 452      *
 453      * @return QueryBuilder
 454      */
 455     private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
 456     {
 457         return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
 458     }
 459
 460     /**
 461      * Return the given QueryBuilder with an orderBy() call.
 462      *
 463      * @param QueryBuilder $qb
 464      * @param string       $sortBy
 465      * @param string       $direction
 466      *
 467      * @return QueryBuilder
 468      */
 469     private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
 470     {
 471         return $qb
 472             ->orderBy(sprintf('e.%s', $sortBy), $direction);
 473     }
 474 }