aboutsummaryrefslogtreecommitdiff
path: root/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
diff options
context:
space:
mode:
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs')
-rw-r--r--MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs81
1 files changed, 51 insertions, 30 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
index 4e851053b..02f5483c5 100644
--- a/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
+++ b/MediaBrowser.Server.Implementations/Persistence/SqliteItemRepository.cs
@@ -1694,6 +1694,33 @@ namespace MediaBrowser.Server.Implementations.Persistence
return " left join UserDataDb.UserData on (select UserDataKey from UserDataKeys where ItemId=Guid order by Priority LIMIT 1)=UserDataDb.UserData.Key";
}
+ private string GetGroupBy(InternalItemsQuery query)
+ {
+ var groups = new List<string>();
+
+ if (!string.IsNullOrWhiteSpace(query.GroupByAncestorOfType))
+ {
+ groups.Add("(Select PresentationUniqueKey from TypedBaseItems B where B.Type = 'MediaBrowser.Controller.Entities.TV.Series' And B.Guid in (Select AncestorId from AncestorIds where ItemId=A.Guid))");
+ }
+
+ if (EnableGroupByPresentationUniqueKey(query))
+ {
+ groups.Add("PresentationUniqueKey");
+ }
+
+ if (groups.Count > 0)
+ {
+ return " Group by " + string.Join(",", groups.ToArray());
+ }
+
+ return string.Empty;
+ }
+
+ private string GetFromText()
+ {
+ return " from TypedBaseItems A";
+ }
+
public IEnumerable<BaseItem> GetItemList(InternalItemsQuery query)
{
if (query == null)
@@ -1707,7 +1734,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
using (var cmd = _connection.CreateCommand())
{
- cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems";
+ cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText();
cmd.CommandText += GetJoinUserDataText(query);
if (EnableJoinUserData(query))
@@ -1723,10 +1750,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
cmd.CommandText += whereText;
- if (EnableGroupByPresentationUniqueKey(query))
- {
- cmd.CommandText += " Group by PresentationUniqueKey";
- }
+ cmd.CommandText += GetGroupBy(query);
cmd.CommandText += GetOrderByText(query);
@@ -1797,7 +1821,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
using (var cmd = _connection.CreateCommand())
{
- cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + " from TypedBaseItems";
+ cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, _retriveItemColumns, cmd)) + GetFromText();
cmd.CommandText += GetJoinUserDataText(query);
if (EnableJoinUserData(query))
@@ -1817,10 +1841,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
cmd.CommandText += whereText;
- if (EnableGroupByPresentationUniqueKey(query))
- {
- cmd.CommandText += " Group by PresentationUniqueKey";
- }
+ cmd.CommandText += GetGroupBy(query);
cmd.CommandText += GetOrderByText(query);
@@ -1838,11 +1859,11 @@ namespace MediaBrowser.Server.Implementations.Persistence
if (EnableGroupByPresentationUniqueKey(query))
{
- cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems";
+ cmd.CommandText += "; select count (distinct PresentationUniqueKey)" + GetFromText();
}
else
{
- cmd.CommandText += "; select count (guid) from TypedBaseItems";
+ cmd.CommandText += "; select count (guid)" + GetFromText();
}
cmd.CommandText += GetJoinUserDataText(query);
@@ -1905,7 +1926,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
return " ORDER BY " + string.Join(",", query.SortBy.Select(i =>
{
- var columnMap = MapOrderByField(i);
+ var columnMap = MapOrderByField(i, query);
var columnAscending = isAscending;
if (columnMap.Item2)
{
@@ -1918,7 +1939,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
}).ToArray());
}
- private Tuple<string, bool> MapOrderByField(string name)
+ private Tuple<string, bool> MapOrderByField(string name, InternalItemsQuery query)
{
if (string.Equals(name, ItemSortBy.AirTime, StringComparison.OrdinalIgnoreCase))
{
@@ -1977,6 +1998,10 @@ namespace MediaBrowser.Server.Implementations.Persistence
{
return new Tuple<string, bool>("(select value from itemvalues where ItemId=Guid and Type=3 LIMIT 1)", false);
}
+ if (string.Equals(name, ItemSortBy.SeriesDatePlayed, StringComparison.OrdinalIgnoreCase))
+ {
+ return new Tuple<string, bool>("(Select MAX(LastPlayedDate) from TypedBaseItems B"+ GetJoinUserDataText(query) + " where B.Guid in (Select ItemId from AncestorIds where AncestorId in (select guid from typedbaseitems c where C.Type = 'MediaBrowser.Controller.Entities.TV.Series' And C.Guid in (Select AncestorId from AncestorIds where ItemId=A.Guid))))", false);
+ }
return new Tuple<string, bool>(name, false);
}
@@ -1994,7 +2019,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
using (var cmd = _connection.CreateCommand())
{
- cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems";
+ cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText();
cmd.CommandText += GetJoinUserDataText(query);
if (EnableJoinUserData(query))
@@ -2010,10 +2035,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
cmd.CommandText += whereText;
- if (EnableGroupByPresentationUniqueKey(query))
- {
- cmd.CommandText += " Group by PresentationUniqueKey";
- }
+ cmd.CommandText += GetGroupBy(query);
cmd.CommandText += GetOrderByText(query);
@@ -2070,10 +2092,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
cmd.CommandText += whereText;
- if (EnableGroupByPresentationUniqueKey(query))
- {
- cmd.CommandText += " Group by PresentationUniqueKey";
- }
+ cmd.CommandText += GetGroupBy(query);
cmd.CommandText += GetOrderByText(query);
@@ -2137,7 +2156,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
using (var cmd = _connection.CreateCommand())
{
- cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + " from TypedBaseItems";
+ cmd.CommandText = "select " + string.Join(",", GetFinalColumnsToSelect(query, new[] { "guid" }, cmd)) + GetFromText();
var whereClauses = GetWhereClauses(query, cmd);
cmd.CommandText += GetJoinUserDataText(query);
@@ -2153,10 +2172,7 @@ namespace MediaBrowser.Server.Implementations.Persistence
cmd.CommandText += whereText;
- if (EnableGroupByPresentationUniqueKey(query))
- {
- cmd.CommandText += " Group by PresentationUniqueKey";
- }
+ cmd.CommandText += GetGroupBy(query);
cmd.CommandText += GetOrderByText(query);
@@ -2174,11 +2190,11 @@ namespace MediaBrowser.Server.Implementations.Persistence
if (EnableGroupByPresentationUniqueKey(query))
{
- cmd.CommandText += "; select count (distinct PresentationUniqueKey) from TypedBaseItems";
+ cmd.CommandText += "; select count (distinct PresentationUniqueKey)" + GetFromText();
}
else
{
- cmd.CommandText += "; select count (guid) from TypedBaseItems";
+ cmd.CommandText += "; select count (guid)" + GetFromText();
}
cmd.CommandText += GetJoinUserDataText(query);
@@ -2370,6 +2386,11 @@ namespace MediaBrowser.Server.Implementations.Persistence
whereClauses.Add("ParentIndexNumber=@ParentIndexNumber");
cmd.Parameters.Add(cmd, "@ParentIndexNumber", DbType.Int32).Value = query.ParentIndexNumber.Value;
}
+ if (query.ParentIndexNumberNotEquals.HasValue)
+ {
+ whereClauses.Add("(ParentIndexNumber<>@ParentIndexNumber or ParentIndexNumber is null)");
+ cmd.Parameters.Add(cmd, "@ParentIndexNumber", DbType.Int32).Value = query.ParentIndexNumberNotEquals.Value;
+ }
if (query.MinEndDate.HasValue)
{
whereClauses.Add("EndDate>=@MinEndDate");
generated by cgit v1.2.3 (git 2.46.0) at 2025-12-13 19:42:55 +0000