src/Wallabag/CoreBundle/Repository/EntryRepository.php

   1 <?php
   2
   3 namespace Wallabag\CoreBundle\Repository;
   4
   5 use Doctrine\ORM\EntityRepository;
   6 use Pagerfanta\Adapter\DoctrineORMAdapter;
   7 use Pagerfanta\Pagerfanta;
   8 use Wallabag\CoreBundle\Entity\Tag;
   9
  10 class EntryRepository extends EntityRepository
  11 {
  12     /**
  13      * Return a query builder to used by other getBuilderFor* method.
  14      *
  15      * @param int $userId
  16      *
  17      * @return QueryBuilder
  18      */
  19     private function getBuilderByUser($userId)
  20     {
  21         return $this->createQueryBuilder('e')
  22             ->leftJoin('e.user', 'u')
  23             ->andWhere('u.id = :userId')->setParameter('userId', $userId)
  24             ->orderBy('e.id', '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      * Find Entries.
  89      *
  90      * @param int    $userId
  91      * @param bool   $isArchived
  92      * @param bool   $isStarred
  93      * @param string $sort
  94      * @param string $order
  95      *
  96      * @return array
  97      */
  98     public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC')
  99     {
 100         $qb = $this->createQueryBuilder('e')
 101             ->where('e.user =:userId')->setParameter('userId', $userId);
 102
 103         if (null !== $isArchived) {
 104             $qb->andWhere('e.isArchived =:isArchived')->setParameter('isArchived', (bool) $isArchived);
 105         }
 106
 107         if (null !== $isStarred) {
 108             $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred);
 109         }
 110
 111         if ('created' === $sort) {
 112             $qb->orderBy('e.id', $order);
 113         } elseif ('updated' === $sort) {
 114             $qb->orderBy('e.updatedAt', $order);
 115         }
 116
 117         $pagerAdapter = new DoctrineORMAdapter($qb);
 118
 119         return new Pagerfanta($pagerAdapter);
 120     }
 121
 122     /**
 123      * Fetch an entry with a tag. Only used for tests.
 124      *
 125      * @return Entry
 126      */
 127     public function findOneWithTags($userId)
 128     {
 129         $qb = $this->createQueryBuilder('e')
 130             ->innerJoin('e.tags', 't')
 131             ->innerJoin('e.user', 'u')
 132             ->addSelect('t', 'u')
 133             ->where('e.user=:userId')->setParameter('userId', $userId)
 134         ;
 135
 136         return $qb->getQuery()->getResult();
 137     }
 138
 139     /**
 140      * Find distinct language for a given user.
 141      * Used to build the filter language list.
 142      *
 143      * @param int $userId User id
 144      *
 145      * @return array
 146      */
 147     public function findDistinctLanguageByUser($userId)
 148     {
 149         $results = $this->createQueryBuilder('e')
 150             ->select('e.language')
 151             ->where('e.user = :userId')->setParameter('userId', $userId)
 152             ->andWhere('e.language IS NOT NULL')
 153             ->groupBy('e.language')
 154             ->orderBy('e.language', ' ASC')
 155             ->getQuery()
 156             ->getResult();
 157
 158         $languages = array();
 159         foreach ($results as $result) {
 160             $languages[$result['language']] = $result['language'];
 161         }
 162
 163         return $languages;
 164     }
 165
 166     /**
 167      * Used only in test case to get the right entry associated to the right user.
 168      *
 169      * @param string $username
 170      *
 171      * @return Entry
 172      */
 173     public function findOneByUsernameAndNotArchived($username)
 174     {
 175         return $this->createQueryBuilder('e')
 176             ->leftJoin('e.user', 'u')
 177             ->where('u.username = :username')->setParameter('username', $username)
 178             ->andWhere('e.isArchived = false')
 179             ->setMaxResults(1)
 180             ->getQuery()
 181             ->getSingleResult();
 182     }
 183
 184     /**
 185      * Remove a tag from all user entries.
 186      * We are using a native SQL query because Doctrine doesn't know EntryTag entity because it's a ManyToMany relation.
 187      * Instead of that SQL query we should loop on every entry and remove the tag, could be really long ...
 188      *
 189      * @param int $userId
 190      * @param Tag $tag
 191      */
 192     public function removeTag($userId, Tag $tag)
 193     {
 194         $sql = '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';
 195         $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
 196         $stmt->execute([
 197             'userId' => $userId,
 198             'tagId' => $tag->getId(),
 199         ]);
 200     }
 201 }