diff options
Diffstat (limited to 'Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs')
| -rw-r--r-- | Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs | 283 |
1 files changed, 106 insertions, 177 deletions
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs index 1198f99473..4be9b04baa 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, @@ -352,7 +356,7 @@ public sealed partial class BaseItemRepository } else { - baseQuery = baseQuery.Where(e => e.StartDate > now && e.EndDate < now); + baseQuery = baseQuery.Where(e => e.StartDate > now || e.EndDate < now); } } @@ -366,14 +370,16 @@ public sealed partial class BaseItemRepository p => p.Name, (b, p) => p.Id); + var personTypes = filter.PersonTypes; baseQuery = baseQuery .Where(e => context.PeopleBaseItemMap - .Any(m => m.ItemId == e.Id && peopleEntityIds.Contains(m.PeopleId))); + .Any(m => m.ItemId == e.Id && peopleEntityIds.Contains(m.PeopleId) && (personTypes.Length == 0 || personTypes.Contains(m.People.PersonType)))); } if (!string.IsNullOrWhiteSpace(filter.Person)) { - baseQuery = baseQuery.Where(e => e.Peoples!.Any(f => f.People.Name == filter.Person)); + var personTypes = filter.PersonTypes; + baseQuery = baseQuery.Where(e => e.Peoples!.Any(f => f.People.Name == filter.Person && (personTypes.Length == 0 || personTypes.Contains(f.People.PersonType)))); } if (!string.IsNullOrWhiteSpace(filter.ExternalSeriesId)) @@ -434,115 +440,82 @@ public sealed partial class BaseItemRepository if (filter.IsLiked.HasValue) { - var isLiked = filter.IsLiked.Value; - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue) == isLiked); - } + var likedIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue) + .Select(ud => ud.ItemId); - if (filter.IsFavoriteOrLiked.HasValue) - { - var isFavoriteOrLiked = filter.IsFavoriteOrLiked.Value; - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavoriteOrLiked); + baseQuery = filter.IsLiked.Value + ? baseQuery.Where(e => likedIds.Contains(e.Id)) + : baseQuery.Where(e => !likedIds.Contains(e.Id)); } - if (filter.IsFavorite.HasValue) - { - 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) + if (filter.IsFavoriteOrLiked.HasValue || filter.IsFavorite.HasValue) { - var hasSeries = filter.IncludeItemTypes.Contains(BaseItemKind.Series); - var hasBoxSet = filter.IncludeItemTypes.Contains(BaseItemKind.BoxSet); + var favoriteIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) + .Select(ud => ud.ItemId); - if (hasSeries || hasBoxSet) + if (filter.IsFavoriteOrLiked.HasValue) { - 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))); - } + baseQuery = filter.IsFavoriteOrLiked.Value + ? baseQuery.Where(e => favoriteIds.Contains(e.Id)) + : baseQuery.Where(e => !favoriteIds.Contains(e.Id)); } - else + + if (filter.IsFavorite.HasValue) { - 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); + baseQuery = filter.IsFavorite.Value + ? baseQuery.Where(e => favoriteIds.Contains(e.Id)) + : baseQuery.Where(e => !favoriteIds.Contains(e.Id)); } } + if (filter.IsPlayed.HasValue) + { + var userId = filter.User!.Id; + + // 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); - } + // Series and Seasons 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)); + + // Every other folder kind is a container rather than one continuous piece of media + var resumableFolderTypes = _resumableFolderKinds + .Select(kind => _itemTypeLookup.BaseItemKindNames.GetValueOrDefault(kind)) + .ToArray(); + var folderIsResumableFilter = IsFolderFilter.And(e => resumableFolderTypes.Contains(e.Type)) + .And(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) { @@ -550,26 +523,26 @@ 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(folderIsResumableFilter + .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. - baseQuery = baseQuery.Where(e => e.Type == seriesTypeName || !context.BaseItems - .Where(s => s.Id != e.Id - && inProgressIds.Contains(s.Id) - && (s.PrimaryVersionId ?? s.Id) == (e.PrimaryVersionId ?? e.Id)) - .Any(s => - inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) - > inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) - || (inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) - == inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) - && s.Id.CompareTo(e.Id) < 0))); + // 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.IsFolder + || (e.PrimaryVersionId == null && !context.BaseItems.Any(a => a.PrimaryVersionId == e.Id)) + || !context.BaseItems + .Where(s => s.Id != e.Id + && inProgressIds.Contains(s.Id) + && (s.PrimaryVersionId ?? s.Id) == (e.PrimaryVersionId ?? e.Id)) + .Any(s => + inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) + > inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) + || (inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) + == inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) + && s.Id.CompareTo(e.Id) < 0))); } else { @@ -577,17 +550,14 @@ 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(folderIsResumableFilter.Not()) + .Or(IsFolderFilter.Not().And(e => !resumableMovieIds.Contains(e.Id)))); } } if (filter.ArtistIds.Length > 0) { - baseQuery = baseQuery.WhereReferencedItemMultipleTypes(context, [ItemValueType.Artist, ItemValueType.AlbumArtist], filter.ArtistIds); + baseQuery = baseQuery.WhereReferencedItem(context, [ItemValueType.Artist, ItemValueType.AlbumArtist], filter.ArtistIds); } if (filter.AlbumArtistIds.Length > 0) @@ -618,12 +588,12 @@ public sealed partial class BaseItemRepository if (filter.ExcludeArtistIds.Length > 0) { - baseQuery = baseQuery.WhereReferencedItemMultipleTypes(context, [ItemValueType.Artist, ItemValueType.AlbumArtist], filter.ExcludeArtistIds, true); + baseQuery = baseQuery.WhereReferencedItem(context, [ItemValueType.Artist, ItemValueType.AlbumArtist], filter.ExcludeArtistIds, true); } if (filter.GenreIds.Count > 0) { - baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Genre, filter.GenreIds.ToArray()); + baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Genre, filter.GenreIds); } if (filter.Genres.Count > 0) @@ -649,7 +619,7 @@ public sealed partial class BaseItemRepository if (filter.StudioIds.Length > 0) { - baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Studios, filter.StudioIds.ToArray()); + baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Studios, filter.StudioIds); } if (filter.OfficialRatings.Length > 0) @@ -995,17 +965,6 @@ public sealed partial class BaseItemRepository baseQuery = baseQuery.WhereHasAnyProviderIds(filter.HasAnyProviderIds); } - if (filter.HasAnyProviderIds is not null && filter.HasAnyProviderIds.Count > 0) - { - var includeAny = filter.HasAnyProviderIds - .SelectMany(kvp => kvp.Value.Select(v => $"{kvp.Key}:{v}")) - .ToArray(); - if (includeAny.Length > 0) - { - baseQuery = baseQuery.Where(e => e.Provider!.Select(f => f.ProviderId + ":" + f.ProviderValue)!.Any(f => includeAny.Contains(f))); - } - } - if (filter.HasImdbId.HasValue) { baseQuery = filter.HasImdbId.Value @@ -1027,21 +986,7 @@ public sealed partial class BaseItemRepository : baseQuery.Where(e => e.Provider!.All(f => f.ProviderId.ToLower() != TvdbProviderName)); } - var queryTopParentIds = filter.TopParentIds; - - if (queryTopParentIds.Length > 0) - { - var includedItemByNameTypes = GetItemByNameTypesInQuery(filter); - var enableItemsByName = (filter.IncludeItemsByName ?? false) && includedItemByNameTypes.Count > 0; - if (enableItemsByName && includedItemByNameTypes.Count > 0) - { - baseQuery = baseQuery.Where(e => includedItemByNameTypes.Contains(e.Type) || queryTopParentIds.Any(w => w == e.TopParentId!.Value)); - } - else - { - baseQuery = baseQuery.WhereOneOrMany(queryTopParentIds, e => e.TopParentId!.Value); - } - } + baseQuery = ApplyTopParentFiltering(context, baseQuery, filter); if (filter.AncestorIds.Length > 0) { @@ -1089,6 +1034,7 @@ public sealed partial class BaseItemRepository { var includeTags = filter.IncludeInheritedTags.Select(e => e.GetCleanValue()).ToArray(); var isPlaylistOnlyQuery = includeTypes.Length == 1 && includeTypes.FirstOrDefault() == BaseItemKind.Playlist; + var personTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Person]; var allowedTagItemIds = context.ItemValuesMap .Where(f => f.ItemValue.Type == ItemValueType.Tags && includeTags.Contains(f.ItemValue.CleanValue)) .Select(f => f.ItemId); @@ -1099,6 +1045,9 @@ public sealed partial class BaseItemRepository || e.Parents!.Any(p => allowedTagItemIds.Contains(p.ParentItemId)) || (e.TopParentId.HasValue && allowedTagItemIds.Contains(e.TopParentId.Value)) + // People don't carry the tags of the media they appear in and would never match + || e.Type == personTypeName + // A playlist should be accessible to its owner regardless of allowed tags || (isPlaylistOnlyQuery && e.Data!.Contains($"OwnerUserId\":\"{filter.User!.Id:N}\""))); } @@ -1146,16 +1095,23 @@ public sealed partial class BaseItemRepository : baseQuery.WhereNeitherItemNorDescendantMatches(context, isPlaceHolder); } + // An extra is owned by the single version of an item it is named after, so an extra on any + // version counts for the item itself + IQueryable<Guid> WithPrimaryVersions(IQueryable<Guid> ownerIds) + => ownerIds.Concat(context.BaseItems + .Where(version => version.PrimaryVersionId != null && ownerIds.Contains(version.Id)) + .Select(version => version.PrimaryVersionId!.Value)); + if (filter.HasSpecialFeature.HasValue) { - var itemsWithExtras = context.BaseItems + var itemsWithExtras = WithPrimaryVersions(context.BaseItems .Where(extra => extra.OwnerId != null && extra.ExtraType != null && extra.ExtraType != BaseItemExtraType.Unknown && extra.ExtraType != BaseItemExtraType.Trailer && extra.ExtraType != BaseItemExtraType.ThemeSong && extra.ExtraType != BaseItemExtraType.ThemeVideo) - .Select(extra => extra.OwnerId!.Value) + .Select(extra => extra.OwnerId!.Value)) .Distinct(); Expression<Func<BaseItemEntity, bool>> hasExtras = e => itemsWithExtras.Contains(e.Id); @@ -1167,9 +1123,9 @@ public sealed partial class BaseItemRepository if (filter.HasTrailer.HasValue) { - var trailerOwnerIds = context.BaseItems + var trailerOwnerIds = WithPrimaryVersions(context.BaseItems .Where(extra => extra.ExtraType == BaseItemExtraType.Trailer && extra.OwnerId != null) - .Select(extra => extra.OwnerId!.Value); + .Select(extra => extra.OwnerId!.Value)); Expression<Func<BaseItemEntity, bool>> hasTrailer = e => trailerOwnerIds.Contains(e.Id); @@ -1180,9 +1136,9 @@ public sealed partial class BaseItemRepository if (filter.HasThemeSong.HasValue) { - var themeSongOwnerIds = context.BaseItems + var themeSongOwnerIds = WithPrimaryVersions(context.BaseItems .Where(extra => extra.ExtraType == BaseItemExtraType.ThemeSong && extra.OwnerId != null) - .Select(extra => extra.OwnerId!.Value); + .Select(extra => extra.OwnerId!.Value)); Expression<Func<BaseItemEntity, bool>> hasThemeSong = e => themeSongOwnerIds.Contains(e.Id); @@ -1193,9 +1149,9 @@ public sealed partial class BaseItemRepository if (filter.HasThemeVideo.HasValue) { - var themeVideoOwnerIds = context.BaseItems + var themeVideoOwnerIds = WithPrimaryVersions(context.BaseItems .Where(extra => extra.ExtraType == BaseItemExtraType.ThemeVideo && extra.OwnerId != null) - .Select(extra => extra.OwnerId!.Value); + .Select(extra => extra.OwnerId!.Value)); Expression<Func<BaseItemEntity, bool>> hasThemeVideo = e => themeVideoOwnerIds.Contains(e.Id); @@ -1222,33 +1178,6 @@ public sealed partial class BaseItemRepository } } - if (filter.AdjacentTo.HasValue && !filter.AdjacentTo.Value.IsEmpty()) - { - var adjacentToId = filter.AdjacentTo.Value; - var targetItem = context.BaseItems.Where(e => e.Id == adjacentToId).Select(e => new { e.SortName, e.Id }).FirstOrDefault(); - if (targetItem is not null) - { - var targetSortName = targetItem.SortName ?? string.Empty; - - // 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) - .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)); - } - } - return baseQuery; } } |