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      * @param string $detail     'metadata' or 'full'. Include content field if 'full'
 143      *
 144      * @todo Breaking change: replace default detail=full by detail=metadata in a future version
 145      *
 146      * @return Pagerfanta
 147      */
 148     public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '', $detail = 'full')
 149     {
 150         if (!\in_array(strtolower($detail), ['full', 'metadata'], true)) {
 151             throw new \Exception('Detail "' . $detail . '" parameter is wrong, allowed: full or metadata');
 152         }
 153
 154         $qb = $this->createQueryBuilder('e')
 155             ->leftJoin('e.tags', 't')
 156             ->where('e.user = :userId')->setParameter('userId', $userId);
 157
 158         if ('metadata' === $detail) {
 159             $fieldNames = $this->getClassMetadata()->getFieldNames();
 160             $fields = array_filter($fieldNames, function ($k) {
 161                 return 'content' !== $k;
 162             });
 163             $qb->select(sprintf('partial e.{%s}', implode(',', $fields)));
 164         }
 165
 166         if (null !== $isArchived) {
 167             $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
 168         }
 169
 170         if (null !== $isStarred) {
 171             $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
 172         }
 173
 174         if (null !== $isPublic) {
 175             $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
 176         }
 177
 178         if ($since > 0) {
 179             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 180         }
 181
 182         if (\is_string($tags) && '' !== $tags) {
 183             foreach (explode(',', $tags) as $i => $tag) {
 184                 $entryAlias = 'e' . $i;
 185                 $tagAlias = 't' . $i;
 186
 187                 // Complexe queries to ensure multiple tags are associated to an entry
 188                 // https://stackoverflow.com/a/6638146/569101
 189                 $qb->andWhere($qb->expr()->in(
 190                     'e.id',
 191                     $this->createQueryBuilder($entryAlias)
 192                         ->select($entryAlias . '.id')
 193                         ->leftJoin($entryAlias . '.tags', $tagAlias)
 194                         ->where($tagAlias . '.label = :label' . $i)
 195                         ->getDQL()
 196                 ));
 197
 198                 // bound parameter to the main query builder
 199                 $qb->setParameter('label' . $i, $tag);
 200             }
 201         }
 202
 203         if (!\in_array(strtolower($order), ['asc', 'desc'], true)) {
 204             throw new \Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
 205         }
 206
 207         if ('created' === $sort) {
 208             $qb->orderBy('e.id', $order);
 209         } elseif ('updated' === $sort) {
 210             $qb->orderBy('e.updatedAt', $order);
 211         } elseif ('archived' === $sort) {
 212             $qb->orderBy('e.archivedAt', $order);
 213         }
 214
 215         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 216
 217         return new Pagerfanta($pagerAdapter);
 218     }
 219
 220     /**
 221      * Fetch an entry with a tag. Only used for tests.
 222      *
 223      * @param int $userId
 224      *
 225      * @return array
 226      */
 227     public function findOneWithTags($userId)
 228     {
 229         $qb = $this->createQueryBuilder('e')
 230             ->innerJoin('e.tags', 't')
 231             ->innerJoin('e.user', 'u')
 232             ->addSelect('t', 'u')
 233             ->where('e.user = :userId')->setParameter('userId', $userId)
 234         ;
 235
 236         return $qb->getQuery()->getResult();
 237     }
 238
 239     /**
 240      * Find distinct language for a given user.
 241      * Used to build the filter language list.
 242      *
 243      * @param int $userId User id
 244      *
 245      * @return array
 246      */
 247     public function findDistinctLanguageByUser($userId)
 248     {
 249         $results = $this->createQueryBuilder('e')
 250             ->select('e.language')
 251             ->where('e.user = :userId')->setParameter('userId', $userId)
 252             ->andWhere('e.language IS NOT NULL')
 253             ->groupBy('e.language')
 254             ->orderBy('e.language', ' ASC')
 255             ->getQuery()
 256             ->getResult();
 257
 258         $languages = [];
 259         foreach ($results as $result) {
 260             $languages[$result['language']] = $result['language'];
 261         }
 262
 263         return $languages;
 264     }
 265
 266     /**
 267      * Used only in test case to get the right entry associated to the right user.
 268      *
 269      * @param string $username
 270      *
 271      * @return Entry
 272      */
 273     public function findOneByUsernameAndNotArchived($username)
 274     {
 275         return $this->createQueryBuilder('e')
 276             ->leftJoin('e.user', 'u')
 277             ->where('u.username = :username')->setParameter('username', $username)
 278             ->andWhere('e.isArchived = false')
 279             ->setMaxResults(1)
 280             ->getQuery()
 281             ->getSingleResult();
 282     }
 283
 284     /**
 285      * Remove a tag from all user entries.
 286      *
 287      * 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.
 288      * 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:
 289      *
 290      * 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
 291      *
 292      * @param int $userId
 293      * @param Tag $tag
 294      */
 295     public function removeTag($userId, Tag $tag)
 296     {
 297         $entries = $this->getSortedQueryBuilderByUser($userId)
 298             ->innerJoin('e.tags', 't')
 299             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 300             ->getQuery()
 301             ->getResult();
 302
 303         foreach ($entries as $entry) {
 304             $entry->removeTag($tag);
 305         }
 306
 307         $this->getEntityManager()->flush();
 308     }
 309
 310     /**
 311      * Remove tags from all user entries.
 312      *
 313      * @param int        $userId
 314      * @param Array<Tag> $tags
 315      */
 316     public function removeTags($userId, $tags)
 317     {
 318         foreach ($tags as $tag) {
 319             $this->removeTag($userId, $tag);
 320         }
 321     }
 322
 323     /**
 324      * Find all entries that are attached to a give tag id.
 325      *
 326      * @param int $userId
 327      * @param int $tagId
 328      *
 329      * @return array
 330      */
 331     public function findAllByTagId($userId, $tagId)
 332     {
 333         return $this->getSortedQueryBuilderByUser($userId)
 334             ->innerJoin('e.tags', 't')
 335             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 336             ->getQuery()
 337             ->getResult();
 338     }
 339
 340     /**
 341      * Find an entry by its url and its owner.
 342      * If it exists, return the entry otherwise return false.
 343      *
 344      * @param string $url
 345      * @param int    $userId
 346      *
 347      * @return Entry|bool
 348      */
 349     public function findByUrlAndUserId($url, $userId)
 350     {
 351         $res = $this->createQueryBuilder('e')
 352             ->where('e.url = :url')->setParameter('url', urldecode($url))
 353             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 354             ->getQuery()
 355             ->getResult();
 356
 357         if (\count($res)) {
 358             return current($res);
 359         }
 360
 361         return false;
 362     }
 363
 364     /**
 365      * Find an entry by its hashed url and its owner.
 366      * If it exists, return the entry otherwise return false.
 367      *
 368      * @param string $hashedUrl Url hashed using sha1
 369      * @param int    $userId
 370      *
 371      * @return Entry|bool
 372      */
 373     public function findByHashedUrlAndUserId($hashedUrl, $userId)
 374     {
 375         $res = $this->createQueryBuilder('e')
 376             ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl)
 377             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 378             ->getQuery()
 379             ->getResult();
 380
 381         if (\count($res)) {
 382             return current($res);
 383         }
 384
 385         return false;
 386     }
 387
 388     /**
 389      * Count all entries for a user.
 390      *
 391      * @param int $userId
 392      *
 393      * @return int
 394      */
 395     public function countAllEntriesByUser($userId)
 396     {
 397         $qb = $this->createQueryBuilder('e')
 398             ->select('count(e)')
 399             ->where('e.user = :userId')->setParameter('userId', $userId)
 400         ;
 401
 402         return (int) $qb->getQuery()->getSingleScalarResult();
 403     }
 404
 405     /**
 406      * Remove all entries for a user id.
 407      * Used when a user want to reset all informations.
 408      *
 409      * @param int $userId
 410      */
 411     public function removeAllByUserId($userId)
 412     {
 413         $this->getEntityManager()
 414             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
 415             ->setParameter('userId', $userId)
 416             ->execute();
 417     }
 418
 419     public function removeArchivedByUserId($userId)
 420     {
 421         $this->getEntityManager()
 422             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
 423             ->setParameter('userId', $userId)
 424             ->execute();
 425     }
 426
 427     /**
 428      * Get id and url from all entries
 429      * Used for the clean-duplicates command.
 430      */
 431     public function findAllEntriesIdAndUrlByUserId($userId)
 432     {
 433         $qb = $this->createQueryBuilder('e')
 434             ->select('e.id, e.url')
 435             ->where('e.user = :userid')->setParameter(':userid', $userId);
 436
 437         return $qb->getQuery()->getArrayResult();
 438     }
 439
 440     /**
 441      * @param int $userId
 442      *
 443      * @return array
 444      */
 445     public function findAllEntriesIdByUserId($userId = null)
 446     {
 447         $qb = $this->createQueryBuilder('e')
 448             ->select('e.id');
 449
 450         if (null !== $userId) {
 451             $qb->where('e.user = :userid')->setParameter(':userid', $userId);
 452         }
 453
 454         return $qb->getQuery()->getArrayResult();
 455     }
 456
 457     /**
 458      * Find all entries by url and owner.
 459      *
 460      * @param string $url
 461      * @param int    $userId
 462      *
 463      * @return array
 464      */
 465     public function findAllByUrlAndUserId($url, $userId)
 466     {
 467         return $this->createQueryBuilder('e')
 468             ->where('e.url = :url')->setParameter('url', urldecode($url))
 469             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 470             ->getQuery()
 471             ->getResult();
 472     }
 473
 474     /**
 475      * Returns a random entry, filtering by status.
 476      *
 477      * @param int    $userId
 478      * @param string $type   Can be unread, archive, starred, etc
 479      *
 480      * @throws NoResultException
 481      *
 482      * @return Entry
 483      */
 484     public function getRandomEntry($userId, $type = '')
 485     {
 486         $qb = $this->getQueryBuilderByUser($userId)
 487             ->select('e.id');
 488
 489         switch ($type) {
 490             case 'unread':
 491                 $qb->andWhere('e.isArchived = false');
 492                 break;
 493             case 'archive':
 494                 $qb->andWhere('e.isArchived = true');
 495                 break;
 496             case 'starred':
 497                 $qb->andWhere('e.isStarred = true');
 498                 break;
 499             case 'untagged':
 500                 $qb->leftJoin('e.tags', 't');
 501                 $qb->andWhere('t.id is null');
 502                 break;
 503         }
 504
 505         $ids = $qb->getQuery()->getArrayResult();
 506
 507         if (empty($ids)) {
 508             throw new NoResultException();
 509         }
 510
 511         // random select one in the list
 512         $randomId = $ids[mt_rand(0, \count($ids) - 1)]['id'];
 513
 514         return $this->find($randomId);
 515     }
 516
 517     /**
 518      * Return a query builder to be used by other getBuilderFor* method.
 519      *
 520      * @param int $userId
 521      *
 522      * @return QueryBuilder
 523      */
 524     private function getQueryBuilderByUser($userId)
 525     {
 526         return $this->createQueryBuilder('e')
 527             ->andWhere('e.user = :userId')->setParameter('userId', $userId);
 528     }
 529
 530     /**
 531      * Return a sorted query builder to be used by other getBuilderFor* method.
 532      *
 533      * @param int    $userId
 534      * @param string $sortBy
 535      * @param string $direction
 536      *
 537      * @return QueryBuilder
 538      */
 539     private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
 540     {
 541         return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
 542     }
 543
 544     /**
 545      * Return the given QueryBuilder with an orderBy() call.
 546      *
 547      * @param QueryBuilder $qb
 548      * @param string       $sortBy
 549      * @param string       $direction
 550      *
 551      * @return QueryBuilder
 552      */
 553     private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
 554     {
 555         return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);
 556     }
 557 }