EntryRepository::findByUserIdAndBatchHashedUrls() - Code Metrics - Inspection of "Merge pull request #5261 from wallabag/fix/perform..." - wallabag/wallabag - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( dc6e1a...d7a3f7 )

by Kevin

created 2021-08-06 22:11 UTC

EntryRepository::findByUserIdAndBatchHashedUrls() A

↳ Parent: EntryRepository

Complexity

Conditions	1
Paths	1

Size

Total Lines	14
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	9
c	1
b	0
f	0
nc	1
nop	2
dl	0
loc	14
rs	9.9666

<?php

namespace Wallabag\CoreBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\NoResultException;
use Doctrine\ORM\QueryBuilder;
use Pagerfanta\Doctrine\ORM\QueryAdapter as DoctrineORMAdapter;
use Pagerfanta\Pagerfanta;
use Wallabag\CoreBundle\Entity\Entry;
use Wallabag\CoreBundle\Entity\Tag;
use Wallabag\CoreBundle\Helper\UrlHasher;

class EntryRepository extends EntityRepository
{
    /**
     * Retrieves all entries for a user.
     *
     * @param int $userId
     *
     * @return QueryBuilder
     */
    public function getBuilderForAllByUser($userId)
    {
        return $this
            ->getSortedQueryBuilderByUser($userId)
        ;
    }

    /**
     * Retrieves unread entries for a user.
     *
     * @param int $userId
     *
     * @return QueryBuilder
     */
    public function getBuilderForUnreadByUser($userId)
    {
        return $this
            ->getSortedQueryBuilderByUser($userId)
            ->andWhere('e.isArchived = false')
        ;
    }

    /**
     * Retrieves read entries for a user.
     *
     * @param int $userId
     *
     * @return QueryBuilder
     */
    public function getBuilderForArchiveByUser($userId)
    {
        return $this
            ->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
            ->andWhere('e.isArchived = true')
        ;
    }

    /**
     * Retrieves starred entries for a user.
     *
     * @param int $userId
     *
     * @return QueryBuilder
     */
    public function getBuilderForStarredByUser($userId)
    {
        return $this
            ->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
            ->andWhere('e.isStarred = true')
        ;
    }

    /**
     * Retrieves entries filtered with a search term for a user.
     *
     * @param int    $userId
     * @param string $term
     * @param string $currentRoute
     *
     * @return QueryBuilder
     */
    public function getBuilderForSearchByUser($userId, $term, $currentRoute)
    {
        $qb = $this
            ->getSortedQueryBuilderByUser($userId);

        if ('starred' === $currentRoute) {
            $qb->andWhere('e.isStarred = true');
        } elseif ('unread' === $currentRoute) {
            $qb->andWhere('e.isArchived = false');
        } elseif ('archive' === $currentRoute) {
            $qb->andWhere('e.isArchived = true');
        }

        // We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
        $qb
            ->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
            ->leftJoin('e.tags', 't')
            ->groupBy('e.id');

        return $qb;
    }

    /**
     * Retrieve a sorted list of untagged entries for a user.
     *
     * @param int $userId
     *
     * @return QueryBuilder
     */
    public function getBuilderForUntaggedByUser($userId)
    {
        return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
    }

    /**
     * Retrieve untagged entries for a user.
     *
     * @param int $userId
     *
     * @return QueryBuilder
     */
    public function getRawBuilderForUntaggedByUser($userId)
    {
        return $this->getQueryBuilderByUser($userId)
            ->leftJoin('e.tags', 't')
            ->andWhere('t.id is null');
    }

