Expose optimized ItemCounts for byName items

5 files changed, 282 insertions, 30 deletions

diff --git a/Emby.Server.Implementations/Library/LibraryManager.cs b/Emby.Server.Implementations/Library/LibraryManager.cs
index e6fa94fbef..0044fcd4dc 100644
--- a/Emby.Server.Implementations/Library/LibraryManager.cs
+++ b/Emby.Server.Implementations/Library/LibraryManager.cs
@@ -1801,6 +1801,18 @@ namespace Emby.Server.Implementations.Library
             return _countService.GetItemCountsForNameItem(kind, id, relatedItemKinds, query);
         }
 
+        /// <inheritdoc/>
+        public Dictionary<Guid, ItemCounts> GetItemCountsForNameItems(BaseItemKind kind, IReadOnlyList<Guid> ids, BaseItemKind[] relatedItemKinds, User? user)
+        {
+            var query = new InternalItemsQuery(user);
+            if (user is not null)
+            {
+                AddUserToQuery(query, user);
+            }
+
+            return _countService.GetItemCountsForNameItems(kind, ids, relatedItemKinds, query);
+        }
+
         public Dictionary<Guid, int> GetChildCountBatch(IReadOnlyList<Guid> parentIds, User? user)
         {
             return _countService.GetChildCountBatch(parentIds, user);
diff --git a/Jellyfin.Server.Implementations/Item/ItemCountService.cs b/Jellyfin.Server.Implementations/Item/ItemCountService.cs
index 704dc31fd0..8b0aac2836 100644
--- a/Jellyfin.Server.Implementations/Item/ItemCountService.cs
+++ b/Jellyfin.Server.Implementations/Item/ItemCountService.cs
@@ -7,6 +7,7 @@ using System.Linq;
 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.Controller.Persistence;
@@ -195,6 +196,56 @@ public class ItemCountService : IItemCountService
             .Select(x => new { x.Key, Count = x.Count() })
             .ToArray();
 
+        var result = BuildItemCounts(counts.Select(c => (c.Key, c.Count)));
+        var totalCount = result.ItemCount;
+
+        if (kind is BaseItemKind.Studio or BaseItemKind.Genre or BaseItemKind.MusicGenre
+            && relatedItemKinds.Contains(BaseItemKind.Episode)
+            && relatedItemKinds.Contains(BaseItemKind.Series))
+        {
+            var rolledUpEpisodeCount = CountEpisodesOfTaggedSeries(context, baseQuery, accessFilter, out var directEpisodeCount);
+            totalCount += rolledUpEpisodeCount - result.EpisodeCount + directEpisodeCount;
+            result.EpisodeCount = rolledUpEpisodeCount + directEpisodeCount;
+        }
+
+        result.ItemCount = totalCount;
+
+        return result;
+    }
+
+    private int CountEpisodesOfTaggedSeries(
+        JellyfinDbContext context,
+        IQueryable<BaseItemEntity> taggedItems,
+        InternalItemsQuery accessFilter,
+        out int unrelatedEpisodeCount)
+    {
+        var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
+        var episodeTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Episode];
+
+        var taggedSeriesIds = taggedItems.Where(e => e.Type == seriesTypeName).Select(e => e.Id);
+        unrelatedEpisodeCount = taggedItems.Count(e => e.Type == episodeTypeName
+            && (e.SeriesId == null || !taggedSeriesIds.Contains(e.SeriesId.Value)));
+
+        // Materialised so the episode count drives off IX_BaseItems_SeriesId.
+        var seriesIds = taggedItems
+            .Where(e => e.Type == seriesTypeName)
+            .Select(e => e.Id)
+            .ToArray();
+
+        if (seriesIds.Length == 0)
+        {
+            return 0;
+        }
+
+        var episodes = context.BaseItems.AsNoTracking()
+            .Where(e => e.Type == episodeTypeName && e.SeriesId != null)
+            .WhereOneOrMany(seriesIds, e => e.SeriesId!.Value);
+
+        return _queryHelpers.ApplyAccessFiltering(context, episodes, accessFilter).Count();
+    }
+
+    private ItemCounts BuildItemCounts(IEnumerable<(string Key, int Count)> counts)
+    {
         var lookup = _itemTypeLookup.BaseItemKindNames;
         var result = new ItemCounts();
         var totalCount = 0;
@@ -249,49 +300,100 @@ public class ItemCountService : IItemCountService
             }
         }
 
-        if (kind is BaseItemKind.Studio or BaseItemKind.Genre or BaseItemKind.MusicGenre
-            && relatedItemKinds.Contains(BaseItemKind.Episode)
-            && relatedItemKinds.Contains(BaseItemKind.Series))
-        {
-            var rolledUpEpisodeCount = CountEpisodesOfTaggedSeries(context, baseQuery, accessFilter, out var directEpisodeCount);
-            totalCount += rolledUpEpisodeCount - result.EpisodeCount + directEpisodeCount;
-            result.EpisodeCount = rolledUpEpisodeCount + directEpisodeCount;
-        }
-
         result.ItemCount = totalCount;
 
         return result;
     }
 
