52 lines
1.8 KiB
PHP
52 lines
1.8 KiB
PHP
<?php
|
|
|
|
namespace App\Repository;
|
|
|
|
use App\Entity\Release;
|
|
use App\Entity\User;
|
|
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
|
|
use Doctrine\Persistence\ManagerRegistry;
|
|
|
|
/**
|
|
* @extends ServiceEntityRepository<Release>
|
|
*
|
|
* @method Release|null find($id, $lockMode = null, $lockVersion = null)
|
|
* @method Release|null findOneBy(array $criteria, array $orderBy = null)
|
|
* @method Release[] findAll()
|
|
* @method Release[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
|
|
*/
|
|
class ReleaseRepository extends ServiceEntityRepository
|
|
{
|
|
public function __construct(ManagerRegistry $registry)
|
|
{
|
|
parent::__construct($registry, Release::class);
|
|
}
|
|
|
|
/**
|
|
* Return the user release listing as an array of associative arrays.
|
|
*
|
|
* The listing includes all releases with this user as owner. Items are sorted by the "artist aggregation" name. An
|
|
* artist aggregation is the joint names of all artists linked to a release. Within an artist aggregation (which
|
|
* most of the time is probably just an artist name), releases are sorted by title (should be by release date).
|
|
*
|
|
* Included data is:
|
|
* - release_id
|
|
* - title
|
|
* - artists (artist aggregation)
|
|
*
|
|
* @return array<int,array<string,mixed>>
|
|
*/
|
|
public function getUserListing(User $user): array
|
|
{
|
|
return $this->getEntityManager()->getConnection()->executeQuery('
|
|
SELECT r.id AS release_id, title, string_agg(name, \' & \' ORDER BY name) AS artists
|
|
FROM release r
|
|
JOIN release_artist ra ON ra.release_id = r.id
|
|
JOIN artist a ON a.id = ra.artist_id
|
|
GROUP BY r.id
|
|
--HAVING COUNT(*) > 1
|
|
ORDER BY artists, title
|
|
')->fetchAllAssociative();
|
|
}
|
|
}
|