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 use Wallabag\CoreBundle\Helper\UrlHasher;
  13
  14 class EntryRepository extends EntityRepository
  15 {
  16     /**
  17      * Retrieves all entries for a user.
  18      *
  19      * @param int $userId
  20      *
  21      * @return QueryBuilder
  22      */
  23     public function getBuilderForAllByUser($userId)
  24     {
  25         return $this
  26             ->getSortedQueryBuilderByUser($userId)
  27         ;
  28     }
  29
  30     /**
  31      * Retrieves unread entries for a user.
  32      *
  33      * @param int $userId
  34      *
  35      * @return QueryBuilder
  36      */
  37     public function getBuilderForUnreadByUser($userId)
  38     {
  39         return $this
  40             ->getSortedQueryBuilderByUser($userId)
  41             ->andWhere('e.isArchived = false')
  42         ;
  43     }
  44
  45     /**
  46      * Retrieves read entries for a user.
  47      *
  48      * @param int $userId
  49      *
  50      * @return QueryBuilder
  51      */
  52     public function getBuilderForArchiveByUser($userId)
  53     {
  54         return $this
  55             ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
  56             ->andWhere('e.isArchived = true')
  57         ;
  58     }
  59
  60     /**
  61      * Retrieves starred entries for a user.
  62      *
  63      * @param int $userId
  64      *
  65      * @return QueryBuilder
  66      */
  67     public function getBuilderForStarredByUser($userId)
  68     {
  69         return $this
  70             ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
  71             ->andWhere('e.isStarred = true')
  72         ;
  73     }
  74
  75     /**
  76      * Retrieves entries filtered with a search term for a user.
  77      *
  78      * @param int    $userId
  79      * @param string $term
  80      * @param string $currentRoute
  81      *
  82      * @return QueryBuilder
  83      */
  84     public function getBuilderForSearchByUser($userId, $term, $currentRoute)
  85     {
  86         $qb = $this
  87             ->getSortedQueryBuilderByUser($userId);
  88
  89         if ('starred' === $currentRoute) {
  90             $qb->andWhere('e.isStarred = true');
  91         } elseif ('unread' === $currentRoute) {
  92             $qb->andWhere('e.isArchived = false');
  93         } elseif ('archive' === $currentRoute) {
  94             $qb->andWhere('e.isArchived = true');
  95         }
  96
  97         // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
  98         $qb
  99             ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
 100             ->leftJoin('e.tags', 't')
 101             ->groupBy('e.id');
 102
 103         return $qb;
 104     }
 105
 106     /**
 107      * Retrieve a sorted list of untagged entries for a user.
 108      *
 109      * @param int $userId
 110      *
 111      * @return QueryBuilder
 112      */
 113     public function getBuilderForUntaggedByUser($userId)
 114     {
 115         return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
 116     }
 117
 118     /**
 119      * Retrieve untagged entries for a user.
 120      *
 121      * @param int $userId
 122      *
 123      * @return QueryBuilder
 124      */
 125     public function getRawBuilderForUntaggedByUser($userId)
 126     {
 127         return $this->getQueryBuilderByUser($userId)
 128             ->leftJoin('e.tags', 't')
 129             ->andWhere('t.id is null');
 130     }
 131
 132     /**
 133      * Find Entries.
 134      *
 135      * @param int    $userId
 136      * @param bool   $isArchived
 137      * @param bool   $isStarred
 138      * @param bool   $isPublic
 139      * @param string $sort
 140      * @param string $order
 141      * @param int    $since
 142      * @param string $tags
 143      * @param string $detail     'metadata' or 'full'. Include content field if 'full'
 144      *
 145      * @todo Breaking change: replace default detail=full by detail=metadata in a future version
 146      *
 147      * @return Pagerfanta
 148      */
 149     public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '', $detail = 'full')
 150     {
 151         if (!\in_array(strtolower($detail), ['full', 'metadata'], true)) {
 152             throw new \Exception('Detail "' . $detail . '" parameter is wrong, allowed: full or metadata');
 153         }
 154
 155         $qb = $this->createQueryBuilder('e')
 156             ->leftJoin('e.tags', 't')
 157             ->where('e.user = :userId')->setParameter('userId', $userId);
 158
 159         if ('metadata' === $detail) {
 160             $fieldNames = $this->getClassMetadata()->getFieldNames();
 161             $fields = array_filter($fieldNames, function ($k) {
 162                 return 'content' !== $k;
 163             });
 164             $qb->select(sprintf('partial e.{%s}', implode(',', $fields)));
 165         }
 166
 167         if (null !== $isArchived) {
 168             $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
 169         }
 170
 171         if (null !== $isStarred) {
 172             $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
 173         }
 174
 175         if (null !== $isPublic) {
 176             $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
 177         }
 178
 179         if ($since > 0) {
 180             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 181         }
 182
 183         if (\is_string($tags) && '' !== $tags) {
 184             foreach (explode(',', $tags) as $i => $tag) {
 185                 $entryAlias = 'e' . $i;
 186                 $tagAlias = 't' . $i;
 187
 188                 // Complexe queries to ensure multiple tags are associated to an entry
 189                 // https://stackoverflow.com/a/6638146/569101
 190                 $qb->andWhere($qb->expr()->in(
 191                     'e.id',
 192                     $this->createQueryBuilder($entryAlias)
 193                         ->select($entryAlias . '.id')
 194                         ->leftJoin($entryAlias . '.tags', $tagAlias)
 195                         ->where($tagAlias . '.label = :label' . $i)
 196                         ->getDQL()
 197                 ));
 198
 199                 // bound parameter to the main query builder
 200                 $qb->setParameter('label' . $i, $tag);
 201             }
 202         }
 203
 204         if (!\in_array(strtolower($order), ['asc', 'desc'], true)) {
 205             throw new \Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
 206         }
 207
 208         if ('created' === $sort) {
 209             $qb->orderBy('e.id', $order);
 210         } elseif ('updated' === $sort) {
 211             $qb->orderBy('e.updatedAt', $order);
 212         } elseif ('archived' === $sort) {
 213             $qb->orderBy('e.archivedAt', $order);
 214         }
 215
 216         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 217
 218         return new Pagerfanta($pagerAdapter);
 219     }
 220
 221     /**
 222      * Fetch an entry with a tag. Only used for tests.
 223      *
 224      * @param int $userId
 225      *
 226      * @return array
 227      */
 228     public function findOneWithTags($userId)
 229     {
 230         $qb = $this->createQueryBuilder('e')
 231             ->innerJoin('e.tags', 't')
 232             ->innerJoin('e.user', 'u')
 233             ->addSelect('t', 'u')
 234             ->where('e.user = :userId')->setParameter('userId', $userId)
 235         ;
 236
 237         return $qb->getQuery()->getResult();
 238     }
 239
 240     /**
 241      * Find distinct language for a given user.
 242      * Used to build the filter language list.
 243      *
 244      * @param int $userId User id
 245      *
 246      * @return array
 247      */
 248     public function findDistinctLanguageByUser($userId)
 249     {
 250         $results = $this->createQueryBuilder('e')
 251             ->select('e.language')
 252             ->where('e.user = :userId')->setParameter('userId', $userId)
 253             ->andWhere('e.language IS NOT NULL')
 254             ->groupBy('e.language')
 255             ->orderBy('e.language', ' ASC')
 256             ->getQuery()
 257             ->getResult();
 258
 259         $languages = [];
 260         foreach ($results as $result) {
 261             $languages[$result['language']] = $result['language'];
 262         }
 263
 264         return $languages;
 265     }
 266
 267     /**
 268      * Used only in test case to get the right entry associated to the right user.
 269      *
 270      * @param string $username
 271      *
 272      * @return Entry
 273      */
 274     public function findOneByUsernameAndNotArchived($username)
 275     {
 276         return $this->createQueryBuilder('e')
 277             ->leftJoin('e.user', 'u')
 278             ->where('u.username = :username')->setParameter('username', $username)
 279             ->andWhere('e.isArchived = false')
 280             ->setMaxResults(1)
 281             ->getQuery()
 282             ->getSingleResult();
 283     }
 284
 285     /**
 286      * Remove a tag from all user entries.
 287      *
 288      * 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.
 289      * 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:
 290      *
 291      * 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
 292      *
 293      * @param int $userId
 294      * @param Tag $tag
 295      */
 296     public function removeTag($userId, Tag $tag)
 297     {
 298         $entries = $this->getSortedQueryBuilderByUser($userId)
 299             ->innerJoin('e.tags', 't')
 300             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 301             ->getQuery()
 302             ->getResult();
 303
 304         foreach ($entries as $entry) {
 305             $entry->removeTag($tag);
 306         }
 307
 308         $this->getEntityManager()->flush();
 309     }
 310
 311     /**
 312      * Remove tags from all user entries.
 313      *
 314      * @param int        $userId
 315      * @param Array<Tag> $tags
 316      */
 317     public function removeTags($userId, $tags)
 318     {
 319         foreach ($tags as $tag) {
 320             $this->removeTag($userId, $tag);
 321         }
 322     }
 323
 324     /**
 325      * Find all entries that are attached to a give tag id.
 326      *
 327      * @param int $userId
 328      * @param int $tagId
 329      *
 330      * @return array
 331      */
 332     public function findAllByTagId($userId, $tagId)
 333     {
 334         return $this->getSortedQueryBuilderByUser($userId)
 335             ->innerJoin('e.tags', 't')
 336             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 337             ->getQuery()
 338             ->getResult();
 339     }
 340
 341     /**
 342      * Find an entry by its url and its owner.
 343      * If it exists, return the entry otherwise return false.
 344      *
 345      * @param string $url
 346      * @param int    $userId
 347      *
 348      * @return Entry|bool
 349      */
 350     public function findByUrlAndUserId($url, $userId)
 351     {
 352         return $this->findByHashedUrlAndUserId(
 353             UrlHasher::hashUrl($url),
 354             $userId
 355         );
 356     }
 357
 358     /**
 359      * Find an entry by its hashed url and its owner.
 360      * If it exists, return the entry otherwise return false.
 361      *
 362      * @param string $hashedUrl Url hashed using sha1
 363      * @param int    $userId
 364      *
 365      * @return Entry|bool
 366      */
 367     public function findByHashedUrlAndUserId($hashedUrl, $userId)
 368     {
 369         $res = $this->createQueryBuilder('e')
 370             ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl)
 371             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 372             ->getQuery()
 373             ->getResult();
 374
 375         if (\count($res)) {
 376             return current($res);
 377         }
 378
 379         return false;
 380     }
 381
 382     /**
 383      * Count all entries for a user.
 384      *
 385      * @param int $userId
 386      *
 387      * @return int
 388      */
 389     public function countAllEntriesByUser($userId)
 390     {
 391         $qb = $this->createQueryBuilder('e')
 392             ->select('count(e)')
 393             ->where('e.user = :userId')->setParameter('userId', $userId)
 394         ;
 395
 396         return (int) $qb->getQuery()->getSingleScalarResult();
 397     }
 398
 399     /**
 400      * Remove all entries for a user id.
 401      * Used when a user want to reset all informations.
 402      *
 403      * @param int $userId
 404      */
 405     public function removeAllByUserId($userId)
 406     {
 407         $this->getEntityManager()
 408             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
 409             ->setParameter('userId', $userId)
 410             ->execute();
 411     }
 412
 413     public function removeArchivedByUserId($userId)
 414     {
 415         $this->getEntityManager()
 416             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
 417             ->setParameter('userId', $userId)
 418             ->execute();
 419     }
 420
 421     /**
 422      * Get id and url from all entries
 423      * Used for the clean-duplicates command.
 424      */
 425     public function findAllEntriesIdAndUrlByUserId($userId)
 426     {
 427         $qb = $this->createQueryBuilder('e')
 428             ->select('e.id, e.url')
 429             ->where('e.user = :userid')->setParameter(':userid', $userId);
 430
 431         return $qb->getQuery()->getArrayResult();
 432     }
 433
 434     /**
 435      * @param int $userId
 436      *
 437      * @return array
 438      */
 439     public function findAllEntriesIdByUserId($userId = null)
 440     {
 441         $qb = $this->createQueryBuilder('e')
 442             ->select('e.id');
 443
 444         if (null !== $userId) {
 445             $qb->where('e.user = :userid')->setParameter(':userid', $userId);
 446         }
 447
 448         return $qb->getQuery()->getArrayResult();
 449     }
 450
 451     /**
 452      * Find all entries by url and owner.
 453      *
 454      * @param string $url
 455      * @param int    $userId
 456      *
 457      * @return array
 458      */
 459     public function findAllByUrlAndUserId($url, $userId)
 460     {
 461         return $this->createQueryBuilder('e')
 462             ->where('e.url = :url')->setParameter('url', urldecode($url))
 463             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 464             ->getQuery()
 465             ->getResult();
 466     }
 467
 468     /**
 469      * Returns a random entry, filtering by status.
 470      *
 471      * @param int    $userId
 472      * @param string $type   Can be unread, archive, starred, etc
 473      *
 474      * @throws NoResultException
 475      *
 476      * @return Entry
 477      */
 478     public function getRandomEntry($userId, $type = '')
 479     {
 480         $qb = $this->getQueryBuilderByUser($userId)
 481             ->select('e.id');
 482
 483         switch ($type) {
 484             case 'unread':
 485                 $qb->andWhere('e.isArchived = false');
 486                 break;
 487             case 'archive':
 488                 $qb->andWhere('e.isArchived = true');
 489                 break;
 490             case 'starred':
 491                 $qb->andWhere('e.isStarred = true');
 492                 break;
 493             case 'untagged':
 494                 $qb->leftJoin('e.tags', 't');
 495                 $qb->andWhere('t.id is null');
 496                 break;
 497         }
 498
 499         $ids = $qb->getQuery()->getArrayResult();
 500
 501         if (empty($ids)) {
 502             throw new NoResultException();
 503         }
 504
 505         // random select one in the list
 506         $randomId = $ids[mt_rand(0, \count($ids) - 1)]['id'];
 507
 508         return $this->find($randomId);
 509     }
 510
 511     /**
 512      * Inject a UrlHasher.
 513      *
 514      * @param UrlHasher $hasher
 515      */
 516     public function setUrlHasher(UrlHasher $hasher)
 517     {
 518         $this->urlHasher = $hasher;
 519     }
 520
 521     /**
 522      * Get the UrlHasher, or create a default one if not injected.
 523      *
 524      * XXX: the default uses the default hash algorithm
 525      *
 526      * @return UrlHasher
 527      */
 528     protected function getUrlHasher()
 529     {
 530         if (!isset($this->urlHasher)) {
 531             $this->setUrlHasher(new UrlHasher());
 532         }
 533
 534         return $this->urlHasher;
 535     }
 536
 537     /**
 538      * Return a query builder to be used by other getBuilderFor* method.
 539      *
 540      * @param int $userId
 541      *
 542      * @return QueryBuilder
 543      */
 544     private function getQueryBuilderByUser($userId)
 545     {
 546         return $this->createQueryBuilder('e')
 547             ->andWhere('e.user = :userId')->setParameter('userId', $userId);
 548     }
 549
 550     /**
 551      * Return a sorted query builder to be used by other getBuilderFor* method.
 552      *
 553      * @param int    $userId
 554      * @param string $sortBy
 555      * @param string $direction
 556      *
 557      * @return QueryBuilder
 558      */
 559     private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
 560     {
 561         return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
 562     }
 563
 564     /**
 565      * Return the given QueryBuilder with an orderBy() call.
 566      *
 567      * @param QueryBuilder $qb
 568      * @param string       $sortBy
 569      * @param string       $direction
 570      *
 571      * @return QueryBuilder
 572      */
 573     private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
 574     {
 575         return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);
 576     }
 577 }