MovieRepository::findAllByIds() - Code Metrics - Inspection of "Speed up for user recommendations page (Query opti..." - svbackend/my-art-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d44c39...321b0a )

by Valentyn

created 2018-10-19 18:54 UTC

MovieRepository::findAllByIds() A

↳ Parent: MovieRepository

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	2

Importance

Changes

Metric	Value
dl	0
loc	10
ccs	0
cts	7
cp	0
rs	9.9332
c	0
b	0
f	0
cc	1
nc	1
nop	1
crap	2

<?php

declare(strict_types=1);

namespace App\Movies\Repository;

use App\Guests\Entity\GuestSession;
use App\Movies\Entity\Movie;
use App\Users\Entity\User;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Symfony\Bridge\Doctrine\RegistryInterface;

/**
 * @method Movie|null find($id, $lockMode = null, $lockVersion = null)
 * @method Movie|null findOneBy(array $criteria, array $orderBy = null)
 * @method Movie[]    findAll()
 * @method Movie[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class MovieRepository extends ServiceEntityRepository
{
    public function __construct(RegistryInterface $registry)
    {
        parent::__construct($registry, Movie::class);
    }

    private function getBaseQuery(): QueryBuilder
    {
        return $this->createQueryBuilder('m')
            ->leftJoin('m.translations', 'mt', null, null, 'mt.locale')
            ->addSelect('mt')
            ->leftJoin('m.genres', 'mg')
            ->addSelect('mg')
            ->leftJoin('mg.translations', 'mgt', null, null, 'mgt.locale')
            ->addSelect('mgt');
    }

    /**
     * @param int       $id
     * @param User|null $user
     *
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     *
     * @return Movie|null
     */
    public function findOneForMoviePage(int $id, ?User $user = null): ?Movie
    {
        if ($user === null) {
            return $this->getBaseQuery()
                ->where('m.id = :id')
                ->setParameter('id', $id)
                ->getQuery()
                ->getSingleResult();
        }

        $result = $this->getBaseQuery()
            ->where('m.id = :id')
            ->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
            ->addSelect('uwm')
            ->leftJoin('m.userRecommendedMovie', 'urm', 'WITH', 'urm.user = :user_id AND urm.originalMovie = :id')
            ->addSelect('urm')
            ->leftJoin('m.userInterestedMovie', 'uim', 'WITH', 'uim.user = :user_id AND uim.movie = :id')
            ->addSelect('uim')
            ->setParameter('user_id', $user->getId())
            ->setParameter('id', $id)
            ->getQuery()
            ->getSingleResult();

        return $result;
    }

    public function findAllByIdsWithFlags(array $ids, int $userId, int $originalMovieId)
    {
        $result = $this->getBaseQuery()
            ->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id') // if this relation exists then user has already watched this movie
            ->addSelect('uwm')
            ->leftJoin('m.userRecommendedMovie', 'urm', 'WITH', 'urm.user = :user_id AND urm.originalMovie = :original_movie_id')
            ->addSelect('urm')
            ->where('m.id IN (:ids)')
            ->setParameter('user_id', $userId)
            ->setParameter('original_movie_id', $originalMovieId)
            ->setParameter('ids', $ids)
            ->getQuery()
            ->getResult();

        // Sorting here because ORDER BY FIELD(m.id, ...$ids) not working in postgres, we need to use joins on sorted table and so on, but I dont want to
        // todo => add sorting to sql
        $reversedIds = array_flip($ids);
        usort($result, function (Movie $movie1, Movie $movie2) use ($reversedIds) {
            return $reversedIds[$movie1->getId()] <=> $reversedIds[$movie2->getId()];
        });

        return $result;
    }

    public function findAllByIdsWithoutFlags(array $ids)
    {
        $result = $this->findAllByIds($ids);

        // Sorting here because ORDER BY FIELD(m.id, ...$ids) not working in postgres, we need to use joins on sorted table and so on, but I dont want to
        // todo => add sorting to sql
        $reversedIds = array_flip($ids);
        usort($result, function (Movie $movie1, Movie $movie2) use ($reversedIds) {
            return $reversedIds[$movie1->getId()] <=> $reversedIds[$movie2->getId()];
        });

        return $result;
    }

    public function findAllWithIsWatchedFlag(?User $user = null, ?GuestSession $guest = null): array
    {
        $items = $this->getBaseQuery();

        if ($user !== null) {
            $items
                ->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
                ->addSelect('uwm')
                ->setParameter('user_id', $user->getId());
        }

        if ($guest !== null) {
            $items
                ->leftJoin('m.guestWatchedMovie', 'gwm', 'WITH', 'gwm.guestSession = :guest_id')
                ->addSelect('gwm')
                ->setParameter('guest_id', $guest->getId());
        }

        $items->where('m.id IN (:ids)');

        /** Ids query */

        $ids = $this->createQueryBuilder('m')
            ->select('m.id')
            ->orderBy('m.id', 'DESC');

        /** Count query */

        $count = $this->createQueryBuilder('m')
            ->select('COUNT(m.id)');

        return [$items->getQuery(), $ids->getQuery(), $count->getQuery()];
    }

    public function findAllWithIsGuestWatchedFlag(?GuestSession $guestSession)
    {
        $guestSessionId = $guestSession ? $guestSession->getId() : 0;

        $result = $this->getBaseQuery()
            ->leftJoin('m.guestWatchedMovie', 'gwm', 'WITH', 'gwm.guestSession = :guest_session_id') // if this relation exists then guest has already watched this movie
            ->addSelect('gwm')
            ->setParameter('guest_session_id', $guestSessionId)
            ->orderBy('m.id', 'DESC')
            ->getQuery();

        return $result;
    }

    /**
     * @param array $ids
     *
     * @return array|Movie[]
     */
    public function findAllByIds(array $ids)
    {
        $result = $this->getBaseQuery()
            ->where('m.id IN (:ids)')
            ->setParameter('ids', $ids)
            ->getQuery()
            ->getResult();

        return $result;
    }

    /**
     * @param array $ids
     *
     * @return array|Movie[]
     */
    public function findAllByIdsWithSimilarMovies(array $ids): array
    {
        $result = $this->createQueryBuilder('m')
            ->leftJoin('m.similarMovies', 'sm')
            ->addSelect('sm')
            ->where('m.id IN (:ids)')
            ->setParameter('ids', $ids)
            ->getQuery()
            ->getScalarResult();

        return $result;
    }

    /**
     * @param array $ids
     *
     * @return array|Movie[]
     */
    public function findAllByTmdbIds(array $ids)
    {
        $result = $this->getBaseQuery()
            ->where('m.tmdb.id IN (:ids)')
            ->setParameter('ids', $ids)
            ->getQuery()
            ->getResult();

        return $result;
    }

    /**
     * @param array $ids
     *
     * @return array of int
     */
    public function findAllIdsByTmdbIds(array $ids)
    {
        $result = $this->createQueryBuilder('m')
            ->select('m.id, m.tmdb.voteAverage, m.releaseDate')
            ->where('m.tmdb.id IN (:ids)')
            ->setParameter('ids', $ids)
            ->getQuery()
            ->getScalarResult();

        return $result;
    }

    public function getAllWatchedMoviesByUserId(User $owner, ?User $currentUser = null): array
    {
        $items = $this->getBaseQuery()
            ->leftJoin('m.ownerWatchedMovie', 'owm', 'WITH', 'owm.user = :owner_id')
            ->addSelect('owm')
            ->setParameter('owner_id', $owner->getId());

        if ($currentUser !== null) {
            $items->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
                ->addSelect('uwm')
                ->setParameter('user_id', $currentUser->getId());
        }

        $items->where('m.id IN (:ids)');

        $ids = $this->createQueryBuilder('m')
            ->select('m.id')
            ->leftJoin('m.ownerWatchedMovie', 'owm', 'WITH', 'owm.user = :owner_id')
            ->where('owm.id != 0')
            ->setParameter('owner_id', $owner->getId())
            ->orderBy('owm.id', 'DESC');

        $count = $this->createQueryBuilder('m')
            ->select('COUNT(m.id)')
            ->leftJoin('m.ownerWatchedMovie', 'owm', 'WITH', 'owm.user = :owner_id')
            ->setParameter('owner_id', $owner->getId())

            ->where('owm.id != 0');

        return [$items->getQuery(), $ids->getQuery(), $count->getQuery()];
    }

    public function getAllInterestedMoviesByUserId(int $profileOwnerId, ?User $currentUser = null): Query
    {
        $result = $this->getBaseQuery()
            ->leftJoin('m.userInterestedMovie', 'uim', 'WITH', 'uim.user = :owner_id')
            ->addSelect('uim')
            ->setParameter('owner_id', $profileOwnerId)
            ->andWhere('uim.id != 0')
            ->orderBy('uim.id', 'DESC');

        if ($currentUser !== null) {
            $result->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :current_user_id')
                ->addSelect('uwm')
                ->setParameter('current_user_id', $currentUser->getId());
        }

        return $result->getQuery();
    }

    public function findAllByActor(int $actorId, ?User $currentUser = null): Query
    {
        $result = $this->getBaseQuery()
            ->leftJoin('m.actors', 'ma', 'WITH', 'ma.actor = :actor AND ma.movie = m')
            ->setParameter('actor', $actorId)
            ->andWhere('ma.id != 0')
            ->orderBy('m.releaseDate', 'DESC');

        if ($currentUser !== null) {
            $result->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
                ->addSelect('uwm')
                ->setParameter('user_id', $currentUser->getId());
        }

        return $result->getQuery();
    }

    public function findAllQuery()
    {
        $result = $this->getBaseQuery()
            ->orderBy('m.id', 'DESC')
            ->getQuery();

        return $result;
    }

    public function findByTitleQuery(string $query)
    {
        $query = mb_strtolower($query);
        $result = $this->getBaseQuery()
            ->andWhere('LOWER(m.originalTitle) LIKE :title OR LOWER(mt.title) LIKE :title')
            ->setParameter('title', "%{$query}%")
            ->getQuery();

        return $result;
    }

    public function findByTitleWithUserRecommendedMovieQuery(string $query, int $userId, int $originalMovieId)
    {
        $query = mb_strtolower($query);
        $result = $this->getBaseQuery()
            ->andWhere('LOWER(m.originalTitle) LIKE :title OR LOWER(mt.title) LIKE :title')
            ->setParameter('title', "%{$query}%")
            ->leftJoin('m.userRecommendedMovie', 'urm', 'WITH', 'urm.user = :user_id AND urm.originalMovie = :movie_id')
            ->addSelect('urm')
            ->setParameter('user_id', $userId)
            ->setParameter('movie_id', $originalMovieId)
            ->getQuery();

        return $result;
    }

    public function findOneByIdOrTmdbId(?int $id = null, ?int $tmdb_id = null)
    {
        if ($id === null && $tmdb_id === null) {
            throw new \InvalidArgumentException('Movie ID or TMDB ID should be provided');
        }

        return $id ? $this->find($id) : $this->findOneBy(['tmdb.id' => $tmdb_id]);
    }
}


