Optimize internal querying of UserData, other fixes (#14795)

8 files changed, 107 insertions, 55 deletions

diff --git a/MediaBrowser.Controller/Entities/BaseItem.cs b/MediaBrowser.Controller/Entities/BaseItem.cs
index 67675e756..4989f0f3f 100644
--- a/MediaBrowser.Controller/Entities/BaseItem.cs
+++ b/MediaBrowser.Controller/Entities/BaseItem.cs
@@ -107,8 +107,15 @@ namespace MediaBrowser.Controller.Entities
             ProductionLocations = Array.Empty<string>();
             RemoteTrailers = Array.Empty<MediaUrl>();
             ExtraIds = Array.Empty<Guid>();
+            UserData = [];
         }
 
+        /// <summary>
+        /// Gets or Sets the user data collection as cached from the last Db query.
+        /// </summary>
+        [JsonIgnore]
+        public ICollection<UserData> UserData { get; set; }
+
         [JsonIgnore]
         public string PreferredMetadataCountryCode { get; set; }
 
diff --git a/MediaBrowser.Controller/Entities/Folder.cs b/MediaBrowser.Controller/Entities/Folder.cs
index b889e73e3..abb2aab63 100644
--- a/MediaBrowser.Controller/Entities/Folder.cs
+++ b/MediaBrowser.Controller/Entities/Folder.cs
@@ -42,6 +42,8 @@ namespace MediaBrowser.Controller.Entities
     /// </summary>
     public class Folder : BaseItem
     {
+        private IEnumerable<BaseItem> _children;
+
         public Folder()
         {
             LinkedChildren = Array.Empty<LinkedChild>();
@@ -108,11 +110,15 @@ namespace MediaBrowser.Controller.Entities
         }
 
         /// <summary>
-        /// Gets the actual children.
+        /// Gets or Sets the actual children.
         /// </summary>
         /// <value>The actual children.</value>
         [JsonIgnore]
-        public virtual IEnumerable<BaseItem> Children => LoadChildren();
+        public virtual IEnumerable<BaseItem> Children
+        {
+            get => _children ??= LoadChildren();
+            set => _children = value;
+        }
 
         /// <summary>
         /// Gets thread-safe access to all recursive children of this folder - without regard to user.
@@ -281,6 +287,7 @@ namespace MediaBrowser.Controller.Entities
         /// <returns>Task.</returns>
         public Task ValidateChildren(IProgress<double> progress, MetadataRefreshOptions metadataRefreshOptions, bool recursive = true, bool allowRemoveRoot = false, CancellationToken cancellationToken = default)
         {
+            Children = null; // invalidate cached children.
             return ValidateChildrenInternal(progress, recursive, true, allowRemoveRoot, metadataRefreshOptions, metadataRefreshOptions.DirectoryService, cancellationToken);
         }
 
@@ -288,6 +295,7 @@ namespace MediaBrowser.Controller.Entities
         {
             var dictionary = new Dictionary<Guid, BaseItem>();
 
+            Children = null; // invalidate cached children.
             var childrenList = Children.ToList();
 
             foreach (var child in childrenList)
@@ -526,6 +534,7 @@ namespace MediaBrowser.Controller.Entities
                 {
                     if (validChildrenNeedGeneration)
                     {
+                        Children = null; // invalidate cached children.
                         validChildren = Children.ToList();
                     }
 
@@ -568,6 +577,7 @@ namespace MediaBrowser.Controller.Entities
 
                 if (recursive && child is Folder folder)
                 {
+                    folder.Children = null; // invalidate cached children.
                     await folder.RefreshMetadataRecursive(folder.Children.Except([this, child]).ToList(), refreshOptions, true, progress, cancellationToken).ConfigureAwait(false);
                 }
             }
@@ -686,16 +696,22 @@ namespace MediaBrowser.Controller.Entities
                 IEnumerable<BaseItem> items;
                 Func<BaseItem, bool> filter = i => UserViewBuilder.Filter(i, user, query, UserDataManager, LibraryManager);
 
+                var totalCount = 0;
                 if (query.User is null)
                 {
                     items = GetRecursiveChildren(filter);
+                    totalCount = items.Count();
                 }
                 else
                 {
-                    items = GetRecursiveChildren(user, query);
+                    items = GetRecursiveChildren(user, query, out totalCount);
+                    query.Limit = null;
+                    query.StartIndex = null; // override these here as they have already been applied
                 }
 
-                return PostFilterAndSort(items, query);
+                var result = PostFilterAndSort(items, query);
+                result.TotalRecordCount = totalCount;
+                return result;
             }
 
             if (this is not UserRootFolder
@@ -944,22 +960,31 @@ namespace MediaBrowser.Controller.Entities
 
             IEnumerable<BaseItem> items;
 
+            int totalItemCount = 0;
             if (query.User is null)
             {
                 items = Children.Where(filter);
+                totalItemCount = items.Count();
             }
             else
             {
                 // need to pass this param to the children.
                 var childQuery = new InternalItemsQuery
                 {
-                    DisplayAlbumFolders = query.DisplayAlbumFolders
+                    DisplayAlbumFolders = query.DisplayAlbumFolders,
+                    Limit = query.Limit,
+                    StartIndex = query.StartIndex
                 };
 
-                items = GetChildren(user, true, childQuery).Where(filter);
+                items = GetChildren(user, true, out totalItemCount, childQuery).Where(filter);
+
+                query.Limit = null;
+                query.StartIndex = null;
             }
 
-            return PostFilterAndSort(items, query);
+            var result = PostFilterAndSort(items, query);
+            result.TotalRecordCount = totalItemCount;
+            return result;
         }
 
         protected QueryResult<BaseItem> PostFilterAndSort(IEnumerable<BaseItem> items, InternalItemsQuery query)
@@ -1242,30 +1267,30 @@ namespace MediaBrowser.Controller.Entities
             return true;
         }
 