    /**
     * Retrieve the number of untagged entries for a user.
     *
     * @param int $userId
     *
     * @return int
     */
    public function countUntaggedEntriesByUser($userId)
    {
        return (int) $this->getRawBuilderForUntaggedByUser($userId)
            ->select('count(e.id)')
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * Find Entries.
     *
     * @param int    $userId
     * @param bool   $isArchived
     * @param bool   $isStarred
     * @param bool   $isPublic
     * @param string $sort
     * @param string $order
     * @param int    $since
     * @param string $tags
     * @param string $detail     'metadata' or 'full'. Include content field if 'full'
     *
     * @todo Breaking change: replace default detail=full by detail=metadata in a future version
     *
     * @return Pagerfanta
     */
    public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '', $detail = 'full')
    {
        if (!\in_array(strtolower($detail), ['full', 'metadata'], true)) {
            throw new \Exception('Detail "' . $detail . '" parameter is wrong, allowed: full or metadata');
        }

        $qb = $this->createQueryBuilder('e')
            ->leftJoin('e.tags', 't')
            ->where('e.user = :userId')->setParameter('userId', $userId);

        if ('metadata' === $detail) {
            $fieldNames = $this->getClassMetadata()->getFieldNames();
            $fields = array_filter($fieldNames, function ($k) {
                return 'content' !== $k;
            });
            $qb->select(sprintf('partial e.{%s}', implode(',', $fields)));
        }

        if (null !== $isArchived) {
            $qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
        }

        if (null !== $isStarred) {
            $qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
        }

        if (null !== $isPublic) {
            $qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
        }

        if ($since > 0) {
            $qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
        }

        if (\is_string($tags) && '' !== $tags) {
            foreach (explode(',', $tags) as $i => $tag) {
                $entryAlias = 'e' . $i;
                $tagAlias = 't' . $i;

                // Complexe queries to ensure multiple tags are associated to an entry
                // https://stackoverflow.com/a/6638146/569101
                $qb->andWhere($qb->expr()->in(
                    'e.id',
                    $this->createQueryBuilder($entryAlias)
                        ->select($entryAlias . '.id')
                        ->leftJoin($entryAlias . '.tags', $tagAlias)
                        ->where($tagAlias . '.label = :label' . $i)
                        ->getDQL()
                ));

                // bound parameter to the main query builder
                $qb->setParameter('label' . $i, $tag);
            }
        }

        if (!\in_array(strtolower($order), ['asc', 'desc'], true)) {
            throw new \Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
        }

        if ('created' === $sort) {
            $qb->orderBy('e.id', $order);
        } elseif ('updated' === $sort) {
            $qb->orderBy('e.updatedAt', $order);
        } elseif ('archived' === $sort) {
            $qb->orderBy('e.archivedAt', $order);
        }

        $pagerAdapter = new DoctrineORMAdapter($qb, true, false);

        return new Pagerfanta($pagerAdapter);
    }

    /**
     * Fetch an entry with a tag. Only used for tests.
     *
     * @param int $userId
     *
     * @return array
     */
    public function findOneWithTags($userId)
    {
        $qb = $this->createQueryBuilder('e')
            ->innerJoin('e.tags', 't')
            ->innerJoin('e.user', 'u')
            ->addSelect('t', 'u')
            ->where('e.user = :userId')->setParameter('userId', $userId)
        ;

        return $qb->getQuery()->getResult();
    }

    /**
     * Find distinct language for a given user.
     * Used to build the filter language list.
     *
     * @param int $userId User id
     *
     * @return array
     */
    public function findDistinctLanguageByUser($userId)
    {
        $results = $this->createQueryBuilder('e')
            ->select('e.language')
            ->where('e.user = :userId')->setParameter('userId', $userId)
            ->andWhere('e.language IS NOT NULL')
            ->groupBy('e.language')
            ->orderBy('e.language', ' ASC')
            ->getQuery()
            ->getResult();

        $languages = [];
        foreach ($results as $result) {
            $languages[$result['language']] = $result['language'];
        }

        return $languages;
    }

    /**
     * Used only in test case to get the right entry associated to the right user.
     *
     * @param string $username
     *
     * @return Entry
     */
    public function findOneByUsernameAndNotArchived($username)
    {
        return $this->createQueryBuilder('e')
            ->leftJoin('e.user', 'u')
            ->where('u.username = :username')->setParameter('username', $username)
            ->andWhere('e.isArchived = false')
            ->setMaxResults(1)
            ->getQuery()
            ->getSingleResult();
    }

    /**
     * Remove a tag from all user entries.
     *
     * 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.
     * 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:
     *
     * 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
     *
     * @param int $userId
     */
    public function removeTag($userId, Tag $tag)
    {
        $entries = $this->getSortedQueryBuilderByUser($userId)
            ->innerJoin('e.tags', 't')
            ->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
            ->getQuery()
            ->getResult();

        foreach ($entries as $entry) {
            $entry->removeTag($tag);
        }

        $this->getEntityManager()->flush();
    }

