src/Wallabag/CoreBundle/Repository/EntryRepository.php

   1 <?php
   2
   3 namespace Wallabag\CoreBundle\Repository;
   4
   5 use Doctrine\ORM\EntityRepository;
   6 use Doctrine\ORM\Query;
   7 use Pagerfanta\Adapter\DoctrineORMAdapter;
   8 use Pagerfanta\Pagerfanta;
   9 use Wallabag\CoreBundle\Entity\Tag;
  10
  11 class EntryRepository extends EntityRepository
  12 {
  13     /**
  14      * Retrieves all entries for a user.
  15      *
  16      * @param int $userId
  17      *
  18      * @return QueryBuilder
  19      */
  20     public function getBuilderForAllByUser($userId)
  21     {
  22         return $this
  23             ->getBuilderByUser($userId)
  24         ;
  25     }
  26
  27     /**
  28      * Retrieves unread entries for a user.
  29      *
  30      * @param int $userId
  31      *
  32      * @return QueryBuilder
  33      */
  34     public function getBuilderForUnreadByUser($userId)
  35     {
  36         return $this
  37             ->getBuilderByUser($userId)
  38             ->andWhere('e.isArchived = false')
  39         ;
  40     }
  41
  42     /**
  43      * Retrieves read entries for a user.
  44      *
  45      * @param int $userId
  46      *
  47      * @return QueryBuilder
  48      */
  49     public function getBuilderForArchiveByUser($userId)
  50     {
  51         return $this
  52             ->getBuilderByUser($userId)
  53             ->andWhere('e.isArchived = true')
  54         ;
  55     }
  56
  57     /**
  58      * Retrieves starred entries for a user.
  59      *
  60      * @param int $userId
  61      *
  62      * @return QueryBuilder
  63      */
  64     public function getBuilderForStarredByUser($userId)
  65     {
  66         return $this
  67             ->getBuilderByUser($userId)
  68             ->andWhere('e.isStarred = true')
  69         ;
  70     }
  71
  72     /**
  73      * Retrieves entries filtered with a search term for a user.
  74      *
  75      * @param int    $userId
  76      * @param string $term
  77      * @param strint $currentRoute
  78      *
  79      * @return QueryBuilder
  80      */
  81     public function getBuilderForSearchByUser($userId, $term, $currentRoute)
  82     {
  83         $qb = $this
  84             ->getBuilderByUser($userId);
  85
  86         if ('starred' === $currentRoute) {
  87             $qb->andWhere('e.isStarred = true');
  88         } elseif ('unread' === $currentRoute) {
  89             $qb->andWhere('e.isArchived = false');
  90         } elseif ('archive' === $currentRoute) {
  91             $qb->andWhere('e.isArchived = true');
  92         }
  93
  94         // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
  95         $qb
  96             ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
  97             ->leftJoin('e.tags', 't')
  98             ->groupBy('e.id');
  99
 100         return $qb;
 101     }
 102
 103     /**
 104      * Retrieves untagged entries for a user.
 105      *
 106      * @param int $userId
 107      *
 108      * @return QueryBuilder
 109      */
 110     public function getBuilderForUntaggedByUser($userId)
 111     {
 112         return $this
 113             ->getBuilderByUser($userId)
 114             ->andWhere('size(e.tags) = 0');
 115     }
 116
 117     /**
 118      * Find Entries.
 119      *
 120      * @param int    $userId
 121      * @param bool   $isArchived
 122      * @param bool   $isStarred
 123      * @param bool   $isPublic
 124      * @param string $sort
 125      * @param string $order
 126      * @param int    $since
 127      * @param string $tags
 128      *
 129      * @return array
 130      */
 131     public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
 132     {
 133         $qb = $this->createQueryBuilder('e')
 134             ->leftJoin('e.tags', 't')
 135             ->where('e.user =:userId')->setParameter('userId', $userId);
 136
 137         if (null !== $isArchived) {
 138             $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
 139         }
 140
 141         if (null !== $isStarred) {
 142             $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
 143         }
 144
 145         if (null !== $isPublic) {
 146             $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
 147         }
 148
 149         if ($since > 0) {
 150             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 151         }
 152
 153         if ('' !== $tags) {
 154             foreach (explode(',', $tags) as $tag) {
 155                 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
 156             }
 157         }
 158
 159         if ('created' === $sort) {
 160             $qb->orderBy('e.id', $order);
 161         } elseif ('updated' === $sort) {
 162             $qb->orderBy('e.updatedAt', $order);
 163         }
 164
 165         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 166
 167         return new Pagerfanta($pagerAdapter);
 168     }
 169
 170     /**
 171      * Fetch an entry with a tag. Only used for tests.
 172      *
 173      * @param int $userId
 174      *
 175      * @return Entry
 176      */
 177     public function findOneWithTags($userId)
 178     {
 179         $qb = $this->createQueryBuilder('e')
 180             ->innerJoin('e.tags', 't')
 181             ->innerJoin('e.user', 'u')
 182             ->addSelect('t', 'u')
 183             ->where('e.user=:userId')->setParameter('userId', $userId)
 184         ;
 185
 186         return $qb->getQuery()->getResult();
 187     }
 188
 189     /**
 190      * Find distinct language for a given user.
 191      * Used to build the filter language list.
 192      *
 193      * @param int $userId User id
 194      *
 195      * @return array
 196      */
 197     public function findDistinctLanguageByUser($userId)
 198     {
 199         $results = $this->createQueryBuilder('e')
 200             ->select('e.language')
 201             ->where('e.user = :userId')->setParameter('userId', $userId)
 202             ->andWhere('e.language IS NOT NULL')
 203             ->groupBy('e.language')
 204             ->orderBy('e.language', ' ASC')
 205             ->getQuery()
 206             ->getResult();
 207
 208         $languages = [];
 209         foreach ($results as $result) {
 210             $languages[$result['language']] = $result['language'];
 211         }
 212
 213         return $languages;
 214     }
 215
 216     /**
 217      * Used only in test case to get the right entry associated to the right user.
 218      *
 219      * @param string $username
 220      *
 221      * @return Entry
 222      */
 223     public function findOneByUsernameAndNotArchived($username)
 224     {
 225         return $this->createQueryBuilder('e')
 226             ->leftJoin('e.user', 'u')
 227             ->where('u.username = :username')->setParameter('username', $username)
 228             ->andWhere('e.isArchived = false')
 229             ->setMaxResults(1)
 230             ->getQuery()
 231             ->getSingleResult();
 232     }
 233
 234     /**
 235      * Remove a tag from all user entries.
 236      *
 237      * 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.
 238      * 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:
 239      *
 240      * 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
 241      *
 242      * @param int $userId
 243      * @param Tag $tag
 244      */
 245     public function removeTag($userId, Tag $tag)
 246     {
 247         $entries = $this->getBuilderByUser($userId)
 248             ->innerJoin('e.tags', 't')
 249             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 250             ->getQuery()
 251             ->getResult();
 252
 253         foreach ($entries as $entry) {
 254             $entry->removeTag($tag);
 255         }
 256
 257         $this->getEntityManager()->flush();
 258     }
 259
 260     /**
 261      * Remove tags from all user entries.
 262      *
 263      * @param int        $userId
 264      * @param Array<Tag> $tags
 265      */
 266     public function removeTags($userId, $tags)
 267     {
 268         foreach ($tags as $tag) {
 269             $this->removeTag($userId, $tag);
 270         }
 271     }
 272
 273     /**
 274      * Find all entries that are attached to a give tag id.
 275      *
 276      * @param int $userId
 277      * @param int $tagId
 278      *
 279      * @return array
 280      */
 281     public function findAllByTagId($userId, $tagId)
 282     {
 283         return $this->getBuilderByUser($userId)
 284             ->innerJoin('e.tags', 't')
 285             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 286             ->getQuery()
 287             ->getResult();
 288     }
 289
 290     /**
 291      * Find an entry by its url and its owner.
 292      * If it exists, return the entry otherwise return false.
 293      *
 294      * @param $url
 295      * @param $userId
 296      *
 297      * @return Entry|bool
 298      */
 299     public function findByUrlAndUserId($url, $userId)
 300     {
 301         $res = $this->createQueryBuilder('e')
 302             ->where('e.url = :url')->setParameter('url', urldecode($url))
 303             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 304             ->getQuery()
 305             ->getResult();
 306
 307         if (count($res)) {
 308             return current($res);
 309         }
 310
 311         return false;
 312     }
 313
 314     /**
 315      * Count all entries for a user.
 316      *
 317      * @param int $userId
 318      *
 319      * @return int
 320      */
 321     public function countAllEntriesByUser($userId)
 322     {
 323         $qb = $this->createQueryBuilder('e')
 324             ->select('count(e)')
 325             ->where('e.user=:userId')->setParameter('userId', $userId)
 326         ;
 327
 328         return $qb->getQuery()->getSingleScalarResult();
 329     }
 330
 331     /**
 332      * Count all entries for a tag and a user.
 333      *
 334      * @param int $userId
 335      * @param int $tagId
 336      *
 337      * @return int
 338      */
 339     public function countAllEntriesByUserIdAndTagId($userId, $tagId)
 340     {
 341         $qb = $this->createQueryBuilder('e')
 342             ->select('count(e.id)')
 343             ->leftJoin('e.tags', 't')
 344             ->where('e.user=:userId')->setParameter('userId', $userId)
 345             ->andWhere('t.id=:tagId')->setParameter('tagId', $tagId)
 346         ;
 347
 348         return $qb->getQuery()->getSingleScalarResult();
 349     }
 350
 351     /**
 352      * Remove all entries for a user id.
 353      * Used when a user want to reset all informations.
 354      *
 355      * @param int $userId
 356      */
 357     public function removeAllByUserId($userId)
 358     {
 359         $this->getEntityManager()
 360             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
 361             ->setParameter('userId', $userId)
 362             ->execute();
 363     }
 364
 365     public function removeArchivedByUserId($userId)
 366     {
 367         $this->getEntityManager()
 368             ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
 369             ->setParameter('userId', $userId)
 370             ->execute();
 371     }
 372
 373     /**
 374      * Get id and url from all entries
 375      * Used for the clean-duplicates command.
 376      */
 377     public function getAllEntriesIdAndUrl($userId)
 378     {
 379         $qb = $this->createQueryBuilder('e')
 380             ->select('e.id, e.url')
 381             ->where('e.user = :userid')->setParameter(':userid', $userId);
 382
 383         return $qb->getQuery()->getArrayResult();
 384     }
 385
 386     /**
 387      * Find all entries by url and owner.
 388      *
 389      * @param $url
 390      * @param $userId
 391      *
 392      * @return array
 393      */
 394     public function findAllByUrlAndUserId($url, $userId)
 395     {
 396         return $this->createQueryBuilder('e')
 397             ->where('e.url = :url')->setParameter('url', urldecode($url))
 398             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 399             ->getQuery()
 400             ->getResult();
 401     }
 402
 403     /**
 404      * Return a query builder to used by other getBuilderFor* method.
 405      *
 406      * @param int $userId
 407      *
 408      * @return QueryBuilder
 409      */
 410     private function getBuilderByUser($userId)
 411     {
 412         return $this->createQueryBuilder('e')
 413             ->andWhere('e.user = :userId')->setParameter('userId', $userId)
 414             ->orderBy('e.createdAt', 'desc')
 415         ;
 416     }
 417 }