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      * @param int    $since
  96      * @param string $tags
  97      *
  98      * @return array
  99      */
 100     public function findEntries($userId, $isArchived = null, $isStarred = null, $sort = 'created', $order = 'ASC', $since = 0, $tags = '')
 101     {
 102         $qb = $this->createQueryBuilder('e')
 103             ->leftJoin('e.tags', 't')
 104             ->where('e.user =:userId')->setParameter('userId', $userId);
 105
 106         if (null !== $isArchived) {
 107             $qb->andWhere('e.isArchived =:isArchived')->setParameter('isArchived', (bool) $isArchived);
 108         }
 109
 110         if (null !== $isStarred) {
 111             $qb->andWhere('e.isStarred =:isStarred')->setParameter('isStarred', (bool) $isStarred);
 112         }
 113
 114         if ($since >= 0) {
 115             $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
 116         }
 117
 118         if ('' !== $tags) {
 119             foreach (explode(',', $tags) as $tag) {
 120                 $qb->andWhere('t.label = :label')->setParameter('label', $tag);
 121             }
 122         }
 123
 124         if ('created' === $sort) {
 125             $qb->orderBy('e.id', $order);
 126         } elseif ('updated' === $sort) {
 127             $qb->orderBy('e.updatedAt', $order);
 128         }
 129
 130         $pagerAdapter = new DoctrineORMAdapter($qb);
 131
 132         return new Pagerfanta($pagerAdapter);
 133     }
 134
 135     /**
 136      * Fetch an entry with a tag. Only used for tests.
 137      *
 138      * @param int $userId
 139      *
 140      * @return Entry
 141      */
 142     public function findOneWithTags($userId)
 143     {
 144         $qb = $this->createQueryBuilder('e')
 145             ->innerJoin('e.tags', 't')
 146             ->innerJoin('e.user', 'u')
 147             ->addSelect('t', 'u')
 148             ->where('e.user=:userId')->setParameter('userId', $userId)
 149         ;
 150
 151         return $qb->getQuery()->getResult();
 152     }
 153
 154     /**
 155      * Find distinct language for a given user.
 156      * Used to build the filter language list.
 157      *
 158      * @param int $userId User id
 159      *
 160      * @return array
 161      */
 162     public function findDistinctLanguageByUser($userId)
 163     {
 164         $results = $this->createQueryBuilder('e')
 165             ->select('e.language')
 166             ->where('e.user = :userId')->setParameter('userId', $userId)
 167             ->andWhere('e.language IS NOT NULL')
 168             ->groupBy('e.language')
 169             ->orderBy('e.language', ' ASC')
 170             ->getQuery()
 171             ->getResult();
 172
 173         $languages = [];
 174         foreach ($results as $result) {
 175             $languages[$result['language']] = $result['language'];
 176         }
 177
 178         return $languages;
 179     }
 180
 181     /**
 182      * Used only in test case to get the right entry associated to the right user.
 183      *
 184      * @param string $username
 185      *
 186      * @return Entry
 187      */
 188     public function findOneByUsernameAndNotArchived($username)
 189     {
 190         return $this->createQueryBuilder('e')
 191             ->leftJoin('e.user', 'u')
 192             ->where('u.username = :username')->setParameter('username', $username)
 193             ->andWhere('e.isArchived = false')
 194             ->setMaxResults(1)
 195             ->getQuery()
 196             ->getSingleResult();
 197     }
 198
 199     /**
 200      * Remove a tag from all user entries.
 201      *
 202      * 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.
 203      * 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:
 204      *
 205      * 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
 206      *
 207      * @param int $userId
 208      * @param Tag $tag
 209      */
 210     public function removeTag($userId, Tag $tag)
 211     {
 212         $entries = $this->getBuilderByUser($userId)
 213             ->innerJoin('e.tags', 't')
 214             ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
 215             ->getQuery()
 216             ->getResult();
 217
 218         foreach ($entries as $entry) {
 219             $entry->removeTag($tag);
 220         }
 221
 222         $this->getEntityManager()->flush();
 223     }
 224
 225     /**
 226      * Find all entries that are attached to a give tag id.
 227      *
 228      * @param int $userId
 229      * @param int $tagId
 230      *
 231      * @return array
 232      */
 233     public function findAllByTagId($userId, $tagId)
 234     {
 235         return $this->getBuilderByUser($userId)
 236             ->innerJoin('e.tags', 't')
 237             ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
 238             ->getQuery()
 239             ->getResult();
 240     }
 241
 242     /**
 243      * Find an entry by its url and its owner.
 244      * If it exists, return the entry otherwise return false.
 245      *
 246      * @param $url
 247      * @param $userId
 248      *
 249      * @return Entry|bool
 250      */
 251     public function findByUrlAndUserId($url, $userId)
 252     {
 253         $res = $this->createQueryBuilder('e')
 254             ->where('e.url = :url')->setParameter('url', $url)
 255             ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
 256             ->getQuery()
 257             ->getResult();
 258
 259         if (count($res)) {
 260             return current($res);
 261         }
 262
 263         return false;
 264     }
 265
 266     /**
 267      * Count all entries for a user.
 268      *
 269      * @param int $userId
 270      *
 271      * @return int
 272      */
 273     public function countAllEntriesByUsername($userId)
 274     {
 275         $qb = $this->createQueryBuilder('e')
 276             ->select('count(e)')
 277             ->where('e.user=:userId')->setParameter('userId', $userId)
 278         ;
 279
 280         return $qb->getQuery()->getSingleScalarResult();
 281     }
 282 }