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