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 string $sort
 139      * @param string $order
 140      * @param int    $since
 141      * @param string $tags
 142      *
 143      * @return array
 144      */
 145     public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
 146     {
 147         $qb = $this->createQueryBuilder('e')
 148             ->leftJoin('e.tags', 't')
 149             ->where('e.user =:userId')->setParameter('userId', $userId);
 150
 151         if (null !== $isArchived) {
 152             $qb->andWhere('e.isArchived =:isArchived')->setParameter('isArchived', (bool) $isArchived);
 153         }
 154
 155         if (null !== $isStarred) {
 156             $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred);
 157         }
 158
 159         if ($since > 0) {
 160             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 161         }
 162
 163         if ('' !== $tags) {
 164             foreach (explode(',', $tags) as $tag) {
 165                 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
 166             }
 167         }
 168
 169         if ('created' === $sort) {
 170             $qb->orderBy('e.id', $order);
 171         } elseif ('updated' === $sort) {
 172             $qb->orderBy('e.updatedAt', $order);
 173         }
 174
 175         $pagerAdapter = new DoctrineORMAdapter($qb, true, false);
 176
 177         return new Pagerfanta($pagerAdapter);
 178     }
 179
 180     /**
 181      * Fetch an entry with a tag. Only used for tests.
 182      *
 183      * @param int $userId
 184      *
 185      * @return Entry
 186      */
 187     public function findOneWithTags($userId)
 188     {
 189         $qb = $this->createQueryBuilder('e')
 190             ->innerJoin('e.tags', 't')
 191             ->innerJoin('e.user', 'u')
 192             ->addSelect('t', 'u')
 193             ->where('e.user=:userId')->setParameter('userId', $userId)
 194         ;
 195
 196         return $qb->getQuery()->getResult();
 197     }
 198
 199     /**
 200      * Find distinct language for a given user.
 201      * Used to build the filter language list.
 202      *
 203      * @param int $userId User id
 204      *
 205      * @return array
 206      */
 207     public function findDistinctLanguageByUser($userId)
 208     {
 209         $results = $this->createQueryBuilder('e')
 210             ->select('e.language')
 211             ->where('e.user = :userId')->setParameter('userId', $userId)
 212             ->andWhere('e.language IS NOT NULL')
 213             ->groupBy('e.language')
 214             ->orderBy('e.language', ' ASC')
 215             ->getQuery()
 216             ->getResult();
 217
 218         $languages = [];
 219         foreach ($results as $result) {
 220             $languages[$result['language']] = $result['language'];
 221         }
 222
 223         return $languages;
 224     }
 225
 226     /**
 227      * Used only in test case to get the right entry associated to the right user.
 228      *
 229      * @param string $username
 230      *
 231      * @return Entry
 232      */
 233     public function findOneByUsernameAndNotArchived($username)
 234     {
 235         return $this->createQueryBuilder('e')
 236             ->leftJoin('e.user', 'u')
 237             ->where('u.username = :username')->setParameter('username', $username)
 238             ->andWhere('e.isArchived = false')
 239             ->setMaxResults(1)
 240             ->getQuery()
 241             ->getSingleResult();
 242     }
 243
 244     /**
 245      * Remove a tag from all user entries.
 246      *
 247      * 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.
 248      * 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:
 249      *
 250      * 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
 251      *
 252      * @param int $userId
 253      * @param Tag $tag
 254      */
 255     public function removeTag($userId, Tag $tag)
 256     {
 257         $entries = $this->getBuilderByUser($userId)
 258             ->innerJoin('e.tags', 't')
 259             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 260             ->getQuery()
 261             ->getResult();
 262
 263         foreach ($entries as $entry) {
 264             $entry->removeTag($tag);
 265         }
 266
 267         $this->getEntityManager()->flush();
 268     }
 269
 270     /**
 271      * Remove tags from all user entries.
 272      *
 273      * @param int        $userId
 274      * @param Array<Tag> $tags
 275      */
 276     public function removeTags($userId, $tags)
 277     {
 278         foreach ($tags as $tag) {
 279             $this->removeTag($userId, $tag);
 280         }
 281     }
 282
 283     /**
 284      * Find all entries that are attached to a give tag id.
 285      *
 286      * @param int $userId
 287      * @param int $tagId
 288      *
 289      * @return array
 290      */
 291     public function findAllByTagId($userId, $tagId)
 292     {
 293         return $this->getBuilderByUser($userId)
 294             ->innerJoin('e.tags', 't')
 295             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 296             ->getQuery()
 297             ->getResult();
 298     }
 299
 300     /**
 301      * Find an entry by its url and its owner.
 302      * If it exists, return the entry otherwise return false.
 303      *
 304      * @param $url
 305      * @param $userId
 306      *
 307      * @return Entry|bool
 308      */
 309     public function findByUrlAndUserId($url, $userId)
 310     {
 311         $res = $this->createQueryBuilder('e')
 312             ->where('e.url = :url')->setParameter('url', urldecode($url))
 313             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 314             ->getQuery()
 315             ->getResult();
 316
 317         if (count($res)) {
 318             return current($res);
 319         }
 320
 321         return false;
 322     }
 323
 324     /**
 325      * Count all entries for a user.
 326      *
 327      * @param int $userId
 328      *
 329      * @return int
 330      */
 331     public function countAllEntriesByUser($userId)
 332     {
 333         $qb = $this->createQueryBuilder('e')
 334             ->select('count(e)')
 335             ->where('e.user=:userId')->setParameter('userId', $userId)
 336         ;
 337
 338         return $qb->getQuery()->getSingleScalarResult();
 339     }
 340
 341     /**
 342      * Count all entries for a tag and a user.
 343      *
 344      * @param int $userId
 345      * @param int $tagId
 346      *
 347      * @return int
 348      */
 349     public function countAllEntriesByUserIdAndTagId($userId, $tagId)
 350     {
 351         $qb = $this->createQueryBuilder('e')
 352             ->select('count(e.id)')
 353             ->leftJoin('e.tags', 't')
 354             ->where('e.user=:userId')->setParameter('userId', $userId)
 355             ->andWhere('t.id=:tagId')->setParameter('tagId', $tagId)
 356         ;
 357
 358         return $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 getAllEntriesIdAndUrl($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      * Find all entries by url and owner.
 398      *
 399      * @param $url
 400      * @param $userId
 401      *
 402      * @return array
 403      */
 404     public function findAllByUrlAndUserId($url, $userId)
 405     {
 406         return $this->createQueryBuilder('e')
 407             ->where('e.url = :url')->setParameter('url', urldecode($url))
 408             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 409             ->getQuery()
 410             ->getResult();
 411     }
 412 }