From f9d2a2a854c877c1332d7cd1397edbb5e038450c Mon Sep 17 00:00:00 2001
From: Marcel van der Veldt <m.vanderveldt@outlook.com>
Date: Thu, 29 Aug 2024 11:23:19 +0200
Subject: [PATCH] fix db query for album tracks

---
 music_assistant/server/controllers/media/albums.py | 6 +++---
 music_assistant/server/controllers/media/base.py   | 1 +
 music_assistant/server/controllers/media/tracks.py | 6 ++++--
 music_assistant/server/helpers/database.py         | 2 +-
 4 files changed, 9 insertions(+), 6 deletions(-)

diff --git a/music_assistant/server/controllers/media/albums.py b/music_assistant/server/controllers/media/albums.py
index 6a41fcc9..f52aba65 100644
--- a/music_assistant/server/controllers/media/albums.py
+++ b/music_assistant/server/controllers/media/albums.py
@@ -286,9 +286,9 @@ class AlbumsController(MediaControllerBase[Album]):
         item_id: str | int,
     ) -> list[Track]:
         """Return in-database album tracks for the given database album."""
-        subquery = f"SELECT track_id FROM {DB_TABLE_ALBUM_TRACKS} WHERE album_id = {item_id}"
-        query = f"WHERE tracks.item_id in ({subquery})"
-        return await self.mass.music.tracks._get_library_items_by_query(extra_query_parts=[query])
+        return await self.mass.music.tracks._get_library_items_by_query(
+            extra_query_parts=[f"WHERE album_tracks.album_id = {item_id}"],
+        )
 
     async def _add_library_item(self, item: Album) -> int:
         """Add a new record to the database."""
diff --git a/music_assistant/server/controllers/media/base.py b/music_assistant/server/controllers/media/base.py
index aa2ecdc6..dffc20c9 100644
--- a/music_assistant/server/controllers/media/base.py
+++ b/music_assistant/server/controllers/media/base.py
@@ -745,6 +745,7 @@ class MediaControllerBase(Generic[ItemCls], metaclass=ABCMeta):
         if query_parts:
             sql_query += " WHERE " + " AND ".join(query_parts)
         # build final query
+        sql_query += f" GROUP BY {self.db_table}.item_id"
         if order_by:
             if sort_key := SORT_KEYS.get(order_by):
                 sql_query += f" ORDER BY {sort_key}"
diff --git a/music_assistant/server/controllers/media/tracks.py b/music_assistant/server/controllers/media/tracks.py
index e4b7d853..a9220f09 100644
--- a/music_assistant/server/controllers/media/tracks.py
+++ b/music_assistant/server/controllers/media/tracks.py
@@ -82,8 +82,10 @@ class TracksController(MediaControllerBase[Track]):
                     'disc_number', album_tracks.disc_number,
                     'track_number', album_tracks.track_number,
                     'images', json_extract(albums.metadata, '$.images')
-                ) FROM albums JOIN album_tracks on album_tracks.track_id = tracks.item_id  WHERE albums.item_id = album_tracks.album_id) AS track_album
-            FROM tracks"""  # noqa: E501
+                ) FROM albums WHERE albums.item_id = album_tracks.album_id) AS track_album
+            FROM tracks
+            LEFT JOIN album_tracks on album_tracks.track_id = tracks.item_id
+            """  # noqa: E501
         # register (extra) api handlers
         api_base = self.api_base
         self.mass.register_api_command(f"music/{api_base}/track_versions", self.versions)
diff --git a/music_assistant/server/helpers/database.py b/music_assistant/server/helpers/database.py
index 7cada571..9e0e37e1 100644
--- a/music_assistant/server/helpers/database.py
+++ b/music_assistant/server/helpers/database.py
@@ -80,7 +80,7 @@ class DatabaseConnection:
         """Perform async initialization."""
         self._db = await aiosqlite.connect(self.db_path)
         self._db.row_factory = aiosqlite.Row
-        await self.execute("PRAGMA analysis_limit=400;")
+        await self.execute("PRAGMA analysis_limit=10000;")
         await self.execute("PRAGMA optimize;")
         await self.commit()
 
-- 
2.34.1