-    private int CountEpisodesOfTaggedSeries(
-        JellyfinDbContext context,
-        IQueryable<BaseItemEntity> taggedItems,
-        InternalItemsQuery accessFilter,
-        out int unrelatedEpisodeCount)
+    private static ItemValueType[] GetItemValueTypes(BaseItemKind kind)
+        => kind switch
+        {
+            BaseItemKind.MusicArtist => [ItemValueType.Artist, ItemValueType.AlbumArtist],
+            BaseItemKind.Genre or BaseItemKind.MusicGenre => [ItemValueType.Genre],
+            BaseItemKind.Studio => [ItemValueType.Studios],
+            _ => []
+        };
+
+    /// <inheritdoc />
+    public Dictionary<Guid, ItemCounts> GetItemCountsForNameItems(BaseItemKind kind, IReadOnlyList<Guid> ids, BaseItemKind[] relatedItemKinds, InternalItemsQuery accessFilter)
     {
-        var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
-        var episodeTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Episode];
+        ArgumentNullException.ThrowIfNull(ids);
+        ArgumentNullException.ThrowIfNull(relatedItemKinds);
 
-        var taggedSeriesIds = taggedItems.Where(e => e.Type == seriesTypeName).Select(e => e.Id);
-        unrelatedEpisodeCount = taggedItems.Count(e => e.Type == episodeTypeName
-            && (e.SeriesId == null || !taggedSeriesIds.Contains(e.SeriesId.Value)));
+        var result = new Dictionary<Guid, ItemCounts>();
+        if (ids.Count == 0)
+        {
+            return result;
+        }
 
-        // Materialised so the episode count drives off IX_BaseItems_SeriesId.
-        var seriesIds = taggedItems
-            .Where(e => e.Type == seriesTypeName)
-            .Select(e => e.Id)
+        // Only the kinds keyed by a cleaned item value can be grouped for every id in one query.
+        // Anything else, and the listing that rolls the episodes of a tagged series up into it,
+        // keeps the single item path, so a batch can never report a different number than it.
+        var valueTypes = GetItemValueTypes(kind);
+        var rollsUpEpisodes = kind is BaseItemKind.Studio or BaseItemKind.Genre or BaseItemKind.MusicGenre
+            && relatedItemKinds.Contains(BaseItemKind.Episode)
+            && relatedItemKinds.Contains(BaseItemKind.Series);
+
+        if (valueTypes.Length == 0 || rollsUpEpisodes)
+        {
+            foreach (var id in ids)
+            {
+                result[id] = GetItemCountsForNameItem(kind, id, relatedItemKinds, accessFilter);
+            }
+
+            return result;
+        }
+
+        using var context = _dbProvider.CreateDbContext();
+
+        var nameItems = context.BaseItems.AsNoTracking()
+            .Where(e => ids.Contains(e.Id))
+            .Select(e => new { e.Id, e.CleanName })
             .ToArray();
 
-        if (seriesIds.Length == 0)
+        foreach (var id in ids)
         {
-            return 0;
+            result[id] = new ItemCounts();
         }
 
-        var episodes = context.BaseItems.AsNoTracking()
-            .Where(e => e.Type == episodeTypeName && e.SeriesId != null)
-            .WhereOneOrMany(seriesIds, e => e.SeriesId!.Value);
+        var cleanNames = nameItems
+            .Select(n => n.CleanName)
+            .Where(n => n is not null)
+            .Distinct(StringComparer.Ordinal)
+            .ToArray();
 
-        return _queryHelpers.ApplyAccessFiltering(context, episodes, accessFilter).Count();
+        if (cleanNames.Length == 0)
+        {
+            return result;
+        }
+
+        var typeNames = relatedItemKinds.Select(k => _itemTypeLookup.BaseItemKindNames[k]).ToArray();
+
+        var related = _queryHelpers.ApplyAccessFiltering(
+            context,
+            context.BaseItems.AsNoTracking().Where(e => typeNames.Contains(e.Type)),
+            accessFilter);
+
+        var grouped = context.ItemValuesMap.AsNoTracking()
+            .Where(ivm => valueTypes.Contains(ivm.ItemValue.Type) && cleanNames.Contains(ivm.ItemValue.CleanValue))
+            .Join(related, ivm => ivm.ItemId, e => e.Id, (ivm, e) => new { ivm.ItemValue.CleanValue, e.Type })
+            .GroupBy(x => new { x.CleanValue, x.Type })
+            .Select(g => new { g.Key.CleanValue, g.Key.Type, Count = g.Count() })
+            .ToArray();
+
+        var byCleanName = grouped
+            .GroupBy(g => g.CleanValue, StringComparer.Ordinal)
+            .ToDictionary(g => g.Key, g => g.Select(x => (x.Type, x.Count)).ToArray(), StringComparer.Ordinal);
+
+        foreach (var nameItem in nameItems)
+        {
+            if (nameItem.CleanName is not null && byCleanName.TryGetValue(nameItem.CleanName, out var counts))
+            {
+                result[nameItem.Id] = BuildItemCounts(counts);
+            }
+        }
+
+        return result;
     }
 
     private static IQueryable<BaseItemEntity> ItemsById(JellyfinDbContext context, IQueryable<Guid> itemIds)
