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