Fix favorite filter performance

2 files changed, 32 insertions, 23 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 1d34346888..24892fef92 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -168,21 +168,16 @@ public sealed partial class BaseItemRepository
             IsSeries = filter.IsSeries
         });
 
-        // Resolve, then materialize, the set of clean values belonging to items that match the inner filter.
-        var matchingCleanValues = context.ItemValuesMap
-            .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type))
-            .Join(
-                innerQueryFilter,
-                ivm => ivm.ItemId,
-                g => g.Id,
-                (ivm, g) => ivm.ItemValue.CleanValue)
-            .Distinct()
-            .ToList();
-
-        // Match CleanName against the resolved clean values.
         var innerQuery = PrepareItemQuery(context, filter)
             .Where(e => e.Type == returnType)
-            .WhereOneOrMany(matchingCleanValues, e => e.CleanName!);
+            .Where(e => context.ItemValuesMap
+                .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type) && ivm.ItemValue.CleanValue == e.CleanName)
+                .Join(
+                    innerQueryFilter,
+                    ivm => ivm.ItemId,
+                    g => g.Id,
+                    (ivm, g) => ivm.ItemId)
+                .Any());
 
         var outerQueryFilter = new InternalItemsQuery(filter.User)
         {
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index de25e9b763..47f8a40b9c 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -434,20 +434,34 @@ 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)
+        if (filter.IsFavoriteOrLiked.HasValue || filter.IsFavorite.HasValue)
         {
-            var isFavorite = filter.IsFavorite.Value;
-            baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavorite);
+            var favoriteIds = context.UserData
+                .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)
+                .Select(ud => ud.ItemId);
+
+            if (filter.IsFavoriteOrLiked.HasValue)
+            {
+                baseQuery = filter.IsFavoriteOrLiked.Value
+                    ? baseQuery.Where(e => favoriteIds.Contains(e.Id))
+                    : baseQuery.Where(e => !favoriteIds.Contains(e.Id));
+            }
+
+            if (filter.IsFavorite.HasValue)
+            {
+                baseQuery = filter.IsFavorite.Value
+                    ? baseQuery.Where(e => favoriteIds.Contains(e.Id))
+                    : baseQuery.Where(e => !favoriteIds.Contains(e.Id));
+            }
         }
 
         if (filter.IsPlayed.HasValue)