    /**
     * Remove tags from all user entries.
     *
     * @param int        $userId
     * @param Array<Tag> $tags
     */
    public function removeTags($userId, $tags)
    {
        foreach ($tags as $tag) {
            $this->removeTag($userId, $tag);
        }
    }

    /**
     * Find all entries that are attached to a give tag id.
     *
     * @param int $userId
     * @param int $tagId
     *
     * @return array
     */
    public function findAllByTagId($userId, $tagId)
    {
        return $this->getSortedQueryBuilderByUser($userId)
            ->innerJoin('e.tags', 't')
            ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
            ->getQuery()
            ->getResult();
    }

    /**
     * Find an entry by its url and its owner.
     * If it exists, return the entry otherwise return false.
     *
     * @param string $url
     * @param int    $userId
     *
     * @return Entry|false
     */
    public function findByUrlAndUserId($url, $userId)
    {
        return $this->findByHashedUrlAndUserId(
            UrlHasher::hashUrl($url),
            $userId
        );
    }

    /**
     * Find all entries which have an empty value for hash.
     *
     * @return Entry|false
     */
    public function findByEmptyHashedUrlAndUserId(int $userId)
    {
        return $this->createQueryBuilder('e')

            ->where('e.hashedUrl = :empty')->setParameter('empty', '')
            ->orWhere('e.hashedUrl is null')
            ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
            ->andWhere('e.url is not null')
            ->getQuery()
            ->getResult();
    }

    /**
     * Find an entry by its hashed url and its owner.
     * If it exists, return the entry otherwise return false.
     *
     * @param string $hashedUrl Url hashed using sha1
     * @param int    $userId
     *
     * @return Entry|false
     */
    public function findByHashedUrlAndUserId($hashedUrl, $userId)
    {
        // try first using hashed_url (to use the database index)
        $res = $this->createQueryBuilder('e')
            ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl)
            ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
            ->getQuery()
            ->getResult();

        if (\count($res)) {
            return current($res);
        }

        // then try using hashed_given_url (to use the database index)
        $res = $this->createQueryBuilder('e')
            ->where('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl)
            ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
            ->getQuery()
            ->getResult();

        if (\count($res)) {
            return current($res);
        }

        return false;
    }

    public function findByUserIdAndBatchHashedUrls($userId, $hashedUrls)
    {
        $qb = $this->createQueryBuilder('e')->select(['e.id', 'e.hashedUrl', 'e.hashedGivenUrl']);
        $res = $qb->where('e.user = :user_id')->setParameter('user_id', $userId)
                    ->andWhere(
                        $qb->expr()->orX(
                            $qb->expr()->in('e.hashedUrl', $hashedUrls),
                            $qb->expr()->in('e.hashedGivenUrl', $hashedUrls)
                        )
                    )
                    ->getQuery()
                    ->getResult();

        return $res;
    }

    /**
     * Count all entries for a user.
     *
     * @param int $userId
     *
     * @return int
     */
    public function countAllEntriesByUser($userId)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('count(e)')
            ->where('e.user = :userId')->setParameter('userId', $userId)
        ;

