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