-        public IReadOnlyList<BaseItem> GetChildren(User user, bool includeLinkedChildren)
-        {
-            ArgumentNullException.ThrowIfNull(user);
-
-            return GetChildren(user, includeLinkedChildren, new InternalItemsQuery(user));
-        }
-
-        public virtual IReadOnlyList<BaseItem> GetChildren(User user, bool includeLinkedChildren, InternalItemsQuery query)
+        public virtual IReadOnlyList<BaseItem> GetChildren(User user, bool includeLinkedChildren, out int totalItemCount, InternalItemsQuery query = null)
         {
             ArgumentNullException.ThrowIfNull(user);
+            query ??= new InternalItemsQuery();
+            query.User = user;
 
             // the true root should return our users root folder children
             if (IsPhysicalRoot)
             {
-                return LibraryManager.GetUserRootFolder().GetChildren(user, includeLinkedChildren);
+                return LibraryManager.GetUserRootFolder().GetChildren(user, includeLinkedChildren, out totalItemCount);
             }
 
             var result = new Dictionary<Guid, BaseItem>();
 
-            AddChildren(user, includeLinkedChildren, result, false, query);
+            totalItemCount = AddChildren(user, includeLinkedChildren, result, false, query);
 
             return result.Values.ToArray();
         }
 
+        public virtual IReadOnlyList<BaseItem> GetChildren(User user, bool includeLinkedChildren, InternalItemsQuery query = null)
+        {
+            return GetChildren(user, includeLinkedChildren, out _, query);
+        }
+
         protected virtual IEnumerable<BaseItem> GetEligibleChildrenForRecursiveChildren(User user)
         {
             return Children;
@@ -1274,13 +1299,13 @@ namespace MediaBrowser.Controller.Entities
         /// <summary>
         /// Adds the children to list.
         /// </summary>
-        private void AddChildren(User user, bool includeLinkedChildren, Dictionary<Guid, BaseItem> result, bool recursive, InternalItemsQuery query, HashSet<Folder> visitedFolders = null)
+        private int AddChildren(User user, bool includeLinkedChildren, Dictionary<Guid, BaseItem> result, bool recursive, InternalItemsQuery query, HashSet<Folder> visitedFolders = null)
         {
             // Prevent infinite recursion of nested folders
             visitedFolders ??= new HashSet<Folder>();
             if (!visitedFolders.Add(this))
             {
-                return;
+                return 0;
             }
 
             // If Query.AlbumFolders is set, then enforce the format as per the db in that it permits sub-folders in music albums.
@@ -1297,44 +1322,58 @@ namespace MediaBrowser.Controller.Entities
                 children = GetEligibleChildrenForRecursiveChildren(user);
             }
 
-            AddChildrenFromCollection(children, user, includeLinkedChildren, result, recursive, query, visitedFolders);
-
             if (includeLinkedChildren)
             {
-                AddChildrenFromCollection(GetLinkedChildren(user), user, includeLinkedChildren, result, recursive, query, visitedFolders);
+                children = children.Concat(GetLinkedChildren(user)).ToArray();
             }
+
+            return AddChildrenFromCollection(children, user, includeLinkedChildren, result, recursive, query, visitedFolders);
         }
 
