ShortUrlRepository::createFindOneQueryBuilder() - Code Metrics - Inspection of "Merge pull request #842 from acelaya-forks/feature..." - shlinkio/shlink - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — develop ( 8d438a...1f78f5 )

by Alejandro

created 2020-09-23 06:01 UTC

ShortUrlRepository::createFindOneQueryBuilder() A

↳ Parent: ShortUrlRepository

Complexity

Conditions	2
Paths	2

Size

Total Lines	18
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	13
CRAP Score	2

Importance

Changes

Metric	Value
cc	2
eloc	13
c	0
b	0
f	0
nc	2
nop	2
dl	0
loc	18
rs	9.8333
ccs	13
cts	13
cp	1
crap	2

<?php

declare(strict_types=1);

namespace Shlinkio\Shlink\Core\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Shlinkio\Shlink\Common\Util\DateRange;
use Shlinkio\Shlink\Core\Entity\ShortUrl;
use Shlinkio\Shlink\Core\Model\ShortUrlMeta;
use Shlinkio\Shlink\Core\Model\ShortUrlsOrdering;

use function array_column;
use function array_key_exists;
use function count;
use function Functional\contains;

class ShortUrlRepository extends EntityRepository implements ShortUrlRepositoryInterface
{
    /**
     * @param string[] $tags
     * @return ShortUrl[]
     */
    public function findList(
        ?int $limit = null,
        ?int $offset = null,
        ?string $searchTerm = null,
        array $tags = [],
        ?ShortUrlsOrdering $orderBy = null,
        ?DateRange $dateRange = null
    ): array {
        $qb = $this->createListQueryBuilder($searchTerm, $tags, $dateRange);
        $qb->select('DISTINCT s');

        // Set limit and offset
        if ($limit !== null) {
            $qb->setMaxResults($limit);
        }
        if ($offset !== null) {
            $qb->setFirstResult($offset);
        }

        // In case the ordering has been specified, the query could be more complex. Process it
        if ($orderBy !== null && $orderBy->hasOrderField()) {
            return $this->processOrderByForList($qb, $orderBy);
        }

        // With no order by, order by date and just return the list of ShortUrls
        $qb->orderBy('s.dateCreated');
        return $qb->getQuery()->getResult();
    }

    private function processOrderByForList(QueryBuilder $qb, ShortUrlsOrdering $orderBy): array
    {
        $fieldName = $orderBy->orderField();
        $order = $orderBy->orderDirection();

        if (contains(['visits', 'visitsCount', 'visitCount'], $fieldName)) {
            $qb->addSelect('COUNT(DISTINCT v) AS totalVisits')
               ->leftJoin('s.visits', 'v')
               ->groupBy('s')
               ->orderBy('totalVisits', $order);

            return array_column($qb->getQuery()->getResult(), 0);
        }

        // Map public field names to column names
        $fieldNameMap = [
            'originalUrl' => 'longUrl',
            'longUrl' => 'longUrl',
            'shortCode' => 'shortCode',
            'dateCreated' => 'dateCreated',
        ];
        if (array_key_exists($fieldName, $fieldNameMap)) {
            $qb->orderBy('s.' . $fieldNameMap[$fieldName], $order);
        }
        return $qb->getQuery()->getResult();
    }

