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