src/Wallabag/CoreBundle/Repository/EntryRepository.php

   1 <?php
   2
   3 namespace Wallabag\CoreBundle\Repository;
   4
   5 use Doctrine\ORM\EntityRepository;
   6 use Doctrine\ORM\NoResultException;
   7 use Doctrine\ORM\QueryBuilder;
   8 use Pagerfanta\Adapter\DoctrineORMAdapter;
   9 use Pagerfanta\Pagerfanta;
  10 use Wallabag\CoreBundle\Entity\Entry;
  11 use Wallabag\CoreBundle\Entity\Tag;
  12
  13 class EntryRepository extends EntityRepository
  14 {
  15     /**
  16      * Retrieves all entries for a user.
  17      *
  18      * @param int $userId
  19      *
  20      * @return QueryBuilder
  21      */
  22     public function getBuilderForAllByUser($userId)
  23     {
  24         return $this
  25             ->getSortedQueryBuilderByUser($userId)
  26         ;
  27     }
  28
  29     /**
  30      * Retrieves unread entries for a user.
  31      *
  32      * @param int $userId
  33      *
  34      * @return QueryBuilder
  35      */
  36     public function getBuilderForUnreadByUser($userId)
  37     {
  38         return $this
  39             ->getSortedQueryBuilderByUser($userId)
  40             ->andWhere('e.isArchived = false')
  41         ;
  42     }
  43
  44     /**
  45      * Retrieves read entries for a user.
  46      *
  47      * @param int $userId
  48      *
  49      * @return QueryBuilder
  50      */
  51     public function getBuilderForArchiveByUser($userId)
  52     {
  53         return $this
  54             ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
  55             ->andWhere('e.isArchived = true')
  56         ;
  57     }
  58
  59     /**
  60      * Retrieves starred entries for a user.
  61      *
  62      * @param int $userId
  63      *
  64      * @return QueryBuilder
  65      */
  66     public function getBuilderForStarredByUser($userId)
  67     {
  68         return $this
  69             ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
  70             ->andWhere('e.isStarred = true')
  71         ;
  72     }
  73
  74     /**
  75      * Retrieves entries filtered with a search term for a user.
  76      *
  77      * @param int    $userId
  78      * @param string $term
  79      * @param string $currentRoute
  80      *
  81      * @return QueryBuilder
  82      */
  83     public function getBuilderForSearchByUser($userId, $term, $currentRoute)
  84     {
  85         $qb = $this
  86             ->getSortedQueryBuilderByUser($userId);
  87
  88         if ('starred' === $currentRoute) {
  89             $qb->andWhere('e.isStarred = true');
  90         } elseif ('unread' === $currentRoute) {
  91             $qb->andWhere('e.isArchived = false');
  92         } elseif ('archive' === $currentRoute) {
  93             $qb->andWhere('e.isArchived = true');
  94         }
  95
  96         // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
  97         $qb
  98             ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
  99             ->leftJoin('e.tags', 't')
 100             ->groupBy('e.id');
 101
 102         return $qb;
 103     }
 104
 105     /**
 106      * Retrieve a sorted list of untagged entries for a user.
 107      *
 108      * @param int $userId
 109      *
 110      * @return QueryBuilder
 111      */
 112     public function getBuilderForUntaggedByUser($userId)
 113     {
 114         return $this->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         } elseif ('archived' === $sort) {
 197             $qb->orderBy('e.archivedAt', $order);
 198         }
 199
 200         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 201
 202         return new Pagerfanta($pagerAdapter);
 203     }
 204
 205     /**
 206      * Fetch an entry with a tag. Only used for tests.
 207      *
 208      * @param int $userId
 209      *
 210      * @return array
 211      */
 212     public function findOneWithTags($userId)
 213     {
 214         $qb = $this->createQueryBuilder('e')
 215             ->innerJoin('e.tags', 't')
 216             ->innerJoin('e.user', 'u')
 217             ->addSelect('t', 'u')
 218             ->where('e.user = :userId')->setParameter('userId', $userId)
 219         ;
 220
 221         return $qb->getQuery()->getResult();
 222     }
 223
 224     /**
 225      * Find distinct language for a given user.
 226      * Used to build the filter language list.
 227      *
 228      * @param int $userId User id
 229      *
 230      * @return array
 231      */
 232     public function findDistinctLanguageByUser($userId)
 233     {
 234         $results = $this->createQueryBuilder('e')
 235             ->select('e.language')
 236             ->where('e.user = :userId')->setParameter('userId', $userId)
 237             ->andWhere('e.language IS NOT NULL')
 238             ->groupBy('e.language')
 239             ->orderBy('e.language', ' ASC')
 240             ->getQuery()
 241             ->getResult();
 242
 243         $languages = [];
 244         foreach ($results as $result) {
 245             $languages[$result['language']] = $result['language'];
 246         }
 247
 248         return $languages;
 249     }
 250
 251     /**
 252      * Used only in test case to get the right entry associated to the right user.
 253      *
 254      * @param string $username
 255      *
 256      * @return Entry
 257      */
 258     public function findOneByUsernameAndNotArchived($username)
 259     {
 260         return $this->createQueryBuilder('e')
 261             ->leftJoin('e.user', 'u')
 262             ->where('u.username = :username')->setParameter('username', $username)
 263             ->andWhere('e.isArchived = false')
 264             ->setMaxResults(1)
 265             ->getQuery()
 266             ->getSingleResult();
 267     }
 268
 269     /**
 270      * Remove a tag from all user entries.
 271      *
 272      * 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.
 273      * 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:
 274      *
 275      * 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
 276      *
 277      * @param int $userId
 278      * @param Tag $tag
 279      */
 280     public function removeTag($userId, Tag $tag)
 281     {
 282         $entries = $this->getSortedQueryBuilderByUser($userId)
 283             ->innerJoin('e.tags', 't')
 284             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 285             ->getQuery()
 286             ->getResult();
 287
 288         foreach ($entries as $entry) {
 289             $entry->removeTag($tag);
 290         }
 291
 292         $this->getEntityManager()->flush();
 293     }
 294
 295     /**
 296      * Remove tags from all user entries.
 297      *
 298      * @param int        $userId
 299      * @param Array<Tag> $tags
 300      */
 301     public function removeTags($userId, $tags)
 302     {
 303         foreach ($tags as $tag) {
 304             $this->removeTag($userId, $tag);
 305         }
 306     }
 307
 308     /**
 309      * Find all entries that are attached to a give tag id.
 310      *
 311      * @param int $userId
 312      * @param int $tagId
 313      *
 314      * @return array
 315      */
 316     public function findAllByTagId($userId, $tagId)
 317     {
 318         return $this->getSortedQueryBuilderByUser($userId)
 319             ->innerJoin('e.tags', 't')
 320             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 321             ->getQuery()
 322             ->getResult();
 323     }
 324
 325     /**
 326      * Find an entry by its url and its owner.
 327      * If it exists, return the entry otherwise return false.
 328      *
 329      * @param string $url
 330      * @param int    $userId
 331      *
 332      * @return Entry|bool
 333      */
 334     public function findByUrlAndUserId($url, $userId)
 335     {
 336         $res = $this->createQueryBuilder('e')
 337             ->where('e.url = :url')->setParameter('url', urldecode($url))
 338             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 339             ->getQuery()
 340             ->getResult();
 341
 342         if (\count($res)) {
 343             return current($res);
 344         }
 345
 346         return false;
 347     }
 348
 349     /**
 350      * Count all entries for a user.
 351      *
 352      * @param int $userId
 353      *
 354      * @return int
 355      */
 356     public function countAllEntriesByUser($userId)
 357     {
 358         $qb = $this->createQueryBuilder('e')
 359             ->select('count(e)')
 360             ->where('e.user = :userId')->setParameter('userId', $userId)
 361         ;
 362
 363         return (int) $qb->getQuery()->getSingleScalarResult();
 364     }
 365
 366     /**
 367      * Remove all entries for a user id.
 368      * Used when a user want to reset all informations.
 369      *
 370      * @param int $userId
 371      */
 372     public function removeAllByUserId($userId)
 373     {
 374         $this->getEntityManager()
 375             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
 376             ->setParameter('userId', $userId)
 377             ->execute();
 378     }
 379
 380     public function removeArchivedByUserId($userId)
 381     {
 382         $this->getEntityManager()
 383             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
 384             ->setParameter('userId', $userId)
 385             ->execute();
 386     }
 387
 388     /**
 389      * Get id and url from all entries
 390      * Used for the clean-duplicates command.
 391      */
 392     public function findAllEntriesIdAndUrlByUserId($userId)
 393     {
 394         $qb = $this->createQueryBuilder('e')
 395             ->select('e.id, e.url')
 396             ->where('e.user = :userid')->setParameter(':userid', $userId);
 397
 398         return $qb->getQuery()->getArrayResult();
 399     }
 400
 401     /**
 402      * @param int $userId
 403      *
 404      * @return array
 405      */
 406     public function findAllEntriesIdByUserId($userId = null)
 407     {
 408         $qb = $this->createQueryBuilder('e')
 409             ->select('e.id');
 410
 411         if (null !== $userId) {
 412             $qb->where('e.user = :userid')->setParameter(':userid', $userId);
 413         }
 414
 415         return $qb->getQuery()->getArrayResult();
 416     }
 417
 418     /**
 419      * Find all entries by url and owner.
 420      *
 421      * @param string $url
 422      * @param int    $userId
 423      *
 424      * @return array
 425      */
 426     public function findAllByUrlAndUserId($url, $userId)
 427     {
 428         return $this->createQueryBuilder('e')
 429             ->where('e.url = :url')->setParameter('url', urldecode($url))
 430             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 431             ->getQuery()
 432             ->getResult();
 433     }
 434
 435     /**
 436      * Returns a random entry, filtering by status.
 437      *
 438      * @param int    $userId
 439      * @param string $type   Can be unread, archive, starred, etc
 440      *
 441      * @throws NoResultException
 442      *
 443      * @return Entry
 444      */
 445     public function getRandomEntry($userId, $type = '')
 446     {
 447         $qb = $this->getQueryBuilderByUser($userId)
 448             ->select('e.id');
 449
 450         switch ($type) {
 451             case 'unread':
 452                 $qb->andWhere('e.isArchived = false');
 453                 break;
 454             case 'archive':
 455                 $qb->andWhere('e.isArchived = true');
 456                 break;
 457             case 'starred':
 458                 $qb->andWhere('e.isStarred = true');
 459                 break;
 460             case 'untagged':
 461                 $qb->leftJoin('e.tags', 't');
 462                 $qb->andWhere('t.id is null');
 463                 break;
 464         }
 465
 466         $ids = $qb->getQuery()->getArrayResult();
 467
 468         if (empty($ids)) {
 469             throw new NoResultException();
 470         }
 471
 472         // random select one in the list
 473         $randomId = $ids[mt_rand(0, \count($ids) - 1)]['id'];
 474
 475         return $this->find($randomId);
 476     }
 477
 478     /**
 479      * Return a query builder to be used by other getBuilderFor* method.
 480      *
 481      * @param int $userId
 482      *
 483      * @return QueryBuilder
 484      */
 485     private function getQueryBuilderByUser($userId)
 486     {
 487         return $this->createQueryBuilder('e')
 488             ->andWhere('e.user = :userId')->setParameter('userId', $userId);
 489     }
 490
 491     /**
 492      * Return a sorted query builder to be used by other getBuilderFor* method.
 493      *
 494      * @param int    $userId
 495      * @param string $sortBy
 496      * @param string $direction
 497      *
 498      * @return QueryBuilder
 499      */
 500     private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
 501     {
 502         return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
 503     }
 504
 505     /**
 506      * Return the given QueryBuilder with an orderBy() call.
 507      *
 508      * @param QueryBuilder $qb
 509      * @param string       $sortBy
 510      * @param string       $direction
 511      *
 512      * @return QueryBuilder
 513      */
 514     private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
 515     {
 516         return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);
 517     }
 518 }