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             ->getBuilderByUser($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             ->getBuilderByUser($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             ->getBuilderByUser($userId)
  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             ->getBuilderByUser($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             ->getBuilderByUser($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      * Retrieves untagged entries for a user.
 106      *
 107      * @param int $userId
 108      *
 109      * @return QueryBuilder
 110      */
 111     public function getBuilderForUntaggedByUser($userId)
 112     {
 113         return $this
 114             ->getBuilderByUser($userId)
 115             ->andWhere('size(e.tags) = 0');
 116     }
 117
 118     /**
 119      * Retrieve the number of untagged entries for a user.
 120      *
 121      * @param int $userId
 122      *
 123      * @return int
 124      */
 125     public function countUntaggedEntriesByUser($userId)
 126     {
 127         return $this->getRawBuilderForUntaggedByUser($userId)
 128             ->select('count(e.id)')
 129             ->getSingleScalarResult();
 130     }
 131
 132     /**
 133      * Find Entries.
 134      *
 135      * @param int    $userId
 136      * @param bool   $isArchived
 137      * @param bool   $isStarred
 138      * @param bool   $isPublic
 139      * @param string $sort
 140      * @param string $order
 141      * @param int    $since
 142      * @param string $tags
 143      *
 144      * @return Pagerfanta
 145      */
 146     public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
 147     {
 148         $qb = $this->createQueryBuilder('e')
 149             ->leftJoin('e.tags', 't')
 150             ->where('e.user = :userId')->setParameter('userId', $userId);
 151
 152         if (null !== $isArchived) {
 153             $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
 154         }
 155
 156         if (null !== $isStarred) {
 157             $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
 158         }
 159
 160         if (null !== $isPublic) {
 161             $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
 162         }
 163
 164         if ($since > 0) {
 165             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 166         }
 167
 168         if (is_string($tags) && '' !== $tags) {
 169             foreach (explode(',', $tags) as $i => $tag) {
 170                 $entryAlias = 'e' . $i;
 171                 $tagAlias = 't' . $i;
 172
 173                 // Complexe queries to ensure multiple tags are associated to an entry
 174                 // https://stackoverflow.com/a/6638146/569101
 175                 $qb->andWhere($qb->expr()->in(
 176                     'e.id',
 177                     $this->createQueryBuilder($entryAlias)
 178                         ->select($entryAlias . '.id')
 179                         ->leftJoin($entryAlias . '.tags', $tagAlias)
 180                         ->where($tagAlias . '.label = :label' . $i)
 181                         ->getDQL()
 182                 ));
 183
 184                 // bound parameter to the main query builder
 185                 $qb->setParameter('label' . $i, $tag);
 186             }
 187         }
 188
 189         if ('created' === $sort) {
 190             $qb->orderBy('e.id', $order);
 191         } elseif ('updated' === $sort) {
 192             $qb->orderBy('e.updatedAt', $order);
 193         }
 194
 195         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 196
 197         return new Pagerfanta($pagerAdapter);
 198     }
 199
 200     /**
 201      * Fetch an entry with a tag. Only used for tests.
 202      *
 203      * @param int $userId
 204      *
 205      * @return array
 206      */
 207     public function findOneWithTags($userId)
 208     {
 209         $qb = $this->createQueryBuilder('e')
 210             ->innerJoin('e.tags', 't')
 211             ->innerJoin('e.user', 'u')
 212             ->addSelect('t', 'u')
 213             ->where('e.user = :userId')->setParameter('userId', $userId)
 214         ;
 215
 216         return $qb->getQuery()->getResult();
 217     }
 218
 219     /**
 220      * Find distinct language for a given user.
 221      * Used to build the filter language list.
 222      *
 223      * @param int $userId User id
 224      *
 225      * @return array
 226      */
 227     public function findDistinctLanguageByUser($userId)
 228     {
 229         $results = $this->createQueryBuilder('e')
 230             ->select('e.language')
 231             ->where('e.user = :userId')->setParameter('userId', $userId)
 232             ->andWhere('e.language IS NOT NULL')
 233             ->groupBy('e.language')
 234             ->orderBy('e.language', ' ASC')
 235             ->getQuery()
 236             ->getResult();
 237
 238         $languages = [];
 239         foreach ($results as $result) {
 240             $languages[$result['language']] = $result['language'];
 241         }
 242
 243         return $languages;
 244     }
 245
 246     /**
 247      * Used only in test case to get the right entry associated to the right user.
 248      *
 249      * @param string $username
 250      *
 251      * @return Entry
 252      */
 253     public function findOneByUsernameAndNotArchived($username)
 254     {
 255         return $this->createQueryBuilder('e')
 256             ->leftJoin('e.user', 'u')
 257             ->where('u.username = :username')->setParameter('username', $username)
 258             ->andWhere('e.isArchived = false')
 259             ->setMaxResults(1)
 260             ->getQuery()
 261             ->getSingleResult();
 262     }
 263
 264     /**
 265      * Remove a tag from all user entries.
 266      *
 267      * 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.
 268      * 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:
 269      *
 270      * 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
 271      *
 272      * @param int $userId
 273      * @param Tag $tag
 274      */
 275     public function removeTag($userId, Tag $tag)
 276     {
 277         $entries = $this->getBuilderByUser($userId)
 278             ->innerJoin('e.tags', 't')
 279             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 280             ->getQuery()
 281             ->getResult();
 282
 283         foreach ($entries as $entry) {
 284             $entry->removeTag($tag);
 285         }
 286
 287         $this->getEntityManager()->flush();
 288     }
 289
 290     /**
 291      * Remove tags from all user entries.
 292      *
 293      * @param int        $userId
 294      * @param Array<Tag> $tags
 295      */
 296     public function removeTags($userId, $tags)
 297     {
 298         foreach ($tags as $tag) {
 299             $this->removeTag($userId, $tag);
 300         }
 301     }
 302
 303     /**
 304      * Find all entries that are attached to a give tag id.
 305      *
 306      * @param int $userId
 307      * @param int $tagId
 308      *
 309      * @return array
 310      */
 311     public function findAllByTagId($userId, $tagId)
 312     {
 313         return $this->getBuilderByUser($userId)
 314             ->innerJoin('e.tags', 't')
 315             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 316             ->getQuery()
 317             ->getResult();
 318     }
 319
 320     /**
 321      * Find an entry by its url and its owner.
 322      * If it exists, return the entry otherwise return false.
 323      *
 324      * @param $url
 325      * @param $userId
 326      *
 327      * @return Entry|bool
 328      */
 329     public function findByUrlAndUserId($url, $userId)
 330     {
 331         $res = $this->createQueryBuilder('e')
 332             ->where('e.url = :url')->setParameter('url', urldecode($url))
 333             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 334             ->getQuery()
 335             ->getResult();
 336
 337         if (count($res)) {
 338             return current($res);
 339         }
 340
 341         return false;
 342     }
 343
 344     /**
 345      * Count all entries for a user.
 346      *
 347      * @param int $userId
 348      *
 349      * @return int
 350      */
 351     public function countAllEntriesByUser($userId)
 352     {
 353         $qb = $this->createQueryBuilder('e')
 354             ->select('count(e)')
 355             ->where('e.user = :userId')->setParameter('userId', $userId)
 356         ;
 357
 358         return (int) $qb->getQuery()->getSingleScalarResult();
 359     }
 360
 361     /**
 362      * Remove all entries for a user id.
 363      * Used when a user want to reset all informations.
 364      *
 365      * @param int $userId
 366      */
 367     public function removeAllByUserId($userId)
 368     {
 369         $this->getEntityManager()
 370             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
 371             ->setParameter('userId', $userId)
 372             ->execute();
 373     }
 374
 375     public function removeArchivedByUserId($userId)
 376     {
 377         $this->getEntityManager()
 378             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
 379             ->setParameter('userId', $userId)
 380             ->execute();
 381     }
 382
 383     /**
 384      * Get id and url from all entries
 385      * Used for the clean-duplicates command.
 386      */
 387     public function findAllEntriesIdAndUrlByUserId($userId)
 388     {
 389         $qb = $this->createQueryBuilder('e')
 390             ->select('e.id, e.url')
 391             ->where('e.user = :userid')->setParameter(':userid', $userId);
 392
 393         return $qb->getQuery()->getArrayResult();
 394     }
 395
 396     /**
 397      * @param int $userId
 398      *
 399      * @return array
 400      */
 401     public function findAllEntriesIdByUserId($userId = null)
 402     {
 403         $qb = $this->createQueryBuilder('e')
 404             ->select('e.id');
 405
 406         if (null !== $userId) {
 407             $qb->where('e.user = :userid')->setParameter(':userid', $userId);
 408         }
 409
 410         return $qb->getQuery()->getArrayResult();
 411     }
 412
 413     /**
 414      * Find all entries by url and owner.
 415      *
 416      * @param $url
 417      * @param $userId
 418      *
 419      * @return array
 420      */
 421     public function findAllByUrlAndUserId($url, $userId)
 422     {
 423         return $this->createQueryBuilder('e')
 424             ->where('e.url = :url')->setParameter('url', urldecode($url))
 425             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 426             ->getQuery()
 427             ->getResult();
 428     }
 429
 430     /**
 431      * Return a query builder to used by other getBuilderFor* method.
 432      *
 433      * @param int    $userId
 434      * @param string $sortBy
 435      * @param string $direction
 436      *
 437      * @return QueryBuilder
 438      */
 439     private function getBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
 440     {
 441         return $this->createQueryBuilder('e')
 442             ->andWhere('e.user = :userId')->setParameter('userId', $userId)
 443             ->orderBy(sprintf('e.%s', $sortBy), $direction);
 444     }
 445 }