        return (int) $qb->getQuery()->getSingleScalarResult();
    }

    /**
     * Remove all entries for a user id.
     * Used when a user want to reset all informations.
     *
     * @param int $userId
     */
    public function removeAllByUserId($userId)
    {
        $this->getEntityManager()
            ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
            ->setParameter('userId', $userId)
            ->execute();
    }

    public function removeArchivedByUserId($userId)
    {
        $this->getEntityManager()
            ->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
            ->setParameter('userId', $userId)
            ->execute();
    }

    /**
     * Get id and url from all entries
     * Used for the clean-duplicates command.
     */
    public function findAllEntriesIdAndUrlByUserId($userId)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('e.id, e.url')
            ->where('e.user = :userid')->setParameter(':userid', $userId);

        return $qb->getQuery()->getArrayResult();
    }

    /**
     * @param int $userId
     *
     * @return array
     */
    public function findAllEntriesIdByUserId($userId = null)
    {
        $qb = $this->createQueryBuilder('e')
            ->select('e.id');

        if (null !== $userId) {
            $qb->where('e.user = :userid')->setParameter(':userid', $userId);
        }

        return $qb->getQuery()->getArrayResult();
    }

    /**
     * Find all entries by url and owner.
     *
     * @param string $url
     * @param int    $userId
     *
     * @return array
     */
    public function findAllByUrlAndUserId($url, $userId)
    {
        return $this->createQueryBuilder('e')
            ->where('e.url = :url')->setParameter('url', urldecode($url))
            ->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
            ->getQuery()
            ->getResult();
    }

    /**
     * Returns a random entry, filtering by status.
     *
     * @param int    $userId
     * @param string $type   Can be unread, archive, starred, etc
     *
     * @throws NoResultException
     *
     * @return Entry
     */
    public function getRandomEntry($userId, $type = '')
    {
        $qb = $this->getQueryBuilderByUser($userId)
            ->select('e.id');

        switch ($type) {
            case 'unread':
                $qb->andWhere('e.isArchived = false');
                break;
            case 'archive':
                $qb->andWhere('e.isArchived = true');
                break;
            case 'starred':
                $qb->andWhere('e.isStarred = true');
                break;
            case 'untagged':
                $qb->leftJoin('e.tags', 't');
                $qb->andWhere('t.id is null');
                break;
        }

        $ids = $qb->getQuery()->getArrayResult();

        if (empty($ids)) {
            throw new NoResultException();
        }

        // random select one in the list
        $randomId = $ids[mt_rand(0, \count($ids) - 1)]['id'];

        return $this->find($randomId);
    }

    /**
     * Return a query builder to be used by other getBuilderFor* method.
     *
     * @param int $userId
     *
     * @return QueryBuilder
     */
    private function getQueryBuilderByUser($userId)
    {
        return $this->createQueryBuilder('e')
            ->andWhere('e.user = :userId')->setParameter('userId', $userId);
    }

    /**
     * Return a sorted query builder to be used by other getBuilderFor* method.
     *
     * @param int    $userId
     * @param string $sortBy
     * @param string $direction
     *
     * @return QueryBuilder
     */
    private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
    {
        return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
    }

    /**
     * Return the given QueryBuilder with an orderBy() call.
     *
     * @param string $sortBy
     * @param string $direction
     *
     * @return QueryBuilder
     */
    private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
    {
        return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);
    }
}