diff --git a/MediaBrowser.Controller/Library/ILibraryManager.cs b/MediaBrowser.Controller/Library/ILibraryManager.cs
index 9028b0d6b8..382559384a 100644
--- a/MediaBrowser.Controller/Library/ILibraryManager.cs
+++ b/MediaBrowser.Controller/Library/ILibraryManager.cs
@@ -760,6 +760,17 @@ namespace MediaBrowser.Controller.Library
         ItemCounts GetItemCountsForNameItem(BaseItemKind kind, Guid id, BaseItemKind[] relatedItemKinds, User? user);
 
         /// <summary>
+        /// Gets item counts for several "by-name" items of the same kind in one query, instead of
+        /// one query per item.
+        /// </summary>
+        /// <param name="kind">The kind of the name items.</param>
+        /// <param name="ids">The IDs of the name items.</param>
+        /// <param name="relatedItemKinds">The item kinds to count.</param>
+        /// <param name="user">The user for access filtering.</param>
+        /// <returns>The item counts of each requested id.</returns>
+        Dictionary<Guid, ItemCounts> GetItemCountsForNameItems(BaseItemKind kind, IReadOnlyList<Guid> ids, BaseItemKind[] relatedItemKinds, User? user);
+
+        /// <summary>
         /// Batch-fetches child counts for multiple parent folders.
         /// Returns the count of immediate children (non-recursive) for each parent.
         /// </summary>
diff --git a/MediaBrowser.Controller/Persistence/IItemCountService.cs b/MediaBrowser.Controller/Persistence/IItemCountService.cs
index 8ddf93e3e0..20b5cbe603 100644
--- a/MediaBrowser.Controller/Persistence/IItemCountService.cs
+++ b/MediaBrowser.Controller/Persistence/IItemCountService.cs
@@ -37,6 +37,16 @@ public interface IItemCountService
     ItemCounts GetItemCountsForNameItem(BaseItemKind kind, Guid id, BaseItemKind[] relatedItemKinds, InternalItemsQuery accessFilter);
 
     /// <summary>
+    /// Gets item counts for several "by-name" items of the same kind in one query.
+    /// </summary>
+    /// <param name="kind">The kind of the name items.</param>
+    /// <param name="ids">The IDs of the name items.</param>
+    /// <param name="relatedItemKinds">The item kinds to count.</param>
+    /// <param name="accessFilter">A pre-configured query with user access filtering settings.</param>
+    /// <returns>The item counts of each requested id.</returns>
+    Dictionary<Guid, ItemCounts> GetItemCountsForNameItems(BaseItemKind kind, IReadOnlyList<Guid> ids, BaseItemKind[] relatedItemKinds, InternalItemsQuery accessFilter);
+
+    /// <summary>
     /// Gets the count of played items that are descendants of the specified ancestor.
     /// </summary>
     /// <param name="filter">The query filter containing user access settings.</param>
diff --git a/tests/Jellyfin.Server.Implementations.Tests/Item/ItemCountServiceTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Item/ItemCountServiceTests.cs
index fea743f08e..d0e29e5142 100644
--- a/tests/Jellyfin.Server.Implementations.Tests/Item/ItemCountServiceTests.cs
+++ b/tests/Jellyfin.Server.Implementations.Tests/Item/ItemCountServiceTests.cs
@@ -1,8 +1,11 @@
 using System;
 using System.Collections.Generic;
+using System.Globalization;
 using System.Linq;
+using Jellyfin.Data.Enums;
 using Jellyfin.Database.Implementations;
 using Jellyfin.Database.Implementations.Entities;
+using Jellyfin.Database.Implementations.Enums;
 using Jellyfin.Database.Implementations.Locking;
 using Jellyfin.Database.Providers.Sqlite;
 using Jellyfin.Server.Implementations.Item;
@@ -53,9 +56,25 @@ public sealed class ItemCountServiceTests : IDisposable
                 It.IsAny<InternalItemsQuery>()))
             .Returns((JellyfinDbContext _, IQueryable<BaseItemEntity> query, InternalItemsQuery _) => query);
 