    public function countList(?string $searchTerm = null, array $tags = [], ?DateRange $dateRange = null): int
    {
        $qb = $this->createListQueryBuilder($searchTerm, $tags, $dateRange);
        $qb->select('COUNT(DISTINCT s)');

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

    private function createListQueryBuilder(
        ?string $searchTerm = null,
        array $tags = [],
        ?DateRange $dateRange = null
    ): QueryBuilder {
        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb->from(ShortUrl::class, 's')
           ->where('1=1');

        if ($dateRange !== null && $dateRange->getStartDate() !== null) {
            $qb->andWhere($qb->expr()->gte('s.dateCreated', ':startDate'));
            $qb->setParameter('startDate', $dateRange->getStartDate());
        }
        if ($dateRange !== null && $dateRange->getEndDate() !== null) {
            $qb->andWhere($qb->expr()->lte('s.dateCreated', ':endDate'));
            $qb->setParameter('endDate', $dateRange->getEndDate());
        }

        // Apply search term to every searchable field if not empty
        if (! empty($searchTerm)) {
            // Left join with tags only if no tags were provided. In case of tags, an inner join will be done later
            if (empty($tags)) {
                $qb->leftJoin('s.tags', 't');
            }

            // Apply search conditions
            $qb->leftJoin('s.domain', 'd')
               ->andWhere($qb->expr()->orX(
                   $qb->expr()->like('s.longUrl', ':searchPattern'),
                   $qb->expr()->like('s.shortCode', ':searchPattern'),
                   $qb->expr()->like('t.name', ':searchPattern'),
                   $qb->expr()->like('d.authority', ':searchPattern'),
               ))
               ->setParameter('searchPattern', '%' . $searchTerm . '%');
        }

        // Filter by tags if provided
        if (! empty($tags)) {
            $qb->join('s.tags', 't')
               ->andWhere($qb->expr()->in('t.name', $tags));
        }

        return $qb;
    }

    public function findOneWithDomainFallback(string $shortCode, ?string $domain = null): ?ShortUrl
    {
        // When ordering DESC, Postgres puts nulls at the beginning while the rest of supported DB engines put them at
        // the bottom
        $dbPlatform = $this->getEntityManager()->getConnection()->getDatabasePlatform()->getName();
        $ordering = $dbPlatform === 'postgresql' ? 'ASC' : 'DESC';

        $dql = <<<DQL
            SELECT s
              FROM Shlinkio\Shlink\Core\Entity\ShortUrl AS s
         LEFT JOIN s.domain AS d
             WHERE s.shortCode = :shortCode
               AND (s.domain IS NULL OR d.authority = :domain)
          ORDER BY s.domain {$ordering}
DQL;

        $query = $this->getEntityManager()->createQuery($dql);
        $query->setMaxResults(1)
              ->setParameters([
                  'shortCode' => $shortCode,
                  'domain' => $domain,
              ]);

        // Since we ordered by domain, we will have first the URL matching provided domain, followed by the one
        // with no domain (if any), so it is safe to fetch 1 max result and we will get:
        //  * The short URL matching both the short code and the domain, or
        //  * The short URL matching the short code but without any domain, or
        //  * No short URL at all

        return $query->getOneOrNullResult();
    }

    public function findOne(string $shortCode, ?string $domain = null): ?ShortUrl
    {
        $qb = $this->createFindOneQueryBuilder($shortCode, $domain);
        $qb->select('s');

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

    public function shortCodeIsInUse(string $slug, ?string $domain = null): bool
    {
        $qb = $this->createFindOneQueryBuilder($slug, $domain);
        $qb->select('COUNT(DISTINCT s.id)');

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

    private function createFindOneQueryBuilder(string $slug, ?string $domain = null): QueryBuilder
    {
        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb->from(ShortUrl::class, 's')
           ->where($qb->expr()->isNotNull('s.shortCode'))
           ->andWhere($qb->expr()->eq('s.shortCode', ':slug'))
           ->setParameter('slug', $slug)
           ->setMaxResults(1);

        if ($domain !== null) {
            $qb->join('s.domain', 'd')
               ->andWhere($qb->expr()->eq('d.authority', ':authority'))
               ->setParameter('authority', $domain);
        } else {
            $qb->andWhere($qb->expr()->isNull('s.domain'));
        }

        return $qb;
    }

    public function findOneMatching(string $url, array $tags, ShortUrlMeta $meta): ?ShortUrl
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        $qb->select('s')
           ->from(ShortUrl::class, 's')
           ->where($qb->expr()->eq('s.longUrl', ':longUrl'))
           ->setParameter('longUrl', $url)
           ->setMaxResults(1)
           ->orderBy('s.id');

        if ($meta->hasCustomSlug()) {
            $qb->andWhere($qb->expr()->eq('s.shortCode', ':slug'))
               ->setParameter('slug', $meta->getCustomSlug());
        }
        if ($meta->hasMaxVisits()) {
            $qb->andWhere($qb->expr()->eq('s.maxVisits', ':maxVisits'))
               ->setParameter('maxVisits', $meta->getMaxVisits());
        }
        if ($meta->hasValidSince()) {
            $qb->andWhere($qb->expr()->eq('s.validSince', ':validSince'))
               ->setParameter('validSince', $meta->getValidSince());
        }
        if ($meta->hasValidUntil()) {
            $qb->andWhere($qb->expr()->eq('s.validUntil', ':validUntil'))
                ->setParameter('validUntil', $meta->getValidUntil());
        }

        if ($meta->hasDomain()) {
            $qb->join('s.domain', 'd')
               ->andWhere($qb->expr()->eq('d.authority', ':domain'))
               ->setParameter('domain', $meta->getDomain());
        }

        $tagsAmount = count($tags);
        if ($tagsAmount === 0) {
            return $qb->getQuery()->getOneOrNullResult();
        }

        foreach ($tags as $index => $tag) {
            $alias = 't_' . $index;
            $qb->join('s.tags', $alias, Join::WITH, $alias . '.name = :tag' . $index)
               ->setParameter('tag' . $index, $tag);
        }

        // If tags where provided, we need an extra join to see the amount of tags that every short URL has, so that we
        // can discard those that also have more tags, making sure only those fully matching are included.
        $qb->join('s.tags', 't')
           ->groupBy('s')
           ->having($qb->expr()->eq('COUNT(t.id)', ':tagsAmount'))
           ->setParameter('tagsAmount', $tagsAmount);

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


1		<?php
2
3		declare(strict_types=1);
4
5		namespace Shlinkio\Shlink\Core\Repository;
6
7		use Doctrine\ORM\EntityRepository;
8		use Doctrine\ORM\Query\Expr\Join;
9		use Doctrine\ORM\QueryBuilder;
10		use Shlinkio\Shlink\Common\Util\DateRange;
11		use Shlinkio\Shlink\Core\Entity\ShortUrl;
12		use Shlinkio\Shlink\Core\Model\ShortUrlMeta;
13		use Shlinkio\Shlink\Core\Model\ShortUrlsOrdering;
14
15		use function array_column;
16		use function array_key_exists;
17		use function count;
18		use function Functional\contains;
19
20		class ShortUrlRepository extends EntityRepository implements ShortUrlRepositoryInterface
21		{
22		/**
23		* @param string[] $tags
24		* @return ShortUrl[]
25		*/
26	2	public function findList(
27		?int $limit = null,
28		?int $offset = null,
29		?string $searchTerm = null,
30		array $tags = [],
31		?ShortUrlsOrdering $orderBy = null,
32		?DateRange $dateRange = null
33		): array {
34	2	$qb = $this->createListQueryBuilder($searchTerm, $tags, $dateRange);
35	2	$qb->select('DISTINCT s');
36
37		// Set limit and offset
38	2	if ($limit !== null) {
39	1	$qb->setMaxResults($limit);
40		}
41	2	if ($offset !== null) {
42	1	$qb->setFirstResult($offset);
43		}
44
45		// In case the ordering has been specified, the query could be more complex. Process it
46	2	if ($orderBy !== null && $orderBy->hasOrderField()) {
47	2	return $this->processOrderByForList($qb, $orderBy);
48		}
49
50		// With no order by, order by date and just return the list of ShortUrls
51	1	$qb->orderBy('s.dateCreated');
52	1	return $qb->getQuery()->getResult();
53		}
54
55	2	private function processOrderByForList(QueryBuilder $qb, ShortUrlsOrdering $orderBy): array
56		{
57	2	$fieldName = $orderBy->orderField();
58	2	$order = $orderBy->orderDirection();
59
60	2	if (contains(['visits', 'visitsCount', 'visitCount'], $fieldName)) {
61	1	$qb->addSelect('COUNT(DISTINCT v) AS totalVisits')
62	1	->leftJoin('s.visits', 'v')
63	1	->groupBy('s')
64	1	->orderBy('totalVisits', $order);
65
66	1	return array_column($qb->getQuery()->getResult(), 0);
67		}
68
69		// Map public field names to column names
70		$fieldNameMap = [
71	1	'originalUrl' => 'longUrl',
72		'longUrl' => 'longUrl',
73		'shortCode' => 'shortCode',
74		'dateCreated' => 'dateCreated',
75		];
76	1	if (array_key_exists($fieldName, $fieldNameMap)) {
77	1	$qb->orderBy('s.' . $fieldNameMap[$fieldName], $order);
78		}
79	1	return $qb->getQuery()->getResult();
80		}
81
82	2	public function countList(?string $searchTerm = null, array $tags = [], ?DateRange $dateRange = null): int
83		{
84	2	$qb = $this->createListQueryBuilder($searchTerm, $tags, $dateRange);
85	2	$qb->select('COUNT(DISTINCT s)');
86
87	2	return (int) $qb->getQuery()->getSingleScalarResult();
88		}
89
90	3	private function createListQueryBuilder(
91		?string $searchTerm = null,
92		array $tags = [],
93		?DateRange $dateRange = null
94		): QueryBuilder {
95	3	$qb = $this->getEntityManager()->createQueryBuilder();
96	3	$qb->from(ShortUrl::class, 's')
97	3	->where('1=1');
98
99	3	if ($dateRange !== null && $dateRange->getStartDate() !== null) {
100	1	$qb->andWhere($qb->expr()->gte('s.dateCreated', ':startDate'));
101	1	$qb->setParameter('startDate', $dateRange->getStartDate());
102		}
103	3	if ($dateRange !== null && $dateRange->getEndDate() !== null) {
104	1	$qb->andWhere($qb->expr()->lte('s.dateCreated', ':endDate'));
105	1	$qb->setParameter('endDate', $dateRange->getEndDate());
106		}
107
108		// Apply search term to every searchable field if not empty
109	3	if (! empty($searchTerm)) {
110		// Left join with tags only if no tags were provided. In case of tags, an inner join will be done later
111	1	if (empty($tags)) {
112		$qb->leftJoin('s.tags', 't');
113		}
114
115		// Apply search conditions
116	1	$qb->leftJoin('s.domain', 'd')
117	1	->andWhere($qb->expr()->orX(
118	1	$qb->expr()->like('s.longUrl', ':searchPattern'),
119	1	$qb->expr()->like('s.shortCode', ':searchPattern'),
120	1	$qb->expr()->like('t.name', ':searchPattern'),
121	1	$qb->expr()->like('d.authority', ':searchPattern'),
122		))
123	1	->setParameter('searchPattern', '%' . $searchTerm . '%');
124		}
125
126		// Filter by tags if provided
127	3	if (! empty($tags)) {
128	1	$qb->join('s.tags', 't')
129	1	->andWhere($qb->expr()->in('t.name', $tags));
130		}
131
132	3	return $qb;
133		}
134
135	1	public function findOneWithDomainFallback(string $shortCode, ?string $domain = null): ?ShortUrl
136		{
137		// When ordering DESC, Postgres puts nulls at the beginning while the rest of supported DB engines put them at
138		// the bottom
139	1	$dbPlatform = $this->getEntityManager()->getConnection()->getDatabasePlatform()->getName();
140	1	$ordering = $dbPlatform === 'postgresql' ? 'ASC' : 'DESC';
141
142		$dql = <<<DQL
143		SELECT s
144		FROM Shlinkio\Shlink\Core\Entity\ShortUrl AS s
145		LEFT JOIN s.domain AS d
146		WHERE s.shortCode = :shortCode
147		AND (s.domain IS NULL OR d.authority = :domain)
148	1	ORDER BY s.domain {$ordering}
149		DQL;
150
151	1	$query = $this->getEntityManager()->createQuery($dql);
152	1	$query->setMaxResults(1)
153	1	->setParameters([
154	1	'shortCode' => $shortCode,
155	1	'domain' => $domain,
156		]);
157
158		// Since we ordered by domain, we will have first the URL matching provided domain, followed by the one
159		// with no domain (if any), so it is safe to fetch 1 max result and we will get:
160		// * The short URL matching both the short code and the domain, or
161		// * The short URL matching the short code but without any domain, or
162		// * No short URL at all
163
164	1	return $query->getOneOrNullResult();
165		}
166
167	3	public function findOne(string $shortCode, ?string $domain = null): ?ShortUrl
168		{
169	3	$qb = $this->createFindOneQueryBuilder($shortCode, $domain);
170	3	$qb->select('s');
171
172	3	return $qb->getQuery()->getOneOrNullResult();
173		}
174
175	1	public function shortCodeIsInUse(string $slug, ?string $domain = null): bool
176		{
177	1	$qb = $this->createFindOneQueryBuilder($slug, $domain);
178	1	$qb->select('COUNT(DISTINCT s.id)');
179
180	1	return ((int) $qb->getQuery()->getSingleScalarResult()) > 0;
181		}
182
183	4	private function createFindOneQueryBuilder(string $slug, ?string $domain = null): QueryBuilder
184		{
185	4	$qb = $this->getEntityManager()->createQueryBuilder();
186	4	$qb->from(ShortUrl::class, 's')
187	4	->where($qb->expr()->isNotNull('s.shortCode'))
188	4	->andWhere($qb->expr()->eq('s.shortCode', ':slug'))
189	4	->setParameter('slug', $slug)
190	4	->setMaxResults(1);
191
192	4	if ($domain !== null) {
193	4	$qb->join('s.domain', 'd')
194	4	->andWhere($qb->expr()->eq('d.authority', ':authority'))
195	4	->setParameter('authority', $domain);
196		} else {
197	4	$qb->andWhere($qb->expr()->isNull('s.domain'));
198		}
199
200	4	return $qb;
201		}
202
203	3	public function findOneMatching(string $url, array $tags, ShortUrlMeta $meta): ?ShortUrl
204		{
205	3	$qb = $this->getEntityManager()->createQueryBuilder();
206
207	3	$qb->select('s')
208	3	->from(ShortUrl::class, 's')
209	3	->where($qb->expr()->eq('s.longUrl', ':longUrl'))
210	3	->setParameter('longUrl', $url)
211	3	->setMaxResults(1)
212	3	->orderBy('s.id');
213
214	3	if ($meta->hasCustomSlug()) {
215	2	$qb->andWhere($qb->expr()->eq('s.shortCode', ':slug'))
216	2	->setParameter('slug', $meta->getCustomSlug());
217		}
218	3	if ($meta->hasMaxVisits()) {
219	2	$qb->andWhere($qb->expr()->eq('s.maxVisits', ':maxVisits'))
220	2	->setParameter('maxVisits', $meta->getMaxVisits());
221		}
222	3	if ($meta->hasValidSince()) {
223	3	$qb->andWhere($qb->expr()->eq('s.validSince', ':validSince'))
224	3	->setParameter('validSince', $meta->getValidSince());
225		}
226	3	if ($meta->hasValidUntil()) {
227	1	$qb->andWhere($qb->expr()->eq('s.validUntil', ':validUntil'))
228	1	->setParameter('validUntil', $meta->getValidUntil());
229		}
230
231	3	if ($meta->hasDomain()) {
232	1	$qb->join('s.domain', 'd')
233	1	->andWhere($qb->expr()->eq('d.authority', ':domain'))
234	1	->setParameter('domain', $meta->getDomain());
235		}
236
237	3	$tagsAmount = count($tags);
238	3	if ($tagsAmount === 0) {
239	2	return $qb->getQuery()->getOneOrNullResult();
240		}
241
242	3	foreach ($tags as $index => $tag) {
243	3	$alias = 't_' . $index;
244	3	$qb->join('s.tags', $alias, Join::WITH, $alias . '.name = :tag' . $index)
245	3	->setParameter('tag' . $index, $tag);
246		}
247
248		// If tags where provided, we need an extra join to see the amount of tags that every short URL has, so that we
249		// can discard those that also have more tags, making sure only those fully matching are included.
250	3	$qb->join('s.tags', 't')
251	3	->groupBy('s')
252	3	->having($qb->expr()->eq('COUNT(t.id)', ':tagsAmount'))
253	3	->setParameter('tagsAmount', $tagsAmount);
254
255	3	return $qb->getQuery()->getOneOrNullResult();
256		}
257		}
258

shlinkio / shlink

Push — develop ( 8d438a...1f78f5 )

ShortUrlRepository::createFindOneQueryBuilder() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like