def artists(rls: T, conn: Connection) -> list[dict]:
"""
Get the "album artists" of the provided release.
:param rls: The provided release.
:param conn: A connection to the datbase.
:return: A list of ``{"artist": artist.T, "role": ArtistRole}`` dicts
representing the album artists.
"""
cursor = conn.execute(
"""
SELECT
artists.*,
COUNT(rlsarts.release_id) AS num_releases,
rlsarts.role
FROM (
SELECT arts.*
FROM music__releases_artists AS rlsarts
JOIN music__artists AS arts ON arts.id = rlsarts.artist_id
WHERE rlsarts.release_id = ?
GROUP BY arts.id
) AS artists
JOIN music__releases_artists AS rlsarts ON rlsarts.artist_id = artists.id
GROUP BY artists.id, rlsarts.role
""",
(rls.id, ),
)
logger.debug(f"Fetched artists of release {rls.id}.")
return [{
"artist": artist.from_row(without_key(row, "role")),
"role": ArtistRole(row["role"]),
} for row in cursor]
def artists(trk: T, conn: Connection) -> list[dict]:
"""
Get the artists that contributed to a track and their roles.
:param trk: The track whose artists to fetch.
:param conn: A connection to the database.
:return: A list of ``{"artist": artist.T, "role": ArtistRole}`` dicts.
"""
cursor = conn.execute(
"""
SELECT
arts.*,
COUNT(artsrls.release_id) AS num_releases,
trksarts.role
FROM music__tracks_artists AS trksarts
JOIN music__artists AS arts ON arts.id = trksarts.artist_id
LEFT JOIN music__releases_artists AS artsrls
ON artsrls.artist_id = arts.id
WHERE trksarts.track_id = ?
GROUP BY arts.id, trksarts.role
""",
(trk.id, ),
)
logger.debug(f"Fetched artists of track {trk.id}.")
return [{
"artist": artist.from_row(without_key(row, "role")),
"role": ArtistRole(row["role"]),
} for row in cursor]
def top_genres(ply: T, conn: Connection, *, num_genres: int = 5) -> list[dict]:
"""
Get the top genre collections of the tracks in a playlist.
The returned genres are in the following format:
.. code-block:: python
[
{
"genre": collection.T,
"num_matches": int,
},
...
]
The field ``num_releases`` in the genre collections is set to ``None``.
:param ply: The playlist whose top genres to fetch.
:param conn: A connection to the database.
:param num_genres: The number of top genres to fetch.
:return: The top genres.
"""
cursor = conn.execute(
"""
SELECT
genres.*,
COUNT(plystrks.track_id) AS num_matches
FROM music__collections AS genres
LEFT JOIN music__collections_releases AS colsrls
ON colsrls.collection_id = genres.id
LEFT JOIN music__tracks AS trks
ON trks.release_id = colsrls.release_id
LEFT JOIN music__playlists_tracks AS plystrks
ON plystrks.track_id = trks.id
WHERE plystrks.playlist_id = ? AND genres.type = ?
GROUP BY genres.id
ORDER BY num_matches DESC, genres.id ASC
LIMIT ?
""",
(ply.id, CollectionType.GENRE.value, num_genres),
)
logger.debug(f"Fetched top genres of playlist {ply.id}.")
return [{
"genre": collection.from_row(without_key(row, "num_matches")),
"num_matches": row["num_matches"],
} for row in cursor]
def test_without_key():
assert {1: 2} == without_key({1: 2, 3: 4}, 3)