src/Repository/SongRepository.php line 79

Open in your IDE?
  1. <?php
  3. namespace App\Repository;
  5. use App\Entity\EventTag;
  6. use App\Entity\History;
  7. use App\Entity\Song;
  8. use App\Entity\Tag;
  9. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  10. use Doctrine\Persistence\ManagerRegistry;
  11. use Doctrine\ORM\NonUniqueResultException;
  12. use Doctrine\ORM\NoResultException;
  14. /**
  15. * @method Song|null find($id, $lockMode = null, $lockVersion = null)
  16. * @method Song|null findOneBy(array $criteria, array $orderBy = null)
  17. * @method Song[] findAll()
  18. * @method Song[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
  19. */
  20. class SongRepository extends ServiceEntityRepository
  21. {
  22. public function __construct(ManagerRegistry $registry)
  23. {
  24. parent::__construct($registry, Song::class);
  25. }
  27. public function search(?string $search = '', ?int $tagId = 0)
  28. {
  29. $query = $this->createQueryBuilder('s');
  31. if (strlen($search)>0){
  32. $query->andWhere('s.name LIKE :search')
  33. ->setParameter('search', '%'.$search.'%');
  34. }
  36. if ($tagId>0){
  37. $query->leftJoin('s.tags', 't')
  38. ->andWhere('t.id = :tagId')->setParameter('tagId', $tagId);
  39. }
  41. $query->andWhere('s.deleted = false');
  43. return $query->orderBy('s.created', 'DESC')
  44. ->setMaxResults(Song::MAX_SONG_DISPLAY)
  45. ->getQuery()
  46. ->getResult()
  47. ;
  48. }
  50. public function allPathsExceptPaths(array $paths){
  51. return $this->createQueryBuilder('s')
  52. ->select("CONCAT(s.name, '.mp3') as song")
  53. ->where("CONCAT(s.name, '.mp3') NOT IN (:paths)")
  54. ->setParameter('paths', $paths)
  55. ->getQuery()->getResult();
  56. }
  58. /**
  59. * @param Tag $tag
  60. * @return mixed
  61. */
  62. public function getRandomFromTag(Tag $tag){
  63. try {
  64. return $this->createQueryBuilder('s')
  65. ->leftJoin('s.tags', 't')
  66. ->andWhere('t.id = :tagId and s.deleted = false')->setParameter('tagId', $tag->getId())
  67. ->addSelect('RAND() as HIDDEN rand')
  68. ->orderBy('rand')
  69. ->setMaxResults(1)
  70. ->getQuery()
  71. ->getSingleResult();
  72. } catch (NoResultException $e) {
  73. return null;
  74. } catch (NonUniqueResultException $e) {
  75. return null;
  76. }
  77. }
  79. public function getSongsHistoryForEventTag(EventTag $eventTag){
  80. $query = $this->createQueryBuilder('s')
  81. ->select('
  82. s,
  83. SumIfWithinLastXHours(h.created, 4) as HIDDEN last4h,
  84. SumIfWithinLastXHours(h.created, 6) as HIDDEN last6h,
  85. SumIfWithinLastXHours(h.created, 12) as HIDDEN last12h,
  86. SumIfWithinLastXHours(h.created, 24) as HIDDEN last24h,
  87. SumIfWithinLastXHours(h.created, 48) as HIDDEN last48h
  88. ')
  89. ->leftJoin('s.tags', 't')
  90. ->leftJoin('t.eventTags', 'et')
  91. ->leftJoin(History::class, 'h', 'WITH', 'h.song = s.id AND h.eventTag = et.id')
  92. ->where('et.id = :eventTagId and s.deleted = 0')->setParameter('eventTagId', $eventTag->getId())
  93. ->orderBy('last4h')
  94. ->addOrderBy('last6h')
  95. ->addOrderBy('last12h')
  96. ->addOrderBy('last24h')
  97. ->addOrderBy('last48h')
  98. ->groupBy('s.id');
  100. return $query->getQuery()->getResult();
  101. }
  103. /*
  104. public function findOneBySomeField($value): ?Song
  105. {
  106. return $this->createQueryBuilder('s')
  107. ->andWhere('s.exampleField = :val')
  108. ->setParameter('val', $value)
  109. ->getQuery()
  110. ->getOneOrNullResult()
  111. ;
  112. }
  113. */
  114. }