Implement search providers

2 files changed, 49 insertions, 29 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 380c6e582c..f557e3732a 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -133,21 +133,15 @@ public sealed partial class BaseItemRepository
             IsSeries = filter.IsSeries
         });
 
-        // Keep this as an IQueryable sub-select. Materializing to a list would inline one
-        // bound parameter per CleanValue and hit SQLite's variable cap on libraries with
-        // high-cardinality value types (e.g. tens of thousands of artists).
-        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();
-
+        // Use a correlated EXISTS rather than `IN (SELECT DISTINCT CleanValue ...)`. The
+        // IN-form would force materialization of the full set of artist CleanValues across the
+        // entire library before filtering.
         var innerQuery = PrepareItemQuery(context, filter)
             .Where(e => e.Type == returnType)
-            .Where(e => matchingCleanValues.Contains(e.CleanName!));
+            .Where(e => context.ItemValuesMap.Any(ivm =>
+                itemValueTypes.Contains(ivm.ItemValue.Type)
+                && ivm.ItemValue.CleanValue == e.CleanName
+                && innerQueryFilter.Any(g => g.Id == ivm.ItemId)));
 
         var outerQueryFilter = new InternalItemsQuery(filter.User)
         {
@@ -174,9 +168,42 @@ public sealed partial class BaseItemRepository
         // (e.g. alternate versions) by picking the lowest Id per group.
         var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter);
 
-        var orderedMasterQuery = ApplyOrder(masterQuery, filter, context)
-            .GroupBy(e => e.PresentationUniqueKey)
-            .Select(g => g.Min(e => e.Id));
+        IQueryable<Guid> orderedMasterQuery;
+        if (!string.IsNullOrEmpty(filter.SearchTerm))
+        {
+            var cleanSearchTerm = filter.SearchTerm.GetCleanValue();
+            var cleanSearchPrefix = cleanSearchTerm + " ";
+
+            orderedMasterQuery = masterQuery
+                .Select(e => new
+                {
+                    e.Id,
+                    e.PresentationUniqueKey,
+                    e.SortName,
+                    Score = (e.CleanName == cleanSearchTerm) ? 0
+                        : e.CleanName!.StartsWith(cleanSearchTerm) ? 1
+                        : e.CleanName!.Contains(cleanSearchPrefix) ? 2
+                        : 3
+                })
+                .GroupBy(x => x.PresentationUniqueKey)
+                .Select(g => new
+                {
+                    Id = g.Min(x => x.Id),
+                    Score = g.Min(x => x.Score),
+                    SortName = g.Min(x => x.SortName)
+                })
+                .OrderBy(x => x.Score)
+                .ThenBy(x => x.SortName)
+                .Select(x => x.Id);
+        }
+        else
+        {
+            orderedMasterQuery = masterQuery
+                .GroupBy(e => e.PresentationUniqueKey)
+                .Select(g => new { Id = g.Min(e => e.Id), SortName = g.Min(e => e.SortName) })
+                .OrderBy(x => x.SortName)
+                .Select(x => x.Id);
+        }
 
         var result = new QueryResult<(BaseItemDto, ItemCounts?)>();
         if (filter.EnableTotalRecordCount)
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index 0abe981af8..d3e49b58da 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -932,24 +932,17 @@ public sealed partial class BaseItemRepository
 
         if (filter.ExcludeProviderIds is not null && filter.ExcludeProviderIds.Count > 0)
         {
-            var exclude = filter.ExcludeProviderIds.Select(e => $"{e.Key}:{e.Value}").ToArray();
-            baseQuery = baseQuery.Where(e => e.Provider!.Select(f => f.ProviderId + ":" + f.ProviderValue)!.All(f => !exclude.Contains(f)));
+            baseQuery = baseQuery.WhereExcludeProviderIds(filter.ExcludeProviderIds);
         }
 
         if (filter.HasAnyProviderId is not null && filter.HasAnyProviderId.Count > 0)
         {
-            // Allow setting a null or empty value to get all items that have the specified provider set.
-            var includeAny = filter.HasAnyProviderId.Where(e => string.IsNullOrEmpty(e.Value)).Select(e => e.Key).ToArray();
-            if (includeAny.Length > 0)
-            {
-                baseQuery = baseQuery.Where(e => e.Provider!.Any(f => includeAny.Contains(f.ProviderId)));
-            }
+            baseQuery = baseQuery.WhereHasAnyProviderId(filter.HasAnyProviderId);
+        }
 
-            var includeSelected = filter.HasAnyProviderId.Where(e => !string.IsNullOrEmpty(e.Value)).Select(e => $"{e.Key}:{e.Value}").ToArray();
-            if (includeSelected.Length > 0)
-            {
-                baseQuery = baseQuery.Where(e => e.Provider!.Select(f => f.ProviderId + ":" + f.ProviderValue)!.Any(f => includeSelected.Contains(f)));
-            }
+        if (filter.HasAnyProviderIds is not null && filter.HasAnyProviderIds.Count > 0)
+        {
+            baseQuery = baseQuery.WhereHasAnyProviderIds(filter.HasAnyProviderIds);
         }
 
         if (filter.HasImdbId.HasValue)