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