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

Completed

Push — develop ( e18187...f7d54a )

by Alejandro

created 2020-02-02 18:28 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\QueryBuilder;
use Shlinkio\Shlink\Common\Util\DateRange;
use Shlinkio\Shlink\Core\Entity\ShortUrl;
use Shlinkio\Shlink\Core\Model\ShortUrlsOrdering;

use function array_column;
use function array_key_exists;
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->andWhere($qb->expr()->orX(
                $qb->expr()->like('s.longUrl', ':searchPattern'),
                $qb->expr()->like('s.shortCode', ':searchPattern'),
                $qb->expr()->like('t.name', ':searchPattern'),
            ));
            $qb->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;
    }
}


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\QueryBuilder;
9		use Shlinkio\Shlink\Common\Util\DateRange;
10		use Shlinkio\Shlink\Core\Entity\ShortUrl;
11		use Shlinkio\Shlink\Core\Model\ShortUrlsOrdering;
12
13		use function array_column;
14		use function array_key_exists;
15		use function Functional\contains;
16
17		class ShortUrlRepository extends EntityRepository implements ShortUrlRepositoryInterface
18		{
19		/**
20		* @param string[] $tags
21		* @return ShortUrl[]
22		*/
23	2	public function findList(
24		?int $limit = null,
25		?int $offset = null,
26		?string $searchTerm = null,
27		array $tags = [],
28		?ShortUrlsOrdering $orderBy = null,
29		?DateRange $dateRange = null
30		): array {
31	2	$qb = $this->createListQueryBuilder($searchTerm, $tags, $dateRange);
32	2	$qb->select('DISTINCT s');
33
34		// Set limit and offset
35	2	if ($limit !== null) {
36	1	$qb->setMaxResults($limit);
37		}
38	2	if ($offset !== null) {
39	1	$qb->setFirstResult($offset);
40		}
41
42		// In case the ordering has been specified, the query could be more complex. Process it
43	2	if ($orderBy !== null && $orderBy->hasOrderField()) {
44	2	return $this->processOrderByForList($qb, $orderBy);
45		}
46
47		// With no order by, order by date and just return the list of ShortUrls
48	1	$qb->orderBy('s.dateCreated');
49	1	return $qb->getQuery()->getResult();
50		}
51
52	2	private function processOrderByForList(QueryBuilder $qb, ShortUrlsOrdering $orderBy): array
53		{
54	2	$fieldName = $orderBy->orderField();
55	2	$order = $orderBy->orderDirection();
56
57	2	if (contains(['visits', 'visitsCount', 'visitCount'], $fieldName)) {
58	1	$qb->addSelect('COUNT(DISTINCT v) AS totalVisits')
59	1	->leftJoin('s.visits', 'v')
60	1	->groupBy('s')
61	1	->orderBy('totalVisits', $order);
62
63	1	return array_column($qb->getQuery()->getResult(), 0);
64		}
65
66		// Map public field names to column names
67		$fieldNameMap = [
68	1	'originalUrl' => 'longUrl',
69		'longUrl' => 'longUrl',
70		'shortCode' => 'shortCode',
71		'dateCreated' => 'dateCreated',
72		];
73	1	if (array_key_exists($fieldName, $fieldNameMap)) {
74	1	$qb->orderBy('s.' . $fieldNameMap[$fieldName], $order);
75		}
76	1	return $qb->getQuery()->getResult();
77		}
78
79	2	public function countList(?string $searchTerm = null, array $tags = [], ?DateRange $dateRange = null): int
80		{
81	2	$qb = $this->createListQueryBuilder($searchTerm, $tags, $dateRange);
82	2	$qb->select('COUNT(DISTINCT s)');
83
84	2	return (int) $qb->getQuery()->getSingleScalarResult();
85		}
86
87	3	private function createListQueryBuilder(
88		?string $searchTerm = null,
89		array $tags = [],
90		?DateRange $dateRange = null
91		): QueryBuilder {
92	3	$qb = $this->getEntityManager()->createQueryBuilder();
93	3	$qb->from(ShortUrl::class, 's')
94	3	->where('1=1');
95
96	3	if ($dateRange !== null && $dateRange->getStartDate() !== null) {
97	1	$qb->andWhere($qb->expr()->gte('s.dateCreated', ':startDate'));
98	1	$qb->setParameter('startDate', $dateRange->getStartDate());
99		}
100	3	if ($dateRange !== null && $dateRange->getEndDate() !== null) {
101	1	$qb->andWhere($qb->expr()->lte('s.dateCreated', ':endDate'));
102	1	$qb->setParameter('endDate', $dateRange->getEndDate());
103		}
104
105		// Apply search term to every searchable field if not empty
106	3	if (! empty($searchTerm)) {
107		// Left join with tags only if no tags were provided. In case of tags, an inner join will be done later
108	1	if (empty($tags)) {
109		$qb->leftJoin('s.tags', 't');
110		}
111
112		// Apply search conditions
113	1	$qb->andWhere($qb->expr()->orX(
114	1	$qb->expr()->like('s.longUrl', ':searchPattern'),
115	1	$qb->expr()->like('s.shortCode', ':searchPattern'),
116	1	$qb->expr()->like('t.name', ':searchPattern'),
117		));
118	1	$qb->setParameter('searchPattern', '%' . $searchTerm . '%');
119		}
120
121		// Filter by tags if provided
122	3	if (! empty($tags)) {
123	1	$qb->join('s.tags', 't')
124	1	->andWhere($qb->expr()->in('t.name', $tags));
125		}
126
127	3	return $qb;
128		}
129
130	1	public function findOneWithDomainFallback(string $shortCode, ?string $domain = null): ?ShortUrl
131		{
132		// When ordering DESC, Postgres puts nulls at the beginning while the rest of supported DB engines put them at
133		// the bottom
134	1	$dbPlatform = $this->getEntityManager()->getConnection()->getDatabasePlatform()->getName();
135	1	$ordering = $dbPlatform === 'postgresql' ? 'ASC' : 'DESC';
136
137		$dql = <<<DQL
138		SELECT s
139		FROM Shlinkio\Shlink\Core\Entity\ShortUrl AS s
140		LEFT JOIN s.domain AS d
141		WHERE s.shortCode = :shortCode
142		AND (s.domain IS NULL OR d.authority = :domain)
143	1	ORDER BY s.domain {$ordering}
144		DQL;
145
146	1	$query = $this->getEntityManager()->createQuery($dql);
147	1	$query->setMaxResults(1)
148	1	->setParameters([
149	1	'shortCode' => $shortCode,
150	1	'domain' => $domain,
151		]);
152
153		// Since we ordered by domain, we will have first the URL matching provided domain, followed by the one
154		// with no domain (if any), so it is safe to fetch 1 max result and we will get:
155		// * The short URL matching both the short code and the domain, or
156		// * The short URL matching the short code but without any domain, or
157		// * No short URL at all
158
159	1	return $query->getOneOrNullResult();
160		}
161
162	1	public function findOne(string $shortCode, ?string $domain = null): ?ShortUrl
163		{
164	1	$qb = $this->createFindOneQueryBuilder($shortCode, $domain);
165	1	$qb->select('s');
166
167	1	return $qb->getQuery()->getOneOrNullResult();
168		}
169
170	1	public function shortCodeIsInUse(string $slug, ?string $domain = null): bool
171		{
172	1	$qb = $this->createFindOneQueryBuilder($slug, $domain);
173	1	$qb->select('COUNT(DISTINCT s.id)');
174
175	1	return ((int) $qb->getQuery()->getSingleScalarResult()) > 0;
176		}
177
178	2	private function createFindOneQueryBuilder(string $slug, ?string $domain = null): QueryBuilder
179		{
180	2	$qb = $this->getEntityManager()->createQueryBuilder();
181	2	$qb->from(ShortUrl::class, 's')
182	2	->where($qb->expr()->isNotNull('s.shortCode'))
183	2	->andWhere($qb->expr()->eq('s.shortCode', ':slug'))
184	2	->setParameter('slug', $slug)
185	2	->setMaxResults(1);
186
187	2	if ($domain !== null) {
188	2	$qb->join('s.domain', 'd')
189	2	->andWhere($qb->expr()->eq('d.authority', ':authority'))
190	2	->setParameter('authority', $domain);
191		} else {
192	2	$qb->andWhere($qb->expr()->isNull('s.domain'));
193		}
194
195	2	return $qb;
196		}
197		}
198

shlinkio / shlink

Push — develop ( e18187...f7d54a )

ShortUrlRepository::createFindOneQueryBuilder() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like