-        private void AddChildrenFromCollection(IEnumerable<BaseItem> children, User user, bool includeLinkedChildren, Dictionary<Guid, BaseItem> result, bool recursive, InternalItemsQuery query, HashSet<Folder> visitedFolders)
+        private int AddChildrenFromCollection(IEnumerable<BaseItem> children, User user, bool includeLinkedChildren, Dictionary<Guid, BaseItem> result, bool recursive, InternalItemsQuery query, HashSet<Folder> visitedFolders)
         {
-            foreach (var child in children)
-            {
-                if (!child.IsVisible(user))
-                {
-                    continue;
-                }
+            query ??= new InternalItemsQuery();
+            var limit = query.Limit;
+            query.Limit = 100; // this is a bit of a dirty hack thats in favor of specifically the webUI as it does not show more then +99 elements in its badges so there is no point in reading more then that.
+
+            var visibileChildren = children
+                .Where(e => e.IsVisible(user))
+                .ToArray();
 
-                if (query is null || UserViewBuilder.FilterItem(child, query))
+            var realChildren = visibileChildren
+                .Where(e => query is null || UserViewBuilder.FilterItem(e, query))
+                .ToArray();
+            var childCount = realChildren.Count();
+            if (result.Count < query.Limit)
+            {
+                foreach (var child in realChildren
+                    .Skip(query.StartIndex ?? 0)
+                    .TakeWhile(e => query.Limit >= result.Count))
                 {
                     result[child.Id] = child;
                 }
+            }
 
-                if (recursive && child.IsFolder)
+            if (recursive)
+            {
+                foreach (var child in visibileChildren
+                    .Where(e => e.IsFolder)
+                    .OfType<Folder>())
                 {
-                    var folder = (Folder)child;
-
-                    folder.AddChildren(user, includeLinkedChildren, result, true, query, visitedFolders);
+                    childCount += child.AddChildren(user, includeLinkedChildren, result, true, query, visitedFolders);
                 }
             }
+
+            return childCount;
         }
 
-        public virtual IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query)
+        public virtual IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query, out int totalCount)
         {
             ArgumentNullException.ThrowIfNull(user);
 
             var result = new Dictionary<Guid, BaseItem>();
 
-            AddChildren(user, true, result, true, query);
+            totalCount = AddChildren(user, true, result, true, query);
 
             return result.Values.ToArray();
         }
@@ -1668,16 +1707,7 @@ namespace MediaBrowser.Controller.Entities
 
         public override bool IsPlayed(User user, UserItemData userItemData)
         {
-            var itemsResult = GetItemList(new InternalItemsQuery(user)
-            {
-                Recursive = true,
-                IsFolder = false,
-                IsVirtualItem = false,
-                EnableTotalRecordCount = false
-            });
-
-            return itemsResult
-                .All(i => i.IsPlayed(user, userItemData: null));
+            return ItemRepository.GetIsPlayed(user, Id, true);
         }
 
         public override bool IsUnplayed(User user, UserItemData userItemData)
diff --git a/MediaBrowser.Controller/Entities/Movies/BoxSet.cs b/MediaBrowser.Controller/Entities/Movies/BoxSet.cs
index dd5852823..1d1fb2c39 100644
--- a/MediaBrowser.Controller/Entities/Movies/BoxSet.cs
+++ b/MediaBrowser.Controller/Entities/Movies/BoxSet.cs
@@ -136,9 +136,9 @@ namespace MediaBrowser.Controller.Entities.Movies
             return Sort(children, user).ToArray();
         }
 
-        public override IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query)
+        public override IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query, out int totalCount)
         {
-            var children = base.GetRecursiveChildren(user, query);
+            var children = base.GetRecursiveChildren(user, query, out totalCount);
             return Sort(children, user).ToArray();
         }
 
diff --git a/MediaBrowser.Controller/Entities/TV/Season.cs b/MediaBrowser.Controller/Entities/TV/Season.cs
index 48211d99f..b972ebaa6 100644
--- a/MediaBrowser.Controller/Entities/TV/Season.cs
+++ b/MediaBrowser.Controller/Entities/TV/Season.cs
@@ -123,7 +123,7 @@ namespace MediaBrowser.Controller.Entities.TV
 
         public override int GetChildCount(User user)
         {
-            var result = GetChildren(user, true).Count;
+            var result = GetChildren(user, true, null).Count;
 
             return result;
         }