+        var typeLookup = new Mock<IItemTypeLookup>();
+        typeLookup.Setup(l => l.BaseItemKindNames).Returns(new Dictionary<BaseItemKind, string>
+        {
+            [BaseItemKind.Movie] = "Movie",
+            [BaseItemKind.Series] = "Series",
+            [BaseItemKind.Episode] = "Episode",
+            [BaseItemKind.MusicAlbum] = "MusicAlbum",
+            [BaseItemKind.MusicArtist] = "MusicArtist",
+            [BaseItemKind.MusicVideo] = "MusicVideo",
+            [BaseItemKind.Audio] = "Audio",
+            [BaseItemKind.Trailer] = "Trailer",
+            [BaseItemKind.BoxSet] = "BoxSet",
+            [BaseItemKind.Book] = "Book",
+            [BaseItemKind.LiveTvProgram] = "LiveTvProgram"
+        });
+
         _service = new ItemCountService(
             factory.Object,
-            new Mock<IItemTypeLookup>().Object,
+            typeLookup.Object,
             queryHelpers.Object);
     }
 
@@ -335,6 +354,104 @@ public sealed class ItemCountServiceTests : IDisposable
         };
     }
 
+    [Fact]
+    public void GetItemCountsForNameItems_MatchesCountingEachNameItemOnItsOwn()
+    {
+        // Three genres tagging a different number of movies each, plus one tagging nothing.
+        var genres = SeedGenres();
+
+        var filter = new InternalItemsQuery();
+        BaseItemKind[] related = [BaseItemKind.Movie, BaseItemKind.Series];
+
+        var batch = _service.GetItemCountsForNameItems(BaseItemKind.Genre, genres, related, filter);
+
+        // Every requested id is answered, so a caller can index the result without checking.
+        Assert.Equal(genres.Count, batch.Count);
+
+        foreach (var genreId in genres)
+        {
+            var single = _service.GetItemCountsForNameItem(BaseItemKind.Genre, genreId, related, filter);
+
+            Assert.Equal(single.MovieCount, batch[genreId].MovieCount);
+            Assert.Equal(single.SeriesCount, batch[genreId].SeriesCount);
+            Assert.Equal(single.ItemCount, batch[genreId].ItemCount);
+        }
+
+        // And the counts are the seeded ones rather than all zero, which would match trivially.
+        Assert.Equal([3, 2, 1, 0], genres.Select(g => batch[g].MovieCount).ToArray());
+    }
+
+    [Fact]
+    public void GetItemCountsForNameItems_UnknownId_CountsZero()
+    {
+        var unknown = Guid.NewGuid();
+
+        var batch = _service.GetItemCountsForNameItems(
+            BaseItemKind.Genre,
+            [unknown],
+            [BaseItemKind.Movie],
+            new InternalItemsQuery());
+
+        Assert.Equal(0, batch[unknown].ItemCount);
+    }
+
+    /// <summary>
+    /// Seeds four genres tagging three, two, one and no movies, in that order.
+    /// </summary>
+    /// <returns>The ids of the seeded genres.</returns>
+    private List<Guid> SeedGenres()
+    {
+        var genreIds = new List<Guid>();
+
+        using var context = CreateDbContext();
+
+        for (var i = 0; i < 4; i++)
+        {
+            var name = "genre-" + i.ToString(CultureInfo.InvariantCulture);
+            var genreId = Guid.NewGuid();
+            genreIds.Add(genreId);
+
+            var genre = CreateItem(genreId);
+            genre.Type = "Genre";
+            genre.Name = name;
+            genre.CleanName = name;
+            context.BaseItems.Add(genre);
+
+            var itemValue = new ItemValue
+            {
+                ItemValueId = Guid.NewGuid(),
+                Type = ItemValueType.Genre,
+                Value = name,
+                CleanValue = name
+            };
+            context.ItemValues.Add(itemValue);
+            context.SaveChanges();
+
+            // 3 movies for the first genre, 2 for the second, 1 for the third, none for the last.
+            for (var m = 0; m < 3 - i; m++)
+            {
+                var movieId = Guid.NewGuid();
+                var movie = CreateItem(movieId);
+                movie.Type = "Movie";
+                movie.IsFolder = false;
+                context.BaseItems.Add(movie);
+                context.SaveChanges();
+
+                context.ItemValuesMap.Add(new ItemValueMap
+                {
+                    ItemId = movieId,
+                    ItemValueId = itemValue.ItemValueId,
+                    Item = null!,
+                    ItemValue = null!
+                });
+            }
+
+            context.SaveChanges();
+        }
+
+        return genreIds;
+    }
+
     private static BaseItemEntity CreateItem(Guid id, Guid? parentId = null)
     {
         return new BaseItemEntity