Optimize SeriesDatePlayed ordering

2 files changed, 53 insertions, 19 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
index 8f7300b0b9..683c0582ab 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
@@ -2815,6 +2815,14 @@ public sealed class BaseItemRepository
         var orderBy = filter.OrderBy.Where(e => e.OrderBy != ItemSortBy.Default).ToArray();
         var hasSearch = !string.IsNullOrEmpty(filter.SearchTerm);
 
+        // SeriesDatePlayed requires special handling to avoid correlated subqueries.
+        // Instead of running a MAX() subquery per-row in ORDER BY, we pre-aggregate
+        // max played dates per series in one query and left-join it.
+        if (!hasSearch && orderBy.Any(o => o.OrderBy == ItemSortBy.SeriesDatePlayed))
+        {
+            return ApplySeriesDatePlayedOrder(query, filter, context, orderBy);
+        }
+
         IOrderedQueryable<BaseItemEntity>? orderedQuery = null;
 
         if (hasSearch)
@@ -2871,6 +2879,42 @@ public sealed class BaseItemRepository
         return orderedQuery;
     }
 
+    private IQueryable<BaseItemEntity> ApplySeriesDatePlayedOrder(
+        IQueryable<BaseItemEntity> query,
+        InternalItemsQuery filter,
+        JellyfinDbContext context,
+        (ItemSortBy OrderBy, SortOrder SortOrder)[] orderBy)
+    {
+        // Pre-aggregate max played date per series key in ONE query.
+        // This generates a single: SELECT SeriesPresentationUniqueKey, MAX(LastPlayedDate) ... GROUP BY
+        // instead of a correlated subquery per outer row.
+        IQueryable<UserData> userDataQuery = filter.User is not null
+            ? context.UserData.Where(ud => ud.UserId == filter.User.Id && ud.Played)
+            : context.UserData.Where(ud => ud.Played);
+
+        var seriesMaxDates = userDataQuery
+            .Join(
+                context.BaseItems,
+                ud => ud.ItemId,
+                bi => bi.Id,
+                (ud, bi) => new { bi.SeriesPresentationUniqueKey, ud.LastPlayedDate })
+            .Where(x => x.SeriesPresentationUniqueKey != null)
+            .GroupBy(x => x.SeriesPresentationUniqueKey)
+            .Select(g => new { SeriesKey = g.Key!, MaxDate = g.Max(x => x.LastPlayedDate) });
+
+        var joined = query.LeftJoin(
+            seriesMaxDates,
+            e => e.PresentationUniqueKey,
+            s => s.SeriesKey,
+            (e, s) => new { Item = e, MaxDate = s != null ? s.MaxDate : (DateTime?)null });
+
+        var seriesSort = orderBy.First(o => o.OrderBy == ItemSortBy.SeriesDatePlayed);
+
+        return seriesSort.SortOrder == SortOrder.Ascending
+            ? joined.OrderBy(x => x.MaxDate).ThenBy(x => x.Item.SortName).Select(x => x.Item)
+            : joined.OrderByDescending(x => x.MaxDate).ThenBy(x => x.Item.SortName).Select(x => x.Item);
+    }
+
     private IQueryable<BaseItemEntity> TranslateQuery(
         IQueryable<BaseItemEntity> baseQuery,
         JellyfinDbContext context,
diff --git a/Jellyfin.Server.Implementations/Item/OrderMapper.cs b/Jellyfin.Server.Implementations/Item/OrderMapper.cs
index 05751e8fee..f48bbe43e1 100644
--- a/Jellyfin.Server.Implementations/Item/OrderMapper.cs
+++ b/Jellyfin.Server.Implementations/Item/OrderMapper.cs
@@ -57,26 +57,16 @@ public static class OrderMapper
             (ItemSortBy.VideoBitRate, _) => e => e.TotalBitrate,
             (ItemSortBy.ParentIndexNumber, _) => e => e.ParentIndexNumber,
             (ItemSortBy.IndexNumber, _) => e => e.IndexNumber,
+            // SeriesDatePlayed is normally handled via pre-aggregated join in ApplySeriesDatePlayedOrder.
+            // This correlated subquery fallback is only reached when combined with search.
             (ItemSortBy.SeriesDatePlayed, not null) => e =>
-                            jellyfinDbContext.BaseItems
-                                .Where(w => w.SeriesPresentationUniqueKey == e.PresentationUniqueKey)
-                                .LeftJoin(
-                                    jellyfinDbContext.UserData.Where(w => w.UserId == query.User.Id && w.Played),
-                                    item => item.Id,
-                                    userData => userData.ItemId,
-                                    (item, userData) => userData == null ? (DateTime?)null : userData.LastPlayedDate)
-                                .Max(f => f),
-            (ItemSortBy.SeriesDatePlayed, null) => e => jellyfinDbContext.BaseItems.Where(w => w.SeriesPresentationUniqueKey == e.PresentationUniqueKey)
-                                .LeftJoin(
-                                    jellyfinDbContext.UserData.Where(w => w.Played),
-                                    item => item.Id,
-                                    userData => userData.ItemId,
-                                    (item, userData) => userData == null ? (DateTime?)null : userData.LastPlayedDate)
-                                .Max(f => f),
-            // ItemSortBy.SeriesDatePlayed => e => jellyfinDbContext.UserData
-            //     .Where(u => u.Item!.SeriesPresentationUniqueKey == e.PresentationUniqueKey && u.Played)
-            //     .Max(f => f.LastPlayedDate),
-            // ItemSortBy.AiredEpisodeOrder => "AiredEpisodeOrder",
+                jellyfinDbContext.UserData
+                    .Where(w => w.UserId == query.User.Id && w.Played && w.Item!.SeriesPresentationUniqueKey == e.PresentationUniqueKey)
+                    .Max(f => f.LastPlayedDate),
+            (ItemSortBy.SeriesDatePlayed, null) => e =>
+                jellyfinDbContext.UserData
+                    .Where(w => w.Played && w.Item!.SeriesPresentationUniqueKey == e.PresentationUniqueKey)
+                    .Max(f => f.LastPlayedDate),
             _ => e => e.SortName
         };
     }