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