diff --git a/MediaBrowser.Controller/Entities/TV/Series.cs b/MediaBrowser.Controller/Entities/TV/Series.cs
index 62c73d56f..427c2995b 100644
--- a/MediaBrowser.Controller/Entities/TV/Series.cs
+++ b/MediaBrowser.Controller/Entities/TV/Series.cs
@@ -297,6 +297,7 @@ namespace MediaBrowser.Controller.Entities.TV
 
         public async Task RefreshAllMetadata(MetadataRefreshOptions refreshOptions, IProgress<double> progress, CancellationToken cancellationToken)
         {
+            Children = null; // invalidate cached children.
             // Refresh bottom up, seasons and episodes first, then the series
             var items = GetRecursiveChildren();
 
diff --git a/MediaBrowser.Controller/Entities/UserView.cs b/MediaBrowser.Controller/Entities/UserView.cs
index dfa31315c..5624f8b2e 100644
--- a/MediaBrowser.Controller/Entities/UserView.cs
+++ b/MediaBrowser.Controller/Entities/UserView.cs
@@ -89,7 +89,7 @@ namespace MediaBrowser.Controller.Entities
         /// <inheritdoc />
         public override int GetChildCount(User user)
         {
-            return GetChildren(user, true).Count;
+            return GetChildren(user, true, null).Count;
         }
 
         /// <inheritdoc />
@@ -134,20 +134,22 @@ namespace MediaBrowser.Controller.Entities
         }
 
         /// <inheritdoc />
-        public override IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query)
+        public override IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query, out int totalCount)
         {
             query.SetUser(user);
             query.Recursive = true;
             query.EnableTotalRecordCount = false;
             query.ForceDirect = true;
+            var data = GetItemList(query);
+            totalCount = data.Count;
 
-            return GetItemList(query);
+            return data;
         }
 
         /// <inheritdoc />
         protected override IReadOnlyList<BaseItem> GetEligibleChildrenForRecursiveChildren(User user)
         {
-            return GetChildren(user, false);
+            return GetChildren(user, false, null);
         }
 
         public static bool IsUserSpecific(Folder folder)
diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs
index a0dabbac6..e17dc38f7 100644
--- a/MediaBrowser.Controller/Persistence/IItemRepository.cs
+++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs
@@ -7,6 +7,7 @@ using System.Collections.Generic;
 using System.Threading;
 using System.Threading.Tasks;
 using Jellyfin.Data.Enums;
+using Jellyfin.Database.Implementations.Entities;
 using MediaBrowser.Controller.Entities;
 using MediaBrowser.Model.Dto;
 using MediaBrowser.Model.Querying;
@@ -112,4 +113,13 @@ public interface IItemRepository
     /// <param name="id">The id to check.</param>
     /// <returns>True if the item exists, otherwise false.</returns>
     Task<bool> ItemExistsAsync(Guid id);
+
+    /// <summary>
+    /// Gets a value indicating wherever all children of the requested Id has been played.
+    /// </summary>
+    /// <param name="user">The userdata to check against.</param>
+    /// <param name="id">The Top id to check.</param>
+    /// <param name="recursive">Whever the check should be done recursive. Warning expensive operation.</param>
+    /// <returns>A value indicating whever all children has been played.</returns>
+    bool GetIsPlayed(User user, Guid id, bool recursive);
 }
diff --git a/MediaBrowser.Controller/Playlists/Playlist.cs b/MediaBrowser.Controller/Playlists/Playlist.cs
index 1062399e3..fc367b829 100644
--- a/MediaBrowser.Controller/Playlists/Playlist.cs
+++ b/MediaBrowser.Controller/Playlists/Playlist.cs
@@ -149,9 +149,11 @@ namespace MediaBrowser.Controller.Playlists
             return [];
         }
 
-        public override IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query)
+        public override IReadOnlyList<BaseItem> GetRecursiveChildren(User user, InternalItemsQuery query, out int totalCount)
         {
-            return GetPlayableItems(user, query);
+            var items = GetPlayableItems(user, query);
+            totalCount = items.Count;
+            return items;
         }
 
         public IReadOnlyList<Tuple<LinkedChild, BaseItem>> GetManageableItems()