ShortUrlRepository::createFindOneQueryBuilder() - Code Metrics - shlinkio/shlink - Measure and Improve Code Quality continuously with Scrutinizer

ShortUrlRepository::createFindOneQueryBuilder() A
last analyzed 2020-12-13 17:12 UTC

↳ Parent: ShortUrlRepository

Complexity

Conditions	1
Paths	1

Size

Total Lines	12
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	8
CRAP Score	1

Importance

Changes

Metric	Value
eloc	8
dl	0
loc	12
ccs	8
cts	8
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	2
crap	1

<?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\Doctrine\Type\ChronosDateTimeType;
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 Shlinkio\Shlink\Importer\Model\ImportedShlinkUrl;

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')
           ->setMaxResults($limit)
           ->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(), ChronosDateTimeType::CHRONOS_DATETIME);
        }
        if ($dateRange !== null && $dateRange->getEndDate() !== null) {
            $qb->andWhere($qb->expr()->lte('s.dateCreated', ':endDate'));
            $qb->setParameter('endDate', $dateRange->getEndDate(), ChronosDateTimeType::CHRONOS_DATETIME);
        }

        // 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);

        $this->whereDomainIs($qb, $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(), ChronosDateTimeType::CHRONOS_DATETIME);
        }
        if ($meta->hasValidUntil()) {
            $qb->andWhere($qb->expr()->eq('s.validUntil', ':validUntil'))
               ->setParameter('validUntil', $meta->getValidUntil(), ChronosDateTimeType::CHRONOS_DATETIME);
        }
        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();
    }

    public function importedUrlExists(ImportedShlinkUrl $url): bool
    {
        $qb = $this->getEntityManager()->createQueryBuilder();
        $qb->select('COUNT(DISTINCT s.id)')
           ->from(ShortUrl::class, 's')
           ->andWhere($qb->expr()->eq('s.importOriginalShortCode', ':shortCode'))
           ->setParameter('shortCode', $url->shortCode())
           ->andWhere($qb->expr()->eq('s.importSource', ':importSource'))
           ->setParameter('importSource', $url->source())
           ->setMaxResults(1);

        $this->whereDomainIs($qb, $url->domain());

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

    private function whereDomainIs(QueryBuilder $qb, ?string $domain): void
    {
        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'));
        }
    }
}


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

shlinkio / shlink

ShortUrlRepository::createFindOneQueryBuilder() A last analyzed 2020-12-13 17:12 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

ShortUrlRepository::createFindOneQueryBuilder() A
last analyzed 2020-12-13 17:12 UTC