From 2086ac7dd2f64f286ea3c88a53cc0860f28454f8 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 18 Jan 2026 14:59:57 +0100 Subject: Don't use raw SQL --- .../DescendantQueryHelper.cs | 161 +++++++++++++++++++++ 1 file changed, 161 insertions(+) create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs (limited to 'src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs') diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs new file mode 100644 index 0000000000..e6fa6ca458 --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs @@ -0,0 +1,161 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using Jellyfin.Database.Implementations.Entities; +using Jellyfin.Database.Implementations.MatchCriteria; + +namespace Jellyfin.Database.Implementations; + +/// +/// Provides methods for querying item hierarchies using iterative traversal. +/// Uses AncestorIds and LinkedChildren tables for parent-child traversal. +/// +public static class DescendantQueryHelper +{ + /// + /// Gets a queryable of all descendant IDs for a parent item. + /// Traverses AncestorIds and LinkedChildren to find all descendants. + /// + /// Database context. + /// Parent item ID. + /// Queryable of descendant item IDs. + public static IQueryable GetAllDescendantIds(JellyfinDbContext context, Guid parentId) + { + ArgumentNullException.ThrowIfNull(context); + + var descendants = TraverseHierarchyDown(context, [parentId]); + + descendants.Remove(parentId); + + return descendants.AsQueryable(); + } + + /// + /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria. + /// Can be used in LINQ .Contains() expressions. + /// + /// Database context. + /// The matching criteria to apply. + /// Queryable of folder IDs. + public static IQueryable GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria) + { + ArgumentNullException.ThrowIfNull(context); + ArgumentNullException.ThrowIfNull(criteria); + var matchingItemIds = criteria switch + { + HasSubtitles => context.MediaStreamInfos + .Where(ms => ms.StreamType == MediaStreamTypeEntity.Subtitle) + .Select(ms => ms.ItemId) + .Distinct() + .ToHashSet(), + HasChapterImages => context.Chapters + .Where(c => c.ImagePath != null) + .Select(c => c.ItemId) + .Distinct() + .ToHashSet(), + HasMediaStreamType m => GetMatchingMediaStreamItemIds(context, m), + _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}") + }; + + var ancestors = TraverseHierarchyUp(context, matchingItemIds); + + return ancestors.AsQueryable(); + } + + private static HashSet GetMatchingMediaStreamItemIds(JellyfinDbContext context, HasMediaStreamType criteria) + { + var query = context.MediaStreamInfos + .Where(ms => ms.StreamType == criteria.StreamType && ms.Language == criteria.Language); + + if (criteria.IsExternal.HasValue) + { + var isExternal = criteria.IsExternal.Value; + query = query.Where(ms => ms.IsExternal == isExternal); + } + + return query.Select(ms => ms.ItemId).Distinct().ToHashSet(); + } + + /// + /// Traverses DOWN the hierarchy from parent folders to find all descendants. + /// + private static HashSet TraverseHierarchyDown(JellyfinDbContext context, ICollection startIds) + { + var visited = new HashSet(startIds); + var folderStack = new HashSet(startIds); + + while (folderStack.Count != 0) + { + var currentFolders = folderStack.ToArray(); + folderStack.Clear(); + + var directChildren = context.AncestorIds + .WhereOneOrMany(currentFolders, e => e.ParentItemId) + .Select(e => e.ItemId) + .ToArray(); + + var linkedChildren = context.LinkedChildren + .WhereOneOrMany(currentFolders, e => e.ParentId) + .Select(e => e.ChildId) + .ToArray(); + + var allChildren = directChildren.Concat(linkedChildren).Distinct().ToArray(); + + if (allChildren.Length == 0) + { + break; + } + + var childFolders = context.BaseItems + .WhereOneOrMany(allChildren, e => e.Id) + .Where(e => e.IsFolder) + .Select(e => e.Id) + .ToHashSet(); + + foreach (var childId in allChildren) + { + if (visited.Add(childId) && childFolders.Contains(childId)) + { + folderStack.Add(childId); + } + } + } + + return visited; + } + + /// + /// Traverses UP the hierarchy from items to find all ancestor folders. + /// + private static HashSet TraverseHierarchyUp(JellyfinDbContext context, ICollection startIds) + { + var ancestors = new HashSet(); + var itemStack = new HashSet(startIds); + + while (itemStack.Count != 0) + { + var currentItems = itemStack.ToArray(); + itemStack.Clear(); + + var ancestorParents = context.AncestorIds + .WhereOneOrMany(currentItems, e => e.ItemId) + .Select(e => e.ParentItemId) + .ToArray(); + + var linkedParents = context.LinkedChildren + .WhereOneOrMany(currentItems, e => e.ChildId) + .Select(e => e.ParentId) + .ToArray(); + + foreach (var parentId in ancestorParents.Concat(linkedParents)) + { + if (ancestors.Add(parentId)) + { + itemStack.Add(parentId); + } + } + } + + return ancestors; + } +} -- cgit v1.2.3 From bb6c3b4eecee46a0a6222ffe17657cabc7da97f4 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 7 Feb 2026 21:17:01 +0100 Subject: Fix BoxSet collapse handling and deletion --- .../Item/BaseItemRepository.cs | 82 ++++++++++++++++++---- .../Entities/CollectionFolder.cs | 17 ++++- MediaBrowser.Controller/Entities/Folder.cs | 28 ++++++++ .../Entities/InternalItemsQuery.cs | 6 ++ .../DescendantQueryHelper.cs | 60 ++++++++++++++++ 5 files changed, 179 insertions(+), 14 deletions(-) (limited to 'src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs index 3ba6750045..99e85d946d 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs @@ -121,7 +121,9 @@ public sealed class BaseItemRepository var date = (DateTime?)DateTime.UtcNow; - var descendantIds = ids.SelectMany(f => DescendantQueryHelper.GetAllDescendantIds(context, f)).ToHashSet(); + // Use owned-only traversal (AncestorIds) to avoid deleting items that are merely + // linked via LinkedChildren (e.g. movies/series inside a BoxSet are associations, not owned children). + var descendantIds = ids.SelectMany(f => DescendantQueryHelper.GetOwnedDescendantIds(context, f)).ToHashSet(); foreach (var id in ids) { descendantIds.Add(id); @@ -333,6 +335,7 @@ public sealed class BaseItemRepository } var itemsById = ApplyNavigations(context.BaseItems.Where(e => orderedIds.Contains(e.Id)), filter) + .AsSplitQuery() .AsEnumerable() .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)) .Where(dto => dto is not null) @@ -341,7 +344,7 @@ public sealed class BaseItemRepository return orderedIds.Where(itemsById.ContainsKey).Select(id => itemsById[id]).ToArray()!; } - dbQuery = ApplyNavigations(dbQuery, filter); + dbQuery = ApplyNavigations(dbQuery, filter).AsSplitQuery(); return dbQuery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto is not null).ToArray()!; } @@ -994,7 +997,7 @@ public sealed class BaseItemRepository if (filter.CollapseBoxSetItems == true) { - dbQuery = ApplyBoxSetCollapsing(context, dbQuery); + dbQuery = ApplyBoxSetCollapsing(context, dbQuery, filter.CollapseBoxSetItemTypes); } dbQuery = ApplyOrder(dbQuery, filter, context); @@ -1004,12 +1007,55 @@ public sealed class BaseItemRepository private IQueryable ApplyBoxSetCollapsing( JellyfinDbContext context, - IQueryable dbQuery) + IQueryable dbQuery, + BaseItemKind[] collapsibleTypes) { var boxSetTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.BoxSet]; var currentIds = dbQuery.Select(e => e.Id); + if (collapsibleTypes.Length == 0) + { + // Collapse all item types into box sets + return ApplyBoxSetCollapsingAll(context, currentIds, boxSetTypeName); + } + + // Only collapse specific item types, keep others untouched + var collapsibleTypeNames = collapsibleTypes.Select(t => _itemTypeLookup.BaseItemKindNames[t]).ToList(); + + // Items whose type is NOT collapsible (always kept in results) + var nonCollapsibleIds = currentIds + .Where(id => !context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type))); + + // Collapsible items that are NOT in any box set (kept in results) + var collapsibleNotInBoxSet = currentIds + .Where(id => + context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type)) + && !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName) + && !context.LinkedChildren.Any(lc => + lc.ChildId == id + && lc.ChildType == DbLinkedChildType.Manual + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))); + + // Box set IDs containing at least one accessible collapsible child item + var boxSetIds = context.LinkedChildren + .Where(lc => + lc.ChildType == DbLinkedChildType.Manual + && currentIds.Contains(lc.ChildId) + && context.BaseItems.Any(bi => bi.Id == lc.ChildId && collapsibleTypeNames.Contains(bi.Type)) + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)) + .Select(lc => lc.ParentId) + .Distinct(); + + var collapsedIds = nonCollapsibleIds.Union(collapsibleNotInBoxSet).Union(boxSetIds); + return context.BaseItems.Where(e => collapsedIds.Contains(e.Id)); + } + + private static IQueryable ApplyBoxSetCollapsingAll( + JellyfinDbContext context, + IQueryable currentIds, + string boxSetTypeName) + { // Items that are NOT box sets and NOT in any box set var notInBoxSet = currentIds .Where(id => @@ -1019,8 +1065,7 @@ public sealed class BaseItemRepository && lc.ChildType == DbLinkedChildType.Manual && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))); - // Box set IDs containing at least one accessible child item. - // Access filtering is already applied to currentIds via TranslateQuery + // Box set IDs containing at least one accessible child item var boxSetIds = context.LinkedChildren .Where(lc => lc.ChildType == DbLinkedChildType.Manual @@ -1060,8 +1105,10 @@ public sealed class BaseItemRepository dbQuery = dbQuery.Include(e => e.Images); } - // Only include LinkedChildEntities for container types and videos that use them - // (BoxSet, Playlist, CollectionFolder for manual linking; Video, Movie for alternate versions) + // Include LinkedChildEntities for container types and videos that use them + // (BoxSet, Playlist, CollectionFolder for manual linking; Video, Movie for alternate versions). + // When IncludeItemTypes is empty (any type may be returned), always include them to ensure + // LinkedChildren are loaded before items are saved back, preventing accidental deletion. var linkedChildTypes = new[] { BaseItemKind.BoxSet, @@ -1070,7 +1117,7 @@ public sealed class BaseItemRepository BaseItemKind.Video, BaseItemKind.Movie }; - if (filter.IncludeItemTypes.Length > 0 && filter.IncludeItemTypes.Any(linkedChildTypes.Contains)) + if (filter.IncludeItemTypes.Length == 0 || filter.IncludeItemTypes.Any(linkedChildTypes.Contains)) { dbQuery = dbQuery.Include(e => e.LinkedChildEntities); } @@ -1108,7 +1155,7 @@ public sealed class BaseItemRepository dbQuery = TranslateQuery(dbQuery, context, filter); dbQuery = ApplyGroupingFilter(context, dbQuery, filter); dbQuery = ApplyQueryPaging(dbQuery, filter); - dbQuery = ApplyNavigations(dbQuery, filter); + dbQuery = ApplyNavigations(dbQuery, filter).AsSplitQuery(); return dbQuery; } @@ -1531,7 +1578,9 @@ public sealed class BaseItemRepository ? context.BaseItems .Where(e => e.Path != null && pathsToResolve.Contains(e.Path)) .Select(e => new { e.Path, e.Id }) - .ToDictionary(e => e.Path!, e => e.Id) + .AsEnumerable() + .GroupBy(e => e.Path!) + .ToDictionary(g => g.Key, g => g.First().Id) : []; var resolvedChildren = new List<(LinkedChild Child, Guid ChildId)>(); @@ -1628,7 +1677,9 @@ public sealed class BaseItemRepository var pathToIdMap = context.BaseItems .Where(e => e.Path != null && pathsToResolve.Contains(e.Path)) .Select(e => new { e.Path, e.Id }) - .ToDictionary(e => e.Path!, e => e.Id); + .AsEnumerable() + .GroupBy(e => e.Path!) + .ToDictionary(g => g.Key, g => g.First().Id); foreach (var path in pathsToResolve) { @@ -3324,6 +3375,13 @@ public sealed class BaseItemRepository .Where(e => e.OwnerId == null); } } + else if (filter.OwnerIds.Length == 0 && filter.ExtraTypes.Length == 0) + { + // Exclude alternate versions from general queries. Alternate versions have + // OwnerId set (pointing to their primary) but no ExtraType. + // Extras (trailers, etc.) also have OwnerId but DO have ExtraType set - keep those. + baseQuery = baseQuery.Where(e => e.OwnerId == null || e.ExtraType != null); + } if (filter.OwnerIds.Length > 0) { diff --git a/MediaBrowser.Controller/Entities/CollectionFolder.cs b/MediaBrowser.Controller/Entities/CollectionFolder.cs index cf615788ee..ffdc8421da 100644 --- a/MediaBrowser.Controller/Entities/CollectionFolder.cs +++ b/MediaBrowser.Controller/Entities/CollectionFolder.cs @@ -79,14 +79,27 @@ namespace MediaBrowser.Controller.Entities public CollectionType? CollectionType { get; set; } /// - /// Gets the item's children. + /// Gets or sets the item's children. /// /// /// Our children are actually just references to the ones in the physical root... + /// Setting to null propagates invalidation to physical folders since the getter + /// always delegates to and never reads the backing field. /// /// The actual children. [JsonIgnore] - public override IEnumerable Children => GetActualChildren(); + public override IEnumerable Children + { + get => GetActualChildren(); + set + { + // The getter delegates to physical folders, so invalidate their caches. + foreach (var folder in GetPhysicalFolders(true)) + { + folder.Children = null; + } + } + } [JsonIgnore] public override bool SupportsPeople => false; diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs index 44903fd4c1..0c0558b4c1 100644 --- a/MediaBrowser.Controller/Entities/Folder.cs +++ b/MediaBrowser.Controller/Entities/Folder.cs @@ -733,6 +733,7 @@ namespace MediaBrowser.Controller.Entities if (!query.ForceDirect && RequiresPostFiltering(query)) { query.CollapseBoxSetItems = true; + SetCollapseBoxSetItemTypes(query); } if (this is not UserRootFolder @@ -1039,6 +1040,33 @@ namespace MediaBrowser.Controller.Entities return (queryHasMovies || queryHasSeries) && AllowBoxSetCollapsing(query); } + private void SetCollapseBoxSetItemTypes(InternalItemsQuery query) + { + var config = ConfigurationManager.Configuration; + bool collapseMovies = config.EnableGroupingMoviesIntoCollections; + bool collapseSeries = config.EnableGroupingShowsIntoCollections; + + if (collapseMovies && collapseSeries) + { + // Empty means collapse all types + query.CollapseBoxSetItemTypes = []; + return; + } + + var types = new List(); + if (collapseMovies) + { + types.Add(BaseItemKind.Movie); + } + + if (collapseSeries) + { + types.Add(BaseItemKind.Series); + } + + query.CollapseBoxSetItemTypes = types.ToArray(); + } + private static bool AllowBoxSetCollapsing(InternalItemsQuery request) { if (request.IsFavorite.HasValue) diff --git a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs index b36ea627d8..2824fb6954 100644 --- a/MediaBrowser.Controller/Entities/InternalItemsQuery.cs +++ b/MediaBrowser.Controller/Entities/InternalItemsQuery.cs @@ -113,6 +113,12 @@ namespace MediaBrowser.Controller.Entities public bool? CollapseBoxSetItems { get; set; } + /// + /// Gets or sets the item types that should be collapsed into box sets. + /// When empty, all types are collapsed. When set, only items of these types are replaced by their parent box set. + /// + public BaseItemKind[] CollapseBoxSetItemTypes { get; set; } = []; + public string? NameStartsWithOrGreater { get; set; } public string? NameStartsWith { get; set; } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs index e6fa6ca458..3bc36dca7a 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs @@ -30,6 +30,25 @@ public static class DescendantQueryHelper return descendants.AsQueryable(); } + /// + /// Gets a queryable of all owned descendant IDs for a parent item. + /// Traverses only AncestorIds (hierarchical ownership), NOT LinkedChildren (associations). + /// Use this for deletion to avoid destroying items that are merely linked (e.g. movies in a BoxSet). + /// + /// Database context. + /// Parent item ID. + /// Queryable of owned descendant item IDs. + public static IQueryable GetOwnedDescendantIds(JellyfinDbContext context, Guid parentId) + { + ArgumentNullException.ThrowIfNull(context); + + var descendants = TraverseHierarchyDownOwned(context, [parentId]); + + descendants.Remove(parentId); + + return descendants.AsQueryable(); + } + /// /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria. /// Can be used in LINQ .Contains() expressions. @@ -124,6 +143,47 @@ public static class DescendantQueryHelper return visited; } + /// + /// Traverses DOWN the hierarchy using only AncestorIds (ownership), not LinkedChildren. + /// + private static HashSet TraverseHierarchyDownOwned(JellyfinDbContext context, ICollection startIds) + { + var visited = new HashSet(startIds); + var folderStack = new HashSet(startIds); + + while (folderStack.Count != 0) + { + var currentFolders = folderStack.ToArray(); + folderStack.Clear(); + + var directChildren = context.AncestorIds + .WhereOneOrMany(currentFolders, e => e.ParentItemId) + .Select(e => e.ItemId) + .ToArray(); + + if (directChildren.Length == 0) + { + break; + } + + var childFolders = context.BaseItems + .WhereOneOrMany(directChildren, e => e.Id) + .Where(e => e.IsFolder) + .Select(e => e.Id) + .ToHashSet(); + + foreach (var childId in directChildren) + { + if (visited.Add(childId) && childFolders.Contains(childId)) + { + folderStack.Add(childId); + } + } + } + + return visited; + } + /// /// Traverses UP the hierarchy from items to find all ancestor folders. /// -- cgit v1.2.3 From 744c5539d8471addca131c9d9f7e8c4e30f8c4b5 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Thu, 5 Mar 2026 22:54:26 +0100 Subject: Fix review comments --- .../Item/BaseItemRepository.cs | 282 +++++++++------------ .../DescendantQueryHelper.cs | 27 ++ 2 files changed, 152 insertions(+), 157 deletions(-) (limited to 'src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs index 233d572cb2..6b575fbad3 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs @@ -123,7 +123,7 @@ public sealed class BaseItemRepository // Use owned-only traversal (AncestorIds) to avoid deleting items that are merely // linked via LinkedChildren (e.g. movies/series inside a BoxSet are associations, not owned children). - var descendantIds = ids.SelectMany(f => DescendantQueryHelper.GetOwnedDescendantIds(context, f)).ToHashSet(); + var descendantIds = DescendantQueryHelper.GetOwnedDescendantIdsBatch(context, ids); foreach (var id in ids) { descendantIds.Add(id); @@ -306,7 +306,7 @@ public sealed class BaseItemRepository dbQuery = ApplyQueryPaging(dbQuery, filter); dbQuery = ApplyNavigations(dbQuery, filter); - result.Items = dbQuery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto is not null).ToArray()!; + result.Items = dbQuery.AsEnumerable().Where(e => e != null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto != null).ToArray()!; result.StartIndex = filter.StartIndex ?? 0; return result; } @@ -335,10 +335,9 @@ public sealed class BaseItemRepository } var itemsById = ApplyNavigations(context.BaseItems.Where(e => orderedIds.Contains(e.Id)), filter) - .AsSplitQuery() .AsEnumerable() .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)) - .Where(dto => dto is not null) + .Where(dto => dto != null) .ToDictionary(i => i!.Id); return orderedIds.Where(itemsById.ContainsKey).Select(id => itemsById[id]).ToArray()!; @@ -346,7 +345,7 @@ public sealed class BaseItemRepository dbQuery = ApplyNavigations(dbQuery, filter); - return dbQuery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto is not null).ToArray()!; + return dbQuery.AsEnumerable().Where(e => e != null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto != null).ToArray()!; } /// @@ -361,7 +360,7 @@ public sealed class BaseItemRepository return []; } - var limit = filter.Limit ?? 50; + var limit = filter.Limit; using var context = _dbProvider.CreateDbContext(); var baseQuery = PrepareItemQuery(context, filter); @@ -393,44 +392,52 @@ public sealed class BaseItemRepository .Where(groupKeyFilter) .GroupBy(groupKeySelector) .Select(g => new { GroupKey = g.Key!, MaxDate = g.Max(e => e.DateCreated) }) - .OrderByDescending(g => g.MaxDate) - .Take(limit) - .Select(g => g.GroupKey) - .ToList(); + .OrderByDescending(g => g.MaxDate); + + if (filter.Limit.HasValue) + { + topGroupKeys = topGroupKeys.Take(filter.Limit.Value).OrderByDescending(g => g.MaxDate); + } // Get only the first (most recent) item ID per group using a lightweight projection, // then fetch full entities only for those items. This avoids loading all versions/tracks // with expensive navigation properties just to discard duplicates. + var topGroupKeyList = topGroupKeys.Select(g => g.GroupKey).ToList(); + // ThenByDescending(Id) is a tiebreaker for deterministic ordering when multiple items + // share the same DateCreated timestamp — without it, SQL returns arbitrary order across queries. var allItemsLite = collectionType switch { CollectionType.movies => baseQuery - .Where(e => e.PresentationUniqueKey != null && topGroupKeys.Contains(e.PresentationUniqueKey)) + .Where(e => e.PresentationUniqueKey != null && topGroupKeyList.Contains(e.PresentationUniqueKey)) .OrderByDescending(e => e.DateCreated) .ThenByDescending(e => e.Id) .Select(e => new { e.Id, GroupKey = e.PresentationUniqueKey }) - .ToList(), + .AsEnumerable(), _ => baseQuery - .Where(e => e.Album != null && topGroupKeys.Contains(e.Album)) + .Where(e => e.Album != null && topGroupKeyList.Contains(e.Album)) .OrderByDescending(e => e.DateCreated) .ThenByDescending(e => e.Id) .Select(e => new { e.Id, GroupKey = e.Album }) - .ToList() + .AsEnumerable() }; + // Client-side DistinctBy: EF Core/SQLite cannot reliably translate + // GroupBy(...).Select(g => g.First()) to SQL. The projection is lightweight + // (only Id + GroupKey for ~50 items), so client-side dedup is negligible. var firstIds = allItemsLite .DistinctBy(e => e.GroupKey) .Select(e => e.Id) - .ToList(); + .AsEnumerable(); var itemsQuery = context.BaseItems.AsNoTracking().Where(e => firstIds.Contains(e.Id)); - itemsQuery = ApplyNavigations(itemsQuery, filter).AsSingleQuery(); + itemsQuery = ApplyNavigations(itemsQuery, filter); return itemsQuery - .AsEnumerable() .OrderByDescending(e => e.DateCreated) .ThenByDescending(e => e.Id) + .AsEnumerable() .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)) - .Where(dto => dto is not null) + .Where(dto => dto != null) .ToArray()!; } @@ -458,7 +465,7 @@ public sealed class BaseItemRepository /// The query filter options. /// Maximum number of items to return. /// A list of BaseItemDto representing the latest TV content. - private IReadOnlyList GetLatestTvShowItems(JellyfinDbContext context, IQueryable baseQuery, InternalItemsQuery filter, int limit) + private IReadOnlyList GetLatestTvShowItems(JellyfinDbContext context, IQueryable baseQuery, InternalItemsQuery filter, int? limit) { // Episodes added within this window are considered "recently added together" const double RecentAdditionWindowHours = 24.0; @@ -468,22 +475,24 @@ public sealed class BaseItemRepository .Where(e => e.SeriesName != null) .GroupBy(e => e.SeriesName) .Select(g => new { SeriesName = g.Key!, MaxDate = g.Max(e => e.DateCreated) }) - .OrderByDescending(g => g.MaxDate) - .Take(limit) - .ToList(); + .OrderByDescending(g => g.MaxDate); + + if (limit.HasValue) + { + topSeriesWithDates = topSeriesWithDates.Take(limit.Value).OrderByDescending(g => g.MaxDate); + } - var topSeriesNames = topSeriesWithDates.Select(g => g.SeriesName).ToList(); + var topSeriesNames = topSeriesWithDates.Select(g => g.SeriesName).AsEnumerable(); // Compute a global date cutoff: the oldest series' max date minus the window. // Episodes before this cutoff cannot be in any series' "recent additions" window, // so we can safely exclude them to avoid loading ancient episodes. - var globalCutoff = topSeriesWithDates.Count > 0 + var globalCutoff = topSeriesWithDates.Any() ? topSeriesWithDates.Min(g => g.MaxDate)?.AddHours(-RecentAdditionWindowHours) : null; // Fetch only the columns needed for analysis (lightweight projection). - var episodeQuery = baseQuery - .Where(e => e.SeriesName != null && topSeriesNames.Contains(e.SeriesName)); + var episodeQuery = baseQuery.Where(e => e.SeriesName != null && topSeriesNames.Contains(e.SeriesName)); if (globalCutoff is not null) { episodeQuery = episodeQuery.Where(e => e.DateCreated >= globalCutoff); @@ -493,7 +502,7 @@ public sealed class BaseItemRepository .OrderByDescending(e => e.DateCreated) .ThenByDescending(e => e.Id) .Select(e => new { e.Id, e.SeriesName, e.DateCreated, e.SeasonId, e.SeriesId }) - .ToList(); + .AsEnumerable(); // Collect all season/series IDs we'll need to look up for count information var allSeasonIds = new HashSet(); @@ -570,7 +579,12 @@ public sealed class BaseItemRepository .ToDictionary(x => x.SeriesId, x => x.Count) : []; - // Step 5: Apply the container selection logic for each series + // Step 5: Apply the container selection logic for each series. + // For each series, decide which entity best represents the recent additions: + // - 1 episode added → show the Episode itself + // - Multiple episodes in 1 season (multi-season series) → show the Season + // - Multiple episodes in 1 season (single-season series) → show the Series + // - Episodes across multiple seasons → show the Series var entitiesToFetch = new HashSet(); var seriesResults = new List<(Guid? SeasonId, Guid? SeriesId, DateTime MaxDate, Guid MostRecentEpisodeId)>(analysisData.Count); @@ -653,10 +667,19 @@ public sealed class BaseItemRepository } } - return results + var finalResults = results .OrderByDescending(r => r.MaxDate) - .ThenByDescending(r => r.Entity.Id) - .Take(limit) + .ThenByDescending(r => r.Entity.Id); + + if (limit.HasValue) + { + finalResults = finalResults + .Take(limit.Value) + .OrderByDescending(r => r.MaxDate) + .ThenByDescending(r => r.Entity.Id); + } + + return finalResults .Select(r => DeserializeBaseItem(r.Entity, filter.SkipDeserialization)) .Where(dto => dto is not null) .ToArray()!; @@ -758,6 +781,12 @@ public sealed class BaseItemRepository .ToDictionary(x => x.SeriesKey, x => x.LastWatchedId); } + // Two-query pattern: The queries above use GroupBy(...).Select(g => g.OrderBy(...).First()) + // to pick the single best episode ID per series. EF Core ignores .Include() calls when the + // query shape changes through GroupBy/Select projections (see dotnet/efcore#13450), + // so navigation properties (Images, Providers, etc.) cannot be loaded in that query. + // We first identify which episode IDs we need, then fetch full entities with nav props + // only for those few items (~1 per series). var allLastWatchedIds = lastWatchedInfo.Values .Concat(lastWatchedByDateInfo.Values) .Where(id => id != Guid.Empty) @@ -767,7 +796,7 @@ public sealed class BaseItemRepository if (allLastWatchedIds.Count > 0) { var lwQuery = context.BaseItems.AsNoTracking().Where(e => allLastWatchedIds.Contains(e.Id)); - lwQuery = ApplyNavigations(lwQuery, filter).AsSingleQuery(); + lwQuery = ApplyNavigations(lwQuery, filter); lastWatchedEpisodes = lwQuery.ToDictionary(e => e.Id); } @@ -1752,7 +1781,6 @@ public sealed class BaseItemRepository ? context.BaseItems .Where(e => e.Path != null && pathsToResolve.Contains(e.Path)) .Select(e => new { e.Path, e.Id }) - .AsEnumerable() .GroupBy(e => e.Path!) .ToDictionary(g => g.Key, g => g.First().Id) : []; @@ -1851,7 +1879,6 @@ public sealed class BaseItemRepository var pathToIdMap = context.BaseItems .Where(e => e.Path != null && pathsToResolve.Contains(e.Path)) .Select(e => new { e.Path, e.Id }) - .AsEnumerable() .GroupBy(e => e.Path!) .ToDictionary(g => g.Key, g => g.First().Id); @@ -2643,9 +2670,9 @@ public sealed class BaseItemRepository [ .. query .AsEnumerable() - .Where(e => e is not null) + .Where(e => e != null) .Select(e => DeserializeBaseItem(e, filter.SkipDeserialization)) - .Where(item => item is not null) + .Where(item => item != null) .Select(item => (item!, (ItemCounts?)null)) ]; } @@ -3308,38 +3335,20 @@ public sealed class BaseItemRepository if (filter.IsLiked.HasValue) { - if (filter.IsLiked.Value) - { - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue)); - } - else - { - baseQuery = baseQuery.Where(e => !e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue)); - } + var isLiked = filter.IsLiked.Value; + baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue) == isLiked); } if (filter.IsFavoriteOrLiked.HasValue) { - if (filter.IsFavoriteOrLiked.Value) - { - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)); - } - else - { - baseQuery = baseQuery.Where(e => !e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)); - } + var isFavoriteOrLiked = filter.IsFavoriteOrLiked.Value; + baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavoriteOrLiked); } if (filter.IsFavorite.HasValue) { - if (filter.IsFavorite.Value) - { - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)); - } - else - { - baseQuery = baseQuery.Where(e => !e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)); - } + var isFavorite = filter.IsFavorite.Value; + baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavorite); } if (filter.IsPlayed.HasValue) @@ -3357,17 +3366,10 @@ public sealed class BaseItemRepository episode => episode.Id, ud => ud.ItemId, (episode, ud) => episode.SeriesId!.Value) - .Distinct() - .ToList(); + .Distinct(); - if (filter.IsPlayed.Value) - { - baseQuery = baseQuery.Where(s => playedSeriesIdList.Contains(s.Id)); - } - else - { - baseQuery = baseQuery.Where(s => !playedSeriesIdList.Contains(s.Id)); - } + var isPlayed = filter.IsPlayed.Value; + baseQuery = baseQuery.Where(s => playedSeriesIdList.Contains(s.Id) == isPlayed); } else if (filter.IncludeItemTypes.Length == 1 && filter.IncludeItemTypes[0] == BaseItemKind.BoxSet) { @@ -3375,31 +3377,18 @@ public sealed class BaseItemRepository var playedCounts = GetPlayedAndTotalCountBatch(boxSetIds, filter.User!); var playedBoxSetIds = playedCounts .Where(kvp => kvp.Value.Total > 0 && kvp.Value.Played == kvp.Value.Total) - .Select(kvp => kvp.Key) - .ToList(); + .Select(kvp => kvp.Key); - if (filter.IsPlayed.Value) - { - baseQuery = baseQuery.Where(s => playedBoxSetIds.Contains(s.Id)); - } - else - { - baseQuery = baseQuery.Where(s => !playedBoxSetIds.Contains(s.Id)); - } + var isPlayedBoxSet = filter.IsPlayed.Value; + baseQuery = baseQuery.Where(s => playedBoxSetIds.Contains(s.Id) == isPlayedBoxSet); } else { var playedItemIds = context.UserData .Where(ud => ud.UserId == filter.User!.Id && ud.Played) .Select(ud => ud.ItemId); - if (filter.IsPlayed.Value) - { - baseQuery = baseQuery.Where(e => playedItemIds.Contains(e.Id)); - } - else - { - baseQuery = baseQuery.Where(e => !playedItemIds.Contains(e.Id)); - } + var isPlayedItem = filter.IsPlayed.Value; + baseQuery = baseQuery.Where(e => playedItemIds.Contains(e.Id) == isPlayedItem); } } @@ -3408,14 +3397,8 @@ public sealed class BaseItemRepository var resumableItemIds = context.UserData .Where(ud => ud.UserId == filter.User!.Id && ud.PlaybackPositionTicks > 0) .Select(ud => ud.ItemId); - if (filter.IsResumable.Value) - { - baseQuery = baseQuery.Where(e => resumableItemIds.Contains(e.Id)); - } - else - { - baseQuery = baseQuery.Where(e => !resumableItemIds.Contains(e.Id)); - } + var isResumable = filter.IsResumable.Value; + baseQuery = baseQuery.Where(e => resumableItemIds.Contains(e.Id) == isResumable); } if (filter.ArtistIds.Length > 0) @@ -3508,27 +3491,7 @@ public sealed class BaseItemRepository Expression>? maxParentalRatingFilter = null; if (filter.MaxParentalRating != null) { - var max = filter.MaxParentalRating; - var maxScore = max.Score; - var maxSubScore = max.SubScore ?? 0; - var linkedChildren = context.LinkedChildren; - - maxParentalRatingFilter = e => - // Item has a rating: check against limit - (e.InheritedParentalRatingValue != null - && (e.InheritedParentalRatingValue < maxScore - || (e.InheritedParentalRatingValue == maxScore && (e.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))) - // Item has no rating - || (e.InheritedParentalRatingValue == null - && ( - // No linked children (not a BoxSet/Playlist): pass as unrated - !linkedChildren.Any(lc => lc.ParentId == e.Id) - // Has linked children: at least one child must be within limits - || linkedChildren.Any(lc => lc.ParentId == e.Id - && (lc.Child!.InheritedParentalRatingValue == null - || lc.Child.InheritedParentalRatingValue < maxScore - || (lc.Child.InheritedParentalRatingValue == maxScore - && (lc.Child.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))))); + maxParentalRatingFilter = BuildMaxParentalRatingFilter(context, filter.MaxParentalRating); } if (filter.HasParentalRating ?? false) @@ -4040,29 +4003,22 @@ public sealed class BaseItemRepository if (targetItem is not null) { var targetSortName = targetItem.SortName ?? string.Empty; - var prevId = context.BaseItems + + // Fetch both prev and next adjacent items in a single query using Concat (UNION ALL). + var adjacentIds = context.BaseItems .Where(e => string.Compare(e.SortName, targetSortName) < 0) .OrderByDescending(e => e.SortName) .Select(e => e.Id) - .FirstOrDefault(); - - var nextId = context.BaseItems - .Where(e => string.Compare(e.SortName, targetSortName) > 0) - .OrderBy(e => e.SortName) - .Select(e => e.Id) - .FirstOrDefault(); - - var adjacentIds = new List { adjacentToId }; - if (prevId != Guid.Empty) - { - adjacentIds.Add(prevId); - } - - if (nextId != Guid.Empty) - { - adjacentIds.Add(nextId); - } + .Take(1) + .Concat( + context.BaseItems + .Where(e => string.Compare(e.SortName, targetSortName) > 0) + .OrderBy(e => e.SortName) + .Select(e => e.Id) + .Take(1)) + .ToList(); + adjacentIds.Add(adjacentToId); baseQuery = baseQuery.Where(e => adjacentIds.Contains(e.Id)); } } @@ -4243,7 +4199,8 @@ public sealed class BaseItemRepository private static (int Played, int Total) GetPlayedAndTotalCountFromQuery(IQueryable query, Guid userId) { // GroupBy with a constant key aggregates all rows into a single group for server-side counting. - // OrderBy is required before FirstOrDefault to avoid EF Core warnings about unpredictable results. + // OrderBy(g => g.Key) is required before FirstOrDefault to suppress EF Core warnings + // about unpredictable results when using FirstOrDefault without an explicit ordering. var result = query .Select(b => b.UserData!.Any(u => u.UserId == userId && u.Played)) .GroupBy(_ => 1) @@ -4253,7 +4210,6 @@ public sealed class BaseItemRepository Total = g.Count(), Played = g.Count(isPlayed => isPlayed) }) - .OrderBy(_ => 1) .FirstOrDefault(); return result is null ? (0, 0) : (result.Played, result.Total); @@ -4340,25 +4296,7 @@ public sealed class BaseItemRepository // Apply parental rating filtering if (filter.MaxParentalRating is not null) { - var maxScore = filter.MaxParentalRating.Score; - var maxSubScore = filter.MaxParentalRating.SubScore ?? 0; - - baseQuery = baseQuery.Where(e => - // Item has a rating: check against limit - (e.InheritedParentalRatingValue != null - && (e.InheritedParentalRatingValue < maxScore - || (e.InheritedParentalRatingValue == maxScore && (e.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))) - // Item has no rating - || (e.InheritedParentalRatingValue == null - && ( - // No linked children (not a BoxSet/Playlist): pass as unrated - !context.LinkedChildren.Any(lc => lc.ParentId == e.Id) - // Has linked children: at least one child must be within limits - || context.LinkedChildren.Any(lc => lc.ParentId == e.Id - && (lc.Child!.InheritedParentalRatingValue == null - || lc.Child.InheritedParentalRatingValue < maxScore - || (lc.Child.InheritedParentalRatingValue == maxScore - && (lc.Child.InheritedParentalRatingSubValue ?? 0) <= maxSubScore)))))); + baseQuery = baseQuery.Where(BuildMaxParentalRatingFilter(context, filter.MaxParentalRating)); } // Apply block unrated items filtering @@ -4504,4 +4442,34 @@ public sealed class BaseItemRepository context.SaveChanges(); } + + /// + /// Builds a filter expression for max parental rating that handles both rated items + /// and unrated BoxSets/Playlists (which check linked children's ratings). + /// + private static Expression> BuildMaxParentalRatingFilter( + JellyfinDbContext context, + ParentalRatingScore maxRating) + { + var maxScore = maxRating.Score; + var maxSubScore = maxRating.SubScore ?? 0; + var linkedChildren = context.LinkedChildren; + + return e => + // Item has a rating: check against limit + (e.InheritedParentalRatingValue != null + && (e.InheritedParentalRatingValue < maxScore + || (e.InheritedParentalRatingValue == maxScore && (e.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))) + // Item has no rating + || (e.InheritedParentalRatingValue == null + && ( + // No linked children (not a BoxSet/Playlist): pass as unrated + !linkedChildren.Any(lc => lc.ParentId == e.Id) + // Has linked children: at least one child must be within limits + || linkedChildren.Any(lc => lc.ParentId == e.Id + && (lc.Child!.InheritedParentalRatingValue == null + || lc.Child.InheritedParentalRatingValue < maxScore + || (lc.Child.InheritedParentalRatingValue == maxScore + && (lc.Child.InheritedParentalRatingSubValue ?? 0) <= maxSubScore))))); + } } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs index 3bc36dca7a..43e6a8bc00 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs @@ -49,6 +49,33 @@ public static class DescendantQueryHelper return descendants.AsQueryable(); } + /// + /// Gets all owned descendant IDs for multiple parent items in a single traversal. + /// More efficient than calling per parent because + /// it performs one traversal for all seeds instead of N separate traversals. + /// + /// Database context. + /// Parent item IDs. + /// Set of all owned descendant item IDs (excluding the parent IDs themselves). + public static HashSet GetOwnedDescendantIdsBatch(JellyfinDbContext context, IReadOnlyList parentIds) + { + ArgumentNullException.ThrowIfNull(context); + ArgumentNullException.ThrowIfNull(parentIds); + + if (parentIds.Count == 0) + { + return []; + } + + var seedSet = new HashSet(parentIds); + var descendants = TraverseHierarchyDownOwned(context, seedSet); + + // Remove the seed IDs — callers want only descendants + descendants.ExceptWith(seedSet); + + return descendants; + } + /// /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria. /// Can be used in LINQ .Contains() expressions. -- cgit v1.2.3