diff options
Diffstat (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs')
| -rw-r--r-- | Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs | 541 |
1 files changed, 541 insertions, 0 deletions
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs new file mode 100644 index 0000000000..d4cdfdbc3e --- /dev/null +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs @@ -0,0 +1,541 @@ +#pragma warning disable RS0030 // Do not use banned APIs +#pragma warning disable CA1304 // Specify CultureInfo +#pragma warning disable CA1311 // Specify a culture or use an invariant version +#pragma warning disable CA1862 // Use the 'StringComparison' method overloads to perform case-insensitive string comparisons + +using System; +using System.Collections.Generic; +using System.Linq; +using System.Linq.Expressions; +using Jellyfin.Data.Enums; +using Jellyfin.Database.Implementations; +using Jellyfin.Database.Implementations.Entities; +using Jellyfin.Database.Implementations.Enums; +using Jellyfin.Extensions; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Model.Entities; +using MediaBrowser.Model.Querying; +using Microsoft.EntityFrameworkCore; +using BaseItemEntity = Jellyfin.Database.Implementations.Entities.BaseItemEntity; + +namespace Jellyfin.Server.Implementations.Item; + +public sealed partial class BaseItemRepository +{ + /// <inheritdoc /> + public IQueryable<BaseItemEntity> PrepareItemQuery(JellyfinDbContext context, InternalItemsQuery filter) + { + IQueryable<BaseItemEntity> dbQuery = context.BaseItems.AsNoTracking(); + dbQuery = dbQuery.AsSingleQuery(); + + return dbQuery; + } + + private IQueryable<BaseItemEntity> ApplyQueryFilter(IQueryable<BaseItemEntity> dbQuery, JellyfinDbContext context, InternalItemsQuery filter) + { + dbQuery = TranslateQuery(dbQuery, context, filter); + dbQuery = ApplyGroupingFilter(context, dbQuery, filter); + dbQuery = ApplyQueryPaging(dbQuery, filter); + dbQuery = ApplyNavigations(dbQuery, filter); + return dbQuery; + } + + private IQueryable<BaseItemEntity> ApplyQueryPaging(IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter) + { + if (filter.Limit.HasValue || filter.StartIndex.HasValue) + { + var offset = filter.StartIndex ?? 0; + + if (offset > 0) + { + dbQuery = dbQuery.Skip(offset); + } + + if (filter.Limit.HasValue) + { + dbQuery = dbQuery.Take(filter.Limit.Value); + } + } + + return dbQuery; + } + + private IQueryable<BaseItemEntity> ApplyGroupingFilter(JellyfinDbContext context, IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter) + { + // This whole block is needed to filter duplicate entries on request + // for the time being it cannot be used because it would destroy the ordering + // this results in "duplicate" responses for queries that try to lookup individual series or multiple versions but + // for that case the invoker has to run a DistinctBy(e => e.PresentationUniqueKey) on their own + + var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter); + if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey) + { + var tempQuery = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + } + else if (enableGroupByPresentationUniqueKey) + { + var tempQuery = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + } + else if (filter.GroupBySeriesPresentationUniqueKey) + { + var tempQuery = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.OrderBy(x => x.Id).FirstOrDefault()).Select(e => e!.Id); + dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id)); + } + else + { + dbQuery = dbQuery.Distinct(); + } + + if (filter.CollapseBoxSetItems == true) + { + dbQuery = ApplyBoxSetCollapsing(context, dbQuery, filter.CollapseBoxSetItemTypes); + + // Apply name-range filters after collapse so BoxSets are filtered by their own name, + // not by their children's names. + dbQuery = ApplyNameFilters(dbQuery, filter); + } + + dbQuery = ApplyOrder(dbQuery, filter, context); + + return dbQuery; + } + + private IQueryable<BaseItemEntity> ApplyBoxSetCollapsing( + JellyfinDbContext context, + IQueryable<BaseItemEntity> 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 == Jellyfin.Database.Implementations.Entities.LinkedChildType.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 == Jellyfin.Database.Implementations.Entities.LinkedChildType.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<BaseItemEntity> ApplyBoxSetCollapsingAll( + JellyfinDbContext context, + IQueryable<Guid> currentIds, + string boxSetTypeName) + { + // Items that are NOT box sets and NOT in any box set + var notInBoxSet = currentIds + .Where(id => + !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName) + && !context.LinkedChildren.Any(lc => + lc.ChildId == id + && lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))); + + // Box set IDs containing at least one accessible child item + var boxSetIds = context.LinkedChildren + .Where(lc => + lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual + && currentIds.Contains(lc.ChildId) + && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)) + .Select(lc => lc.ParentId) + .Distinct(); + + var collapsedIds = notInBoxSet.Union(boxSetIds); + return context.BaseItems.Where(e => collapsedIds.Contains(e.Id)); + } + + private static IQueryable<BaseItemEntity> ApplyNameFilters(IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter) + { + if (!string.IsNullOrWhiteSpace(filter.NameStartsWith)) + { + var nameStartsWithLower = filter.NameStartsWith.ToLowerInvariant(); + dbQuery = dbQuery.Where(e => e.SortName!.ToLower().StartsWith(nameStartsWithLower)); + } + + if (!string.IsNullOrWhiteSpace(filter.NameStartsWithOrGreater)) + { + var startsOrGreaterLower = filter.NameStartsWithOrGreater.ToLowerInvariant(); + dbQuery = dbQuery.Where(e => e.SortName!.ToLower().CompareTo(startsOrGreaterLower) >= 0); + } + + if (!string.IsNullOrWhiteSpace(filter.NameLessThan)) + { + var lessThanLower = filter.NameLessThan.ToLowerInvariant(); + dbQuery = dbQuery.Where(e => e.SortName!.ToLower().CompareTo(lessThanLower) < 0); + } + + return dbQuery; + } + + /// <inheritdoc /> + public IQueryable<BaseItemEntity> ApplyNavigations(IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter) + { + if (filter.TrailerTypes.Length > 0 || filter.IncludeItemTypes.Contains(BaseItemKind.Trailer)) + { + dbQuery = dbQuery.Include(e => e.TrailerTypes); + } + + if (filter.DtoOptions.ContainsField(ItemFields.ProviderIds)) + { + dbQuery = dbQuery.Include(e => e.Provider); + } + + if (filter.DtoOptions.ContainsField(ItemFields.Settings)) + { + dbQuery = dbQuery.Include(e => e.LockedFields); + } + + if (filter.DtoOptions.EnableUserData) + { + dbQuery = dbQuery.Include(e => e.UserData); + } + + if (filter.DtoOptions.EnableImages) + { + dbQuery = dbQuery.Include(e => e.Images); + } + + // 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, + BaseItemKind.Playlist, + BaseItemKind.CollectionFolder, + BaseItemKind.Video, + BaseItemKind.Movie + }; + if (filter.IncludeItemTypes.Length == 0 || filter.IncludeItemTypes.Any(linkedChildTypes.Contains)) + { + dbQuery = dbQuery.Include(e => e.LinkedChildEntities); + } + + if (filter.IncludeExtras) + { + dbQuery = dbQuery.Include(e => e.Extras); + } + + return dbQuery; + } + + /// <inheritdoc /> + public IQueryable<BaseItemEntity> ApplyOrder(IQueryable<BaseItemEntity> query, InternalItemsQuery filter, JellyfinDbContext context) + { + 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) + { + var relevanceExpression = OrderMapper.MapSearchRelevanceOrder(filter.SearchTerm!); + orderedQuery = query.OrderBy(relevanceExpression); + } + + if (orderBy.Length > 0) + { + var firstOrdering = orderBy[0]; + var expression = OrderMapper.MapOrderByField(firstOrdering.OrderBy, filter, context); + + if (orderedQuery is null) + { + orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending + ? query.OrderBy(expression) + : query.OrderByDescending(expression); + } + else + { + orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending + ? orderedQuery.ThenBy(expression) + : orderedQuery.ThenByDescending(expression); + } + + if (firstOrdering.OrderBy is ItemSortBy.Default or ItemSortBy.SortName) + { + orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending + ? orderedQuery.ThenBy(e => e.Name) + : orderedQuery.ThenByDescending(e => e.Name); + } + + foreach (var item in orderBy.Skip(1)) + { + expression = OrderMapper.MapOrderByField(item.OrderBy, filter, context); + orderedQuery = item.SortOrder == SortOrder.Ascending + ? orderedQuery.ThenBy(expression) + : orderedQuery.ThenByDescending(expression); + } + } + + if (orderedQuery is null) + { + return query.OrderBy(e => e.SortName); + } + + // Add SortName as final tiebreaker + if (!hasSearch && (orderBy.Length == 0 || orderBy.All(o => o.OrderBy is not ItemSortBy.SortName and not ItemSortBy.Name))) + { + orderedQuery = orderedQuery.ThenBy(e => e.SortName); + } + + 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); + } + + /// <summary> + /// Builds a query for descendants of an ancestor with user access filtering applied. + /// Uses recursive CTE to traverse both hierarchical (AncestorIds) and linked (LinkedChildren) relationships. + /// </summary> + /// <inheritdoc /> + public IQueryable<BaseItemEntity> BuildAccessFilteredDescendantsQuery( + JellyfinDbContext context, + InternalItemsQuery filter, + Guid ancestorId) + { + // Use recursive CTE to get all descendants (hierarchical and linked) + var allDescendantIds = DescendantQueryHelper.GetAllDescendantIds(context, ancestorId); + + var baseQuery = context.BaseItems + .Where(b => allDescendantIds.Contains(b.Id) && !b.IsFolder && !b.IsVirtualItem); + + return ApplyAccessFiltering(context, baseQuery, filter); + } + + /// <summary> + /// Applies user access filtering to a query. + /// Includes TopParentIds, parental rating, and tag filtering. + /// </summary> + /// <inheritdoc /> + public IQueryable<BaseItemEntity> ApplyAccessFiltering( + JellyfinDbContext context, + IQueryable<BaseItemEntity> baseQuery, + InternalItemsQuery filter) + { + // Apply TopParentIds filtering (library folder access) + if (filter.TopParentIds.Length > 0) + { + var topParentIds = filter.TopParentIds; + baseQuery = baseQuery.Where(e => topParentIds.Contains(e.TopParentId!.Value)); + } + + // Apply parental rating filtering + if (filter.MaxParentalRating is not null) + { + baseQuery = baseQuery.Where(BuildMaxParentalRatingFilter(context, filter.MaxParentalRating)); + } + + // Apply block unrated items filtering + if (filter.BlockUnratedItems.Length > 0) + { + var unratedItemTypes = filter.BlockUnratedItems.Select(f => f.ToString()).ToArray(); + baseQuery = baseQuery.Where(e => + e.InheritedParentalRatingValue != null || !unratedItemTypes.Contains(e.UnratedType)); + } + + // Apply excluded tags filtering (blocked tags) + if (filter.ExcludeInheritedTags.Length > 0) + { + var excludedTags = filter.ExcludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); + baseQuery = baseQuery.Where(e => + !context.ItemValuesMap.Any(f => + f.ItemValue.Type == ItemValueType.Tags + && excludedTags.Contains(f.ItemValue.CleanValue) + && (f.ItemId == e.Id + || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) + || e.Parents!.Any(p => f.ItemId == p.ParentItemId) + || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); + } + + // Apply included tags filtering (allowed tags - item must have at least one) + if (filter.IncludeInheritedTags.Length > 0) + { + var includeTags = filter.IncludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); + baseQuery = baseQuery.Where(e => + context.ItemValuesMap.Any(f => + f.ItemValue.Type == ItemValueType.Tags + && includeTags.Contains(f.ItemValue.CleanValue) + && (f.ItemId == e.Id + || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value) + || e.Parents!.Any(p => f.ItemId == p.ParentItemId) + || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))); + } + + return baseQuery; + } + + /// <summary> + /// Builds a filter expression for max parental rating that handles both rated items + /// and unrated BoxSets/Playlists (which check linked children's ratings). + /// </summary> + private static Expression<Func<BaseItemEntity, bool>> 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))))); + } + + private Dictionary<Guid, (int Played, int Total)> GetPlayedAndTotalCountBatch(IReadOnlyList<Guid> folderIds, User user) + { + ArgumentNullException.ThrowIfNull(folderIds); + ArgumentNullException.ThrowIfNull(user); + + if (folderIds.Count == 0) + { + return new Dictionary<Guid, (int Played, int Total)>(); + } + + using var dbContext = _dbProvider.CreateDbContext(); + var folderIdsArray = folderIds.ToArray(); + var filter = new InternalItemsQuery(user); + var userId = user.Id; + + var leafItems = dbContext.BaseItems + .Where(b => !b.IsFolder && !b.IsVirtualItem); + leafItems = ApplyAccessFiltering(dbContext, leafItems, filter); + + var playedLeafItems = leafItems + .Select(b => new { b.Id, Played = b.UserData!.Any(ud => ud.UserId == userId && ud.Played) }); + + var ancestorLeaves = dbContext.AncestorIds + .WhereOneOrMany(folderIdsArray, a => a.ParentItemId) + .Join( + playedLeafItems, + a => a.ItemId, + b => b.Id, + (a, b) => new { FolderId = a.ParentItemId, b.Id, b.Played }); + + var linkedLeaves = dbContext.LinkedChildren + .WhereOneOrMany(folderIdsArray, lc => lc.ParentId) + .Join( + playedLeafItems, + lc => lc.ChildId, + b => b.Id, + (lc, b) => new { FolderId = lc.ParentId, b.Id, b.Played }); + + var linkedFolderLeaves = dbContext.LinkedChildren + .WhereOneOrMany(folderIdsArray, lc => lc.ParentId) + .Join( + dbContext.BaseItems.Where(b => b.IsFolder), + lc => lc.ChildId, + b => b.Id, + (lc, b) => new { lc.ParentId, FolderChildId = b.Id }) + .Join( + dbContext.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 }); + + var results = ancestorLeaves + .Union(linkedLeaves) + .Union(linkedFolderLeaves) + .GroupBy(x => x.FolderId) + .Select(g => new + { + FolderId = g.Key, + Total = g.Select(x => x.Id).Distinct().Count(), + Played = g.Where(x => x.Played).Select(x => x.Id).Distinct().Count() + }) + .ToDictionary(x => x.FolderId, x => (x.Played, x.Total)); + + return results; + } +} |