Merge pull request #17463 from Shadowghost/fix-unplayed-filter

Fix (Un)Played filter correctness and performance

5 files changed, 89 insertions, 150 deletions

diff --git a/Jellyfin.Server.Implementations/Extensions/ExpressionExtensions.cs b/Jellyfin.Server.Implementations/Extensions/ExpressionExtensions.cs
index d70ac672f2..0f166fc6e0 100644
--- a/Jellyfin.Server.Implementations/Extensions/ExpressionExtensions.cs
+++ b/Jellyfin.Server.Implementations/Extensions/ExpressionExtensions.cs
@@ -40,6 +40,19 @@ public static class ExpressionExtensions
     }
 
     /// <summary>
+    /// Negates a predicate.
+    /// </summary>
+    /// <typeparam name="T">The predicate parameter type.</typeparam>
+    /// <param name="predicate">The predicate expression to negate.</param>
+    /// <returns>A new expression representing the negation of the input predicate.</returns>
+    public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> predicate)
+    {
+        ArgumentNullException.ThrowIfNull(predicate);
+
+        return Expression.Lambda<Func<T, bool>>(Expression.Not(predicate.Body), predicate.Parameters);
+    }
+
+    /// <summary>
     /// Combines two predicates into a single predicate using a logical AND operation.
     /// </summary>
     /// <typeparam name="T">The predicate parameter type.</typeparam>
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
index a4de9feb05..80e16ca310 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
@@ -503,62 +503,31 @@ public sealed partial class BaseItemRepository
     }
 
     /// <inheritdoc />
-    public IQueryable<Guid> GetFullyPlayedFolderIdsQuery(JellyfinDbContext context, IQueryable<Guid> folderIds, User user)
+    public IQueryable<BaseItemEntity> GetAccessFilteredLeafItemsQuery(JellyfinDbContext context, User user, bool includeOwnedItems = false)
     {
         ArgumentNullException.ThrowIfNull(context);
-        ArgumentNullException.ThrowIfNull(folderIds);
         ArgumentNullException.ThrowIfNull(user);
 
-        var filter = new InternalItemsQuery(user);
-        var userId = user.Id;
-
         var leafItems = context.BaseItems
             .AsNoTracking()
-            .Where(b => !b.IsFolder && !b.IsVirtualItem);
-        leafItems = ApplyAccessFiltering(context, leafItems, filter);
-
-        var playedLeafItems = leafItems
-            .Select(b => new { b.Id, Played = b.UserData!.Any(ud => ud.UserId == userId && ud.Played) });
-
-        var ancestorLeaves = context.AncestorIds
-            .Where(a => folderIds.Contains(a.ParentItemId))
-            .Join(
-                playedLeafItems,
-                a => a.ItemId,
-                b => b.Id,
-                (a, b) => new { FolderId = a.ParentItemId, b.Id, b.Played });
+            .Where(e => !e.IsFolder && !e.IsVirtualItem);
 
-        var linkedLeaves = context.LinkedChildren
-            .Where(lc => folderIds.Contains(lc.ParentId))
-            .Join(
-                playedLeafItems,
-                lc => lc.ChildId,
-                b => b.Id,
-                (lc, b) => new { FolderId = lc.ParentId, b.Id, b.Played });
+        return ApplyAccessFiltering(context, leafItems, new InternalItemsQuery(user) { IncludeOwnedItems = includeOwnedItems });
+    }
 