1			<?php
2
3			namespace Wallabag\CoreBundle\Repository;
4
5			use Doctrine\ORM\EntityRepository;
6			use Doctrine\ORM\NoResultException;
7			use Doctrine\ORM\QueryBuilder;
8			use Pagerfanta\Doctrine\ORM\QueryAdapter as DoctrineORMAdapter;
9			use Pagerfanta\Pagerfanta;
10			use Wallabag\CoreBundle\Entity\Entry;
11			use Wallabag\CoreBundle\Entity\Tag;
12			use Wallabag\CoreBundle\Helper\UrlHasher;
13
14			class EntryRepository extends EntityRepository
15			{
16			/**
17			* Retrieves all entries for a user.
18			*
19			* @param int $userId
20			*
21			* @return QueryBuilder
22			*/
23			public function getBuilderForAllByUser($userId)
24			{
25			return $this
26			->getSortedQueryBuilderByUser($userId)
27			;
28			}
29
30			/**
31			* Retrieves unread entries for a user.
32			*
33			* @param int $userId
34			*
35			* @return QueryBuilder
36			*/
37			public function getBuilderForUnreadByUser($userId)
38			{
39			return $this
40			->getSortedQueryBuilderByUser($userId)
41			->andWhere('e.isArchived = false')
42			;
43			}
44
45			/**
46			* Retrieves read entries for a user.
47			*
48			* @param int $userId
49			*
50			* @return QueryBuilder
51			*/
52			public function getBuilderForArchiveByUser($userId)
53			{
54			return $this
55			->getSortedQueryBuilderByUser($userId, 'archivedAt', 'desc')
56			->andWhere('e.isArchived = true')
57			;
58			}
59
60			/**
61			* Retrieves starred entries for a user.
62			*
63			* @param int $userId
64			*
65			* @return QueryBuilder
66			*/
67			public function getBuilderForStarredByUser($userId)
68			{
69			return $this
70			->getSortedQueryBuilderByUser($userId, 'starredAt', 'desc')
71			->andWhere('e.isStarred = true')
72			;
73			}
74
75			/**
76			* Retrieves entries filtered with a search term for a user.
77			*
78			* @param int $userId
79			* @param string $term
80			* @param string $currentRoute
81			*
82			* @return QueryBuilder
83			*/
84			public function getBuilderForSearchByUser($userId, $term, $currentRoute)
85			{
86			$qb = $this
87			->getSortedQueryBuilderByUser($userId);
88
89			if ('starred' === $currentRoute) {
90			$qb->andWhere('e.isStarred = true');
91			} elseif ('unread' === $currentRoute) {
92			$qb->andWhere('e.isArchived = false');
93			} elseif ('archive' === $currentRoute) {
94			$qb->andWhere('e.isArchived = true');
95			}
96
97			// We lower() all parts here because PostgreSQL 'LIKE' verb is case-sensitive
98			$qb
99			->andWhere('lower(e.content) LIKE lower(:term) OR lower(e.title) LIKE lower(:term) OR lower(e.url) LIKE lower(:term)')->setParameter('term', '%' . $term . '%')
100			->leftJoin('e.tags', 't')
101			->groupBy('e.id');
102
103			return $qb;
104			}
105
106			/**
107			* Retrieve a sorted list of untagged entries for a user.
108			*
109			* @param int $userId
110			*
111			* @return QueryBuilder
112			*/
113			public function getBuilderForUntaggedByUser($userId)
114			{
115			return $this->sortQueryBuilder($this->getRawBuilderForUntaggedByUser($userId));
116			}
117
118			/**
119			* Retrieve untagged entries for a user.
120			*
121			* @param int $userId
122			*
123			* @return QueryBuilder
124			*/
125			public function getRawBuilderForUntaggedByUser($userId)
126			{
127			return $this->getQueryBuilderByUser($userId)
128			->leftJoin('e.tags', 't')
129			->andWhere('t.id is null');
130			}
131
132			/**
133			* Retrieve the number of untagged entries for a user.
134			*
135			* @param int $userId
136			*
137			* @return int
138			*/
139			public function countUntaggedEntriesByUser($userId)
140			{
141			return (int) $this->getRawBuilderForUntaggedByUser($userId)
142			->select('count(e.id)')
143			->getQuery()
144			->getSingleScalarResult();
145			}
146
147			/**
148			* Find Entries.
149			*
150			* @param int $userId
151			* @param bool $isArchived
152			* @param bool $isStarred
153			* @param bool $isPublic
154			* @param string $sort
155			* @param string $order
156			* @param int $since
157			* @param string $tags
158			* @param string $detail 'metadata' or 'full'. Include content field if 'full'
159			*
160			* @todo Breaking change: replace default detail=full by detail=metadata in a future version
161			*
162			* @return Pagerfanta
163			*/
164			public function findEntries($userId, $isArchived = null, $isStarred = null, $isPublic = null, $sort = 'created', $order = 'asc', $since = 0, $tags = '', $detail = 'full')
165			{
166			if (!\in_array(strtolower($detail), ['full', 'metadata'], true)) {
167			throw new \Exception('Detail "' . $detail . '" parameter is wrong, allowed: full or metadata');
168			}
169
170			$qb = $this->createQueryBuilder('e')
171			->leftJoin('e.tags', 't')
172			->where('e.user = :userId')->setParameter('userId', $userId);
173
174			if ('metadata' === $detail) {
175			$fieldNames = $this->getClassMetadata()->getFieldNames();
176			$fields = array_filter($fieldNames, function ($k) {
177			return 'content' !== $k;
178			});
179			$qb->select(sprintf('partial e.{%s}', implode(',', $fields)));
180			}
181
182			if (null !== $isArchived) {
183			$qb->andWhere('e.isArchived = :isArchived')->setParameter('isArchived', (bool) $isArchived);
184			}
185
186			if (null !== $isStarred) {
187			$qb->andWhere('e.isStarred = :isStarred')->setParameter('isStarred', (bool) $isStarred);
188			}
189
190			if (null !== $isPublic) {
191			$qb->andWhere('e.uid IS ' . (true === $isPublic ? 'NOT' : '') . ' NULL');
192			}
193
194			if ($since > 0) {
195			$qb->andWhere('e.updatedAt > :since')->setParameter('since', new \DateTime(date('Y-m-d H:i:s', $since)));
196			}
197
198			if (\is_string($tags) && '' !== $tags) {
199			foreach (explode(',', $tags) as $i => $tag) {
200			$entryAlias = 'e' . $i;
201			$tagAlias = 't' . $i;
202
203			// Complexe queries to ensure multiple tags are associated to an entry
204			// https://stackoverflow.com/a/6638146/569101
205			$qb->andWhere($qb->expr()->in(
206			'e.id',
207			$this->createQueryBuilder($entryAlias)
208			->select($entryAlias . '.id')
209			->leftJoin($entryAlias . '.tags', $tagAlias)
210			->where($tagAlias . '.label = :label' . $i)
211			->getDQL()
212			));
213
214			// bound parameter to the main query builder
215			$qb->setParameter('label' . $i, $tag);
216			}
217			}
218
219			if (!\in_array(strtolower($order), ['asc', 'desc'], true)) {
220			throw new \Exception('Order "' . $order . '" parameter is wrong, allowed: asc or desc');
221			}
222
223			if ('created' === $sort) {
224			$qb->orderBy('e.id', $order);
225			} elseif ('updated' === $sort) {
226			$qb->orderBy('e.updatedAt', $order);
227			} elseif ('archived' === $sort) {
228			$qb->orderBy('e.archivedAt', $order);
229			}
230
231			$pagerAdapter = new DoctrineORMAdapter($qb, true, false);
232
233			return new Pagerfanta($pagerAdapter);
234			}
235
236			/**
237			* Fetch an entry with a tag. Only used for tests.
238			*
239			* @param int $userId
240			*
241			* @return array
242			*/
243			public function findOneWithTags($userId)
244			{
245			$qb = $this->createQueryBuilder('e')
246			->innerJoin('e.tags', 't')
247			->innerJoin('e.user', 'u')
248			->addSelect('t', 'u')
249			->where('e.user = :userId')->setParameter('userId', $userId)
250			;
251
252			return $qb->getQuery()->getResult();
253			}
254
255			/**
256			* Find distinct language for a given user.
257			* Used to build the filter language list.
258			*
259			* @param int $userId User id
260			*
261			* @return array
262			*/
263			public function findDistinctLanguageByUser($userId)
264			{
265			$results = $this->createQueryBuilder('e')
266			->select('e.language')
267			->where('e.user = :userId')->setParameter('userId', $userId)
268			->andWhere('e.language IS NOT NULL')
269			->groupBy('e.language')
270			->orderBy('e.language', ' ASC')
271			->getQuery()
272			->getResult();
273
274			$languages = [];
275			foreach ($results as $result) {
276			$languages[$result['language']] = $result['language'];
277			}
278
279			return $languages;
280			}
281
282			/**
283			* Used only in test case to get the right entry associated to the right user.
284			*
285			* @param string $username
286			*
287			* @return Entry
288			*/
289			public function findOneByUsernameAndNotArchived($username)
290			{
291			return $this->createQueryBuilder('e')
292			->leftJoin('e.user', 'u')
293			->where('u.username = :username')->setParameter('username', $username)
294			->andWhere('e.isArchived = false')
295			->setMaxResults(1)
296			->getQuery()
297			->getSingleResult();
298			}
299
300			/**
301			* Remove a tag from all user entries.
302			*
303			* 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.
304			* 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:
305			*
306			* 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
307			*
308			* @param int $userId
309			*/
310			public function removeTag($userId, Tag $tag)
311			{
312			$entries = $this->getSortedQueryBuilderByUser($userId)
313			->innerJoin('e.tags', 't')
314			->andWhere('t.id = :tagId')->setParameter('tagId', $tag->getId())
315			->getQuery()
316			->getResult();
317
318			foreach ($entries as $entry) {
319			$entry->removeTag($tag);
320			}
321
322			$this->getEntityManager()->flush();
323			}
324
325			/**
326			* Remove tags from all user entries.
327			*
328			* @param int $userId
329			* @param Array<Tag> $tags
330			*/
331			public function removeTags($userId, $tags)
332			{
333			foreach ($tags as $tag) {
334			$this->removeTag($userId, $tag);
335			}
336			}
337
338			/**
339			* Find all entries that are attached to a give tag id.
340			*
341			* @param int $userId
342			* @param int $tagId
343			*
344			* @return array
345			*/
346			public function findAllByTagId($userId, $tagId)
347			{
348			return $this->getSortedQueryBuilderByUser($userId)
349			->innerJoin('e.tags', 't')
350			->andWhere('t.id = :tagId')->setParameter('tagId', $tagId)
351			->getQuery()
352			->getResult();
353			}
354
355			/**
356			* Find an entry by its url and its owner.
357			* If it exists, return the entry otherwise return false.
358			*
359			* @param string $url
360			* @param int $userId
361			*
362			* @return Entry\|false
363			*/
364			public function findByUrlAndUserId($url, $userId)
365			{
366			return $this->findByHashedUrlAndUserId(
367			UrlHasher::hashUrl($url),
368			$userId
369			);
370			}
371
372			/**
373			* Find all entries which have an empty value for hash.
374			*
375			* @return Entry\|false
376			*/
377			public function findByEmptyHashedUrlAndUserId(int $userId)
378			{
379			return $this->createQueryBuilder('e')
			0 ignored issues – show Bug Best Practice introduced 2020-12-22 14:13 UTC by Report Bug Copy Issue Report The expression `return $this->createQuer...getQuery()->getResult()` also could return the type `array` which is incompatible with the documented return type `Wallabag\CoreBundle\Entity\Entry\|false`. Loading history...
380			->where('e.hashedUrl = :empty')->setParameter('empty', '')
381			->orWhere('e.hashedUrl is null')
382			->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
383			->andWhere('e.url is not null')
384			->getQuery()
385			->getResult();
386			}
387
388			/**
389			* Find an entry by its hashed url and its owner.
390			* If it exists, return the entry otherwise return false.
391			*
392			* @param string $hashedUrl Url hashed using sha1
393			* @param int $userId
394			*
395			* @return Entry\|false
396			*/
397			public function findByHashedUrlAndUserId($hashedUrl, $userId)
398			{
399			// try first using hashed_url (to use the database index)
400			$res = $this->createQueryBuilder('e')
401			->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl)
402			->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
403			->getQuery()
404			->getResult();
405
406			if (\count($res)) {
407			return current($res);
408			}
409
410			// then try using hashed_given_url (to use the database index)
411			$res = $this->createQueryBuilder('e')
412			->where('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl)
413			->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
414			->getQuery()
415			->getResult();
416
417			if (\count($res)) {
418			return current($res);
419			}
420
421			return false;
422			}
423
424			public function findByUserIdAndBatchHashedUrls($userId, $hashedUrls)
425			{
426			$qb = $this->createQueryBuilder('e')->select(['e.id', 'e.hashedUrl', 'e.hashedGivenUrl']);
427			$res = $qb->where('e.user = :user_id')->setParameter('user_id', $userId)
428			->andWhere(
429			$qb->expr()->orX(
430			$qb->expr()->in('e.hashedUrl', $hashedUrls),
431			$qb->expr()->in('e.hashedGivenUrl', $hashedUrls)
432			)
433			)
434			->getQuery()
435			->getResult();
436
437			return $res;
438			}
439
440			/**
441			* Count all entries for a user.
442			*
443			* @param int $userId
444			*
445			* @return int
446			*/
447			public function countAllEntriesByUser($userId)
448			{
449			$qb = $this->createQueryBuilder('e')
450			->select('count(e)')
451			->where('e.user = :userId')->setParameter('userId', $userId)
452			;
453
454			return (int) $qb->getQuery()->getSingleScalarResult();
455			}
456
457			/**
458			* Remove all entries for a user id.
459			* Used when a user want to reset all informations.
460			*
461			* @param int $userId
462			*/
463			public function removeAllByUserId($userId)
464			{
465			$this->getEntityManager()
466			->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId')
467			->setParameter('userId', $userId)
468			->execute();
469			}
470
471			public function removeArchivedByUserId($userId)
472			{
473			$this->getEntityManager()
474			->createQuery('DELETE FROM Wallabag\CoreBundle\Entity\Entry e WHERE e.user = :userId AND e.isArchived = TRUE')
475			->setParameter('userId', $userId)
476			->execute();
477			}
478
479			/**
480			* Get id and url from all entries
481			* Used for the clean-duplicates command.
482			*/
483			public function findAllEntriesIdAndUrlByUserId($userId)
484			{
485			$qb = $this->createQueryBuilder('e')
486			->select('e.id, e.url')
487			->where('e.user = :userid')->setParameter(':userid', $userId);
488
489			return $qb->getQuery()->getArrayResult();
490			}
491
492			/**
493			* @param int $userId
494			*
495			* @return array
496			*/
497			public function findAllEntriesIdByUserId($userId = null)
498			{
499			$qb = $this->createQueryBuilder('e')
500			->select('e.id');
501
502			if (null !== $userId) {
503			$qb->where('e.user = :userid')->setParameter(':userid', $userId);
504			}
505
506			return $qb->getQuery()->getArrayResult();
507			}
508
509			/**
510			* Find all entries by url and owner.
511			*
512			* @param string $url
513			* @param int $userId
514			*
515			* @return array
516			*/
517			public function findAllByUrlAndUserId($url, $userId)
518			{
519			return $this->createQueryBuilder('e')
520			->where('e.url = :url')->setParameter('url', urldecode($url))
521			->andWhere('e.user = :user_id')->setParameter('user_id', $userId)
522			->getQuery()
523			->getResult();
524			}
525
526			/**
527			* Returns a random entry, filtering by status.
528			*
529			* @param int $userId
530			* @param string $type Can be unread, archive, starred, etc
531			*
532			* @throws NoResultException
533			*
534			* @return Entry
535			*/
536			public function getRandomEntry($userId, $type = '')
537			{
538			$qb = $this->getQueryBuilderByUser($userId)
539			->select('e.id');
540
541			switch ($type) {
542			case 'unread':
543			$qb->andWhere('e.isArchived = false');
544			break;
545			case 'archive':
546			$qb->andWhere('e.isArchived = true');
547			break;
548			case 'starred':
549			$qb->andWhere('e.isStarred = true');
550			break;
551			case 'untagged':
552			$qb->leftJoin('e.tags', 't');
553			$qb->andWhere('t.id is null');
554			break;
555			}
556
557			$ids = $qb->getQuery()->getArrayResult();
558
559			if (empty($ids)) {
560			throw new NoResultException();
561			}
562
563			// random select one in the list
564			$randomId = $ids[mt_rand(0, \count($ids) - 1)]['id'];
565
566			return $this->find($randomId);
567			}
568
569			/**
570			* Return a query builder to be used by other getBuilderFor* method.
571			*
572			* @param int $userId
573			*
574			* @return QueryBuilder
575			*/
576			private function getQueryBuilderByUser($userId)
577			{
578			return $this->createQueryBuilder('e')
579			->andWhere('e.user = :userId')->setParameter('userId', $userId);
580			}
581
582			/**
583			* Return a sorted query builder to be used by other getBuilderFor* method.
584			*
585			* @param int $userId
586			* @param string $sortBy
587			* @param string $direction
588			*
589			* @return QueryBuilder
590			*/
591			private function getSortedQueryBuilderByUser($userId, $sortBy = 'createdAt', $direction = 'desc')
592			{
593			return $this->sortQueryBuilder($this->getQueryBuilderByUser($userId), $sortBy, $direction);
594			}
595
596			/**
597			* Return the given QueryBuilder with an orderBy() call.
598			*
599			* @param string $sortBy
600			* @param string $direction
601			*
602			* @return QueryBuilder
603			*/
604			private function sortQueryBuilder(QueryBuilder $qb, $sortBy = 'createdAt', $direction = 'desc')
605			{
606			return $qb->orderBy(sprintf('e.%s', $sortBy), $direction);
607			}
608			}
609

wallabag / wallabag

Push — master ( dc6e1a...d7a3f7 )

EntryRepository::findByUserIdAndBatchHashedUrls() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like