src/Wallabag/CoreBundle/Repository/EntryRepository.php

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