-        var linkedFolderLeaves = context.LinkedChildren
-            .Where(lc => folderIds.Contains(lc.ParentId))
-            .Join(
-                context.BaseItems.Where(b => b.IsFolder),
-                lc => lc.ChildId,
-                b => b.Id,
-                (lc, b) => new { lc.ParentId, FolderChildId = b.Id })
-            .Join(
-                context.AncestorIds,
-                x => x.FolderChildId,
-                a => a.ParentItemId,
-                (x, a) => new { x.ParentId, DescendantId = a.ItemId })
-            .Join(
-                playedLeafItems,
-                x => x.DescendantId,
-                b => b.Id,
-                (x, b) => new { FolderId = x.ParentId, b.Id, b.Played });
-
-        return ancestorLeaves
-            .Union(linkedLeaves)
-            .Union(linkedFolderLeaves)
-            .GroupBy(x => x.FolderId)
-            .Where(g => g.Select(x => x.Id).Distinct().Count() == g.Where(x => x.Played).Select(x => x.Id).Distinct().Count())
-            .Select(g => g.Key);
+    /// <inheritdoc />
+    public Expression<Func<BaseItemEntity, bool>> BuildHasDescendantFilter(JellyfinDbContext context, IQueryable<BaseItemEntity> descendants)
+    {
+        ArgumentNullException.ThrowIfNull(context);
+        ArgumentNullException.ThrowIfNull(descendants);
+
+        // Descendants are reachable through the ancestor chain and - for BoxSets and Playlists - as
+        // linked children, which can themselves be folders contributing their own descendants.
+        // Every step is a correlated index seek, so only the rows the outer query keeps are visited
+        // and a folder is left as soon as its first matching descendant is found.
+        return e => context.AncestorIds.Any(a => a.ParentItemId == e.Id && descendants.Any(d => d.Id == a.ItemId))
+            || context.LinkedChildren.Any(lc => lc.ParentId == e.Id
+                && (descendants.Any(d => d.Id == lc.ChildId)
+                    || context.AncestorIds.Any(a => a.ParentItemId == lc.ChildId && descendants.Any(d => d.Id == a.ItemId))));
     }
 }
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index 47f8a40b9c..8e93d22205 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -31,6 +31,10 @@ public sealed partial class BaseItemRepository
     private static readonly string TmdbProviderName = MetadataProvider.Tmdb.ToString().ToLowerInvariant();
     private static readonly string TvdbProviderName = MetadataProvider.Tvdb.ToString().ToLowerInvariant();
 
+    // A fresh expression per access: EF rejects a query tree that reuses one lambda parameter
+    // instance across several lambdas, and this filter is combined into a tree more than once.
+    private static Expression<Func<BaseItemEntity, bool>> IsFolderFilter => e => e.IsFolder;
+
     /// <inheritdoc />
     public IQueryable<BaseItemEntity> TranslateQuery(
         IQueryable<BaseItemEntity> baseQuery,
@@ -466,97 +470,45 @@ public sealed partial class BaseItemRepository
 
         if (filter.IsPlayed.HasValue)
         {
-            var hasSeries = filter.IncludeItemTypes.Contains(BaseItemKind.Series);
-            var hasBoxSet = filter.IncludeItemTypes.Contains(BaseItemKind.BoxSet);
+            var userId = filter.User!.Id;
 
-            if (hasSeries || hasBoxSet)
-            {
-                var userId = filter.User!.Id;
-                var isPlayed = filter.IsPlayed.Value;
-                var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
-                var boxSetTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.BoxSet];
-
-                // Series: played = at least one episode AND all episodes played; unplayed = otherwise.
-                IQueryable<Guid> playedSeriesIds = hasSeries
-                    ? context.BaseItems
-                        .AsNoTracking()
-                        .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue)
-                        .GroupBy(e => e.SeriesId!.Value)
-                        .Where(g => !g.Any(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played)))
-                        .Select(g => g.Key)
-                    : Enumerable.Empty<Guid>().AsQueryable();
-
-                // BoxSet: played = all children played.
-                IQueryable<Guid> playedBoxSetIds = hasBoxSet
-                    ? GetFullyPlayedFolderIdsQuery(
-                        context,
-                        baseQuery.Where(e => e.Type == boxSetTypeName).Select(e => e.Id),
-                        filter.User!)
-                    : Enumerable.Empty<Guid>().AsQueryable();
-
-                // Non-folder items: check UserData directly
-                var playedItemIds = context.UserData
-                    .Where(ud => ud.UserId == userId && ud.Played)
-                    .Select(ud => ud.ItemId);
-
-                if (isPlayed)
-                {
-                    baseQuery = baseQuery.Where(e =>
-                        (e.Type == seriesTypeName && playedSeriesIds.Contains(e.Id))
-                        || (e.Type == boxSetTypeName && playedBoxSetIds.Contains(e.Id))
-                        || (e.Type != seriesTypeName && e.Type != boxSetTypeName && playedItemIds.Contains(e.Id)));
-                }
-                else
-                {
-                    baseQuery = baseQuery.Where(e =>
-                        (e.Type == seriesTypeName && !playedSeriesIds.Contains(e.Id))
-                        || (e.Type == boxSetTypeName && !playedBoxSetIds.Contains(e.Id))
-                        || (e.Type != seriesTypeName && e.Type != boxSetTypeName && !playedItemIds.Contains(e.Id)));
-                }
-            }
-            else
-            {
-                var playedItemIds = context.UserData
-                    .Where(ud => ud.UserId == filter.User!.Id && ud.Played)
-                    .Select(ud => ud.ItemId);
-                var isPlayedItem = filter.IsPlayed.Value;
-                baseQuery = baseQuery.Where(e => playedItemIds.Contains(e.Id) == isPlayedItem);
-            }
+            // Leaf items carry their own played state.
+            var playedItemIds = context.UserData
+                .Where(ud => ud.UserId == userId && ud.Played)
+                .Select(ud => ud.ItemId);
+
+            // Folders (Series, Seasons, BoxSets, albums, ...) have none and count as played once no
+            // descendant is left unplayed, matching what the DTO reports for them. This has to key off
+            // the item itself rather than off the requested item types: tag and collection listings mix
+            // folders and leaf items in a single query.
+            var unplayedLeafItems = GetAccessFilteredLeafItemsQuery(context, filter.User!)
+                .Where(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played));
+
+            var isPlayedFilter = IsFolderFilter.And(BuildHasDescendantFilter(context, unplayedLeafItems).Not())
+                .Or(IsFolderFilter.Not().And(e => playedItemIds.Contains(e.Id)));
+
+            baseQuery = baseQuery.Where(filter.IsPlayed.Value ? isPlayedFilter : isPlayedFilter.Not());
         }
 
         if (filter.IsResumable.HasValue)
         {
-            var hasSeries = filter.IncludeItemTypes.Contains(BaseItemKind.Series);
             var userId = filter.User!.Id;
             var isResumable = filter.IsResumable.Value;
-            var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
 
             // In-progress user data rows; alternate versions track their own progress.
             var inProgress = context.UserData
                 .Where(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0);
 
-            IQueryable<Guid>? resumableSeriesIds = null;
-            if (hasSeries)
-            {
-                // Aggregate per series in a single GROUP BY pass, instead of three full scans.
-                var seriesEpisodeStats = context.BaseItems
-                    .AsNoTracking()
-                    .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue)
-                    .GroupBy(e => e.SeriesId!.Value)
-                    .Select(g => new
-                    {
-                        SeriesId = g.Key,
-                        HasInProgress = g.Any(e => e.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0)),
-                        HasPlayed = g.Any(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)),
-                        HasUnplayed = g.Any(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played))
-                    });
-
-                // A series is resumable if it has an in-progress episode,
-                // or if it has both played and unplayed episodes (partially watched).
-                resumableSeriesIds = seriesEpisodeStats
-                    .Where(s => s.HasInProgress || (s.HasPlayed && s.HasUnplayed))
-                    .Select(s => s.SeriesId);
-            }
+            // Folders are resumable when a descendant is in progress, or when they hold both played and
+            // unplayed descendants (partially watched). Alternate versions keep their own progress, so
+            // they count towards the in-progress check but not towards the played/unplayed one.
+            var leafItems = GetAccessFilteredLeafItemsQuery(context, filter.User!);
+            var inProgressLeafItems = GetAccessFilteredLeafItemsQuery(context, filter.User!, includeOwnedItems: true)
+                .Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0));
+
+            var folderResumableFilter = BuildHasDescendantFilter(context, inProgressLeafItems)
+                .Or(BuildHasDescendantFilter(context, leafItems.Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)))
+                    .And(BuildHasDescendantFilter(context, leafItems.Where(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played)))));
 
             if (isResumable)
             {
@@ -564,18 +516,15 @@ public sealed partial class BaseItemRepository
                 // Match each version on its own progress rather than coalescing onto the primary.
                 var inProgressIds = inProgress.Select(ud => ud.ItemId);
 
-                baseQuery = hasSeries
-                    ? baseQuery.Where(e =>
-                        (e.Type == seriesTypeName && resumableSeriesIds!.Contains(e.Id))
-                        || (e.Type != seriesTypeName && inProgressIds.Contains(e.Id)))
-                    : baseQuery.Where(e => inProgressIds.Contains(e.Id));
+                baseQuery = baseQuery.Where(IsFolderFilter.And(folderResumableFilter)
+                    .Or(IsFolderFilter.Not().And(e => inProgressIds.Contains(e.Id))));
 
                 // When several versions of the same item are in progress, keep only the most recently played one, use id as tiebreaker.
                 // Only in-progress siblings can eliminate a candidate: a version without progress has a NULL max LastPlayedDate,
                 // which is never greater and never ties. Restricting the sibling scan to the in-progress set keeps this bounded by
                 // the user's Continue Watching count instead of forcing a full BaseItems scan (COALESCE keys are non-indexable) per row.
                 // Items in no version group at all have no sibling that could eliminate them, so short-circuit the scan for those.
-                baseQuery = baseQuery.Where(e => e.Type == seriesTypeName
+                baseQuery = baseQuery.Where(e => e.IsFolder
                     || (e.PrimaryVersionId == null && !context.BaseItems.Any(a => a.PrimaryVersionId == e.Id))
                     || !context.BaseItems
                         .Where(s => s.Id != e.Id
@@ -594,11 +543,8 @@ public sealed partial class BaseItemRepository
                 var resumableMovieIds = inProgress
                     .Join(context.BaseItems, ud => ud.ItemId, bi => bi.Id, (ud, bi) => bi.PrimaryVersionId ?? bi.Id);
 
-                baseQuery = hasSeries
-                    ? baseQuery.Where(e =>
-                        (e.Type == seriesTypeName && !resumableSeriesIds!.Contains(e.Id))
-                        || (e.Type != seriesTypeName && !resumableMovieIds.Contains(e.Id)))
-                    : baseQuery.Where(e => !resumableMovieIds.Contains(e.Id));
+                baseQuery = baseQuery.Where(IsFolderFilter.And(folderResumableFilter.Not())
+                    .Or(IsFolderFilter.Not().And(e => !resumableMovieIds.Contains(e.Id))));
             }
         }
 
diff --git a/MediaBrowser.Controller/Entities/UserViewBuilder.cs b/MediaBrowser.Controller/Entities/UserViewBuilder.cs
index 9ba103cc8b..aed11e5cc3 100644
--- a/MediaBrowser.Controller/Entities/UserViewBuilder.cs
+++ b/MediaBrowser.Controller/Entities/UserViewBuilder.cs
@@ -461,11 +461,12 @@ namespace MediaBrowser.Controller.Entities
                     var counts = libraryManager.GetPlayedAndTotalCountBatch(folderIds, user);
                     var isPlayedValue = query.IsPlayed.Value;
 
-                    return itemList.Where(i =>
+                    return itemList.Where(item =>
                     {
-                        if (i.IsFolder && counts.TryGetValue(i.Id, out var c))
+                        if (item is Folder)
                         {
-                            return (c.Total > 0 && c.Played == c.Total) == isPlayedValue;
+                            var itemCount = counts.GetValueOrDefault(item.Id);
+                            return (itemCount.Played >= itemCount.Total) == isPlayedValue;
                         }
 
                         return true;
diff --git a/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs b/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs
index 2e29cbdbba..f9a050d591 100644
--- a/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs
+++ b/MediaBrowser.Controller/Persistence/IItemQueryHelpers.cs
@@ -1,5 +1,6 @@
 using System;
 using System.Linq;
+using System.Linq.Expressions;
 using Jellyfin.Database.Implementations;
 using Jellyfin.Database.Implementations.Entities;
 using MediaBrowser.Controller.Entities;
@@ -79,17 +80,26 @@ public interface IItemQueryHelpers
         Guid ancestorId);
 
     /// <summary>
-    /// Builds an <see cref="IQueryable{Guid}"/> of folder IDs whose descendants are all played
-    /// for the given user. Composable into outer queries to avoid an extra DB roundtrip.
+    /// Builds a query for the playable leaf items a user can access.
     /// </summary>
     /// <param name="context">The database context the resulting query is bound to.</param>
-    /// <param name="folderIds">A query yielding candidate folder IDs.</param>
-    /// <param name="user">The user for access filtering and played status.</param>
-    /// <returns>An <see cref="IQueryable{Guid}"/> of fully-played folder IDs.</returns>
-    IQueryable<Guid> GetFullyPlayedFolderIdsQuery(
+    /// <param name="user">The user to filter accessible items for.</param>
+    /// <param name="includeOwnedItems">Whether to include alternate versions and owned items.</param>
+    /// <returns>The access-filtered leaf item queryable.</returns>
+    IQueryable<BaseItemEntity> GetAccessFilteredLeafItemsQuery(
         JellyfinDbContext context,
-        IQueryable<Guid> folderIds,
-        User user);
+        User user,
+        bool includeOwnedItems = false);
+
+    /// <summary>
+    /// Builds a filter matching items that have at least one of <paramref name="descendants"/> below them.
+    /// </summary>
+    /// <param name="context">The database context the resulting filter is bound to.</param>
+    /// <param name="descendants">A query yielding the descendants to look for.</param>
+    /// <returns>A filter expression matching items with a matching descendant.</returns>
+    Expression<Func<BaseItemEntity, bool>> BuildHasDescendantFilter(
+        JellyfinDbContext context,
+        IQueryable<BaseItemEntity> descendants);
 
     /// <summary>
     /// Deserializes a <see cref="BaseItemEntity"/> into a <see cref="BaseItem"/>.