1		<?php
2
3		declare(strict_types=1);
4
5		namespace App\Movies\Repository;
6
7		use App\Guests\Entity\GuestSession;
8		use App\Movies\Entity\Movie;
9		use App\Users\Entity\User;
10		use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
11		use Doctrine\ORM\Query;
12		use Doctrine\ORM\QueryBuilder;
13		use Symfony\Bridge\Doctrine\RegistryInterface;
14
15		/**
16		* @method Movie\|null find($id, $lockMode = null, $lockVersion = null)
17		* @method Movie\|null findOneBy(array $criteria, array $orderBy = null)
18		* @method Movie[] findAll()
19		* @method Movie[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
20		*/
21		class MovieRepository extends ServiceEntityRepository
22		{
23	27	public function __construct(RegistryInterface $registry)
24		{
25	27	parent::__construct($registry, Movie::class);
26	27	}
27
28	23	private function getBaseQuery(): QueryBuilder
29		{
30	23	return $this->createQueryBuilder('m')
31	23	->leftJoin('m.translations', 'mt', null, null, 'mt.locale')
32	23	->addSelect('mt')
33	23	->leftJoin('m.genres', 'mg')
34	23	->addSelect('mg')
35	23	->leftJoin('mg.translations', 'mgt', null, null, 'mgt.locale')
36	23	->addSelect('mgt');
37		}
38
39		/**
40		* @param int $id
41		* @param User\|null $user
42		*
43		* @throws \Doctrine\ORM\NoResultException
44		* @throws \Doctrine\ORM\NonUniqueResultException
45		*
46		* @return Movie\|null
47		*/
48	2	public function findOneForMoviePage(int $id, ?User $user = null): ?Movie
49		{
50	2	if ($user === null) {
51	2	return $this->getBaseQuery()
52	2	->where('m.id = :id')
53	2	->setParameter('id', $id)
54	2	->getQuery()
55	2	->getSingleResult();
56		}
57
58		$result = $this->getBaseQuery()
59		->where('m.id = :id')
60		->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
61		->addSelect('uwm')
62		->leftJoin('m.userRecommendedMovie', 'urm', 'WITH', 'urm.user = :user_id AND urm.originalMovie = :id')
63		->addSelect('urm')
64		->leftJoin('m.userInterestedMovie', 'uim', 'WITH', 'uim.user = :user_id AND uim.movie = :id')
65		->addSelect('uim')
66		->setParameter('user_id', $user->getId())
67		->setParameter('id', $id)
68		->getQuery()
69		->getSingleResult();
70
71		return $result;
72		}
73
74		public function findAllByIdsWithFlags(array $ids, int $userId, int $originalMovieId)
75		{
76		$result = $this->getBaseQuery()
77		->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id') // if this relation exists then user has already watched this movie
78		->addSelect('uwm')
79		->leftJoin('m.userRecommendedMovie', 'urm', 'WITH', 'urm.user = :user_id AND urm.originalMovie = :original_movie_id')
80		->addSelect('urm')
81		->where('m.id IN (:ids)')
82		->setParameter('user_id', $userId)
83		->setParameter('original_movie_id', $originalMovieId)
84		->setParameter('ids', $ids)
85		->getQuery()
86		->getResult();
87
88		// Sorting here because ORDER BY FIELD(m.id, ...$ids) not working in postgres, we need to use joins on sorted table and so on, but I dont want to
89		// todo => add sorting to sql
90		$reversedIds = array_flip($ids);
91		usort($result, function (Movie $movie1, Movie $movie2) use ($reversedIds) {
92		return $reversedIds[$movie1->getId()] <=> $reversedIds[$movie2->getId()];
93		});
94
95		return $result;
96		}
97
98		public function findAllByIdsWithoutFlags(array $ids)
99		{
100		$result = $this->findAllByIds($ids);
101
102		// Sorting here because ORDER BY FIELD(m.id, ...$ids) not working in postgres, we need to use joins on sorted table and so on, but I dont want to
103		// todo => add sorting to sql
104		$reversedIds = array_flip($ids);
105		usort($result, function (Movie $movie1, Movie $movie2) use ($reversedIds) {
106		return $reversedIds[$movie1->getId()] <=> $reversedIds[$movie2->getId()];
107		});
108
109		return $result;
110		}
111
112	17	public function findAllWithIsWatchedFlag(?User $user = null, ?GuestSession $guest = null): array
113		{
114	17	$items = $this->getBaseQuery();
115
116	17	if ($user !== null) {
117		$items
118		->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
119		->addSelect('uwm')
120		->setParameter('user_id', $user->getId());
121		}
122
123	17	if ($guest !== null) {
124		$items
125		->leftJoin('m.guestWatchedMovie', 'gwm', 'WITH', 'gwm.guestSession = :guest_id')
126		->addSelect('gwm')
127		->setParameter('guest_id', $guest->getId());
128		}
129
130	17	$items->where('m.id IN (:ids)');
131
132		/** Ids query */
133
134	17	$ids = $this->createQueryBuilder('m')
135	17	->select('m.id')
136	17	->orderBy('m.id', 'DESC');
137
138		/** Count query */
139
140	17	$count = $this->createQueryBuilder('m')
141	17	->select('COUNT(m.id)');
142
143	17	return [$items->getQuery(), $ids->getQuery(), $count->getQuery()];
144		}
145
146		public function findAllWithIsGuestWatchedFlag(?GuestSession $guestSession)
147		{
148		$guestSessionId = $guestSession ? $guestSession->getId() : 0;
149
150		$result = $this->getBaseQuery()
151		->leftJoin('m.guestWatchedMovie', 'gwm', 'WITH', 'gwm.guestSession = :guest_session_id') // if this relation exists then guest has already watched this movie
152		->addSelect('gwm')
153		->setParameter('guest_session_id', $guestSessionId)
154		->orderBy('m.id', 'DESC')
155		->getQuery();
156
157		return $result;
158		}
159
160		/**
161		* @param array $ids
162		*
163		* @return array\|Movie[]
164		*/
165		public function findAllByIds(array $ids)
166		{
167		$result = $this->getBaseQuery()
168		->where('m.id IN (:ids)')
169		->setParameter('ids', $ids)
170		->getQuery()
171		->getResult();
172
173		return $result;
174		}
175
176		/**
177		* @param array $ids
178		*
179		* @return array\|Movie[]
180		*/
181		public function findAllByIdsWithSimilarMovies(array $ids): array
182		{
183		$result = $this->createQueryBuilder('m')
184		->leftJoin('m.similarMovies', 'sm')
185		->addSelect('sm')
186		->where('m.id IN (:ids)')
187		->setParameter('ids', $ids)
188		->getQuery()
189		->getScalarResult();
190
191		return $result;
192		}
193
194		/**
195		* @param array $ids
196		*
197		* @return array\|Movie[]
198		*/
199	1	public function findAllByTmdbIds(array $ids)
200		{
201	1	$result = $this->getBaseQuery()
202	1	->where('m.tmdb.id IN (:ids)')
203	1	->setParameter('ids', $ids)
204	1	->getQuery()
205	1	->getResult();
206
207	1	return $result;
208		}
209
210		/**
211		* @param array $ids
212		*
213		* @return array of int
214		*/
215		public function findAllIdsByTmdbIds(array $ids)
216		{
217		$result = $this->createQueryBuilder('m')
218		->select('m.id, m.tmdb.voteAverage, m.releaseDate')
219		->where('m.tmdb.id IN (:ids)')
220		->setParameter('ids', $ids)
221		->getQuery()
222		->getScalarResult();
223
224		return $result;
225		}
226
227	2	public function getAllWatchedMoviesByUserId(User $owner, ?User $currentUser = null): array
228		{
229	2	$items = $this->getBaseQuery()
230	2	->leftJoin('m.ownerWatchedMovie', 'owm', 'WITH', 'owm.user = :owner_id')
231	2	->addSelect('owm')
232	2	->setParameter('owner_id', $owner->getId());
233
234	2	if ($currentUser !== null) {
235	2	$items->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
236	2	->addSelect('uwm')
237	2	->setParameter('user_id', $currentUser->getId());
238		}
239
240	2	$items->where('m.id IN (:ids)');
241
242	2	$ids = $this->createQueryBuilder('m')
243	2	->select('m.id')
244	2	->leftJoin('m.ownerWatchedMovie', 'owm', 'WITH', 'owm.user = :owner_id')
245	2	->where('owm.id != 0')
246	2	->setParameter('owner_id', $owner->getId())
247	2	->orderBy('owm.id', 'DESC');
248
249	2	$count = $this->createQueryBuilder('m')
250	2	->select('COUNT(m.id)')
251	2	->leftJoin('m.ownerWatchedMovie', 'owm', 'WITH', 'owm.user = :owner_id')
252	2	->setParameter('owner_id', $owner->getId())
253
254	2	->where('owm.id != 0');
255
256	2	return [$items->getQuery(), $ids->getQuery(), $count->getQuery()];
257		}
258
259	3	public function getAllInterestedMoviesByUserId(int $profileOwnerId, ?User $currentUser = null): Query
260		{
261	3	$result = $this->getBaseQuery()
262	3	->leftJoin('m.userInterestedMovie', 'uim', 'WITH', 'uim.user = :owner_id')
263	3	->addSelect('uim')
264	3	->setParameter('owner_id', $profileOwnerId)
265	3	->andWhere('uim.id != 0')
266	3	->orderBy('uim.id', 'DESC');
267
268	3	if ($currentUser !== null) {
269	3	$result->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :current_user_id')
270	3	->addSelect('uwm')
271	3	->setParameter('current_user_id', $currentUser->getId());
272		}
273
274	3	return $result->getQuery();
275		}
276
277	2	public function findAllByActor(int $actorId, ?User $currentUser = null): Query
278		{
279	2	$result = $this->getBaseQuery()
280	2	->leftJoin('m.actors', 'ma', 'WITH', 'ma.actor = :actor AND ma.movie = m')
281	2	->setParameter('actor', $actorId)
282	2	->andWhere('ma.id != 0')
283	2	->orderBy('m.releaseDate', 'DESC');
284
285	2	if ($currentUser !== null) {
286	1	$result->leftJoin('m.userWatchedMovie', 'uwm', 'WITH', 'uwm.user = :user_id')
287	1	->addSelect('uwm')
288	1	->setParameter('user_id', $currentUser->getId());
289		}
290
291	2	return $result->getQuery();
292		}
293
294		public function findAllQuery()
295		{
296		$result = $this->getBaseQuery()
297		->orderBy('m.id', 'DESC')
298		->getQuery();
299
300		return $result;
301		}
302
303	2	public function findByTitleQuery(string $query)
304		{
305	2	$query = mb_strtolower($query);
306	2	$result = $this->getBaseQuery()
307	2	->andWhere('LOWER(m.originalTitle) LIKE :title OR LOWER(mt.title) LIKE :title')
308	2	->setParameter('title', "%{$query}%")
309	2	->getQuery();
310
311	2	return $result;
312		}
313
314		public function findByTitleWithUserRecommendedMovieQuery(string $query, int $userId, int $originalMovieId)
315		{
316		$query = mb_strtolower($query);
317		$result = $this->getBaseQuery()
318		->andWhere('LOWER(m.originalTitle) LIKE :title OR LOWER(mt.title) LIKE :title')
319		->setParameter('title', "%{$query}%")
320		->leftJoin('m.userRecommendedMovie', 'urm', 'WITH', 'urm.user = :user_id AND urm.originalMovie = :movie_id')
321		->addSelect('urm')
322		->setParameter('user_id', $userId)
323		->setParameter('movie_id', $originalMovieId)
324		->getQuery();
325
326		return $result;
327		}
328
329	9	public function findOneByIdOrTmdbId(?int $id = null, ?int $tmdb_id = null)
330		{
331	9	if ($id === null && $tmdb_id === null) {
332		throw new \InvalidArgumentException('Movie ID or TMDB ID should be provided');
333		}
334
335	9	return $id ? $this->find($id) : $this->findOneBy(['tmdb.id' => $tmdb_id]);
336		}
337		}
338

svbackend / my-art-lib

Push — master ( d44c39...321b0a )

MovieRepository::findAllByIds() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like