public Task <IEnumerable <TrackDo> > SearchTracks(TrackFilterDo filter = null, PagedQuery <TrackOrderColumnDo> page = null)
{
QueryBuilder baseQuery = GetTrackQuery(filter, page);
var sql = baseQuery.BuildStatement();
return(Query <TrackDo>(sql, baseQuery.Parameters));
}
public Task <int> CountTracks(TrackFilterDo filter = null)
{
QueryBuilder baseQuery = GetTrackQuery(filter);
var countQuery = new QueryBuilder();
countQuery.AddSelectStatement("COUNT(*)");
countQuery.AddFromSubSelect(baseQuery, "base");
var sql = countQuery.BuildStatement();
return(ExecuteScalar <int>(sql, baseQuery.Parameters));
}
private QueryBuilder GetTrackQuery(TrackFilterDo filter, PagedQuery <TrackOrderColumnDo> page = null)
{
var baseQuery = new QueryBuilder();
baseQuery.AddSelectStatement(Map.GetSelectStatement("track"));
baseQuery.AddFromStatement(Map.GetFromStatement("track"));
if (filter != null)
{
if (!string.IsNullOrEmpty(filter.Title))
{
baseQuery.AddFilter(filter.Title, "title", p => $"track.[Title] LIKE '%' + {p} + '%'");
}
if (filter.ArtistId.HasValue)
{
baseQuery.AddFromStatement("JOIN [Library].[TrackArtist] ta ON ta.TrackId = track.Id AND ta.ArtistId = @ArtistId");
baseQuery.AddParameter("ArtistId", filter.ArtistId.Value);
}
if (filter.PlaylistId.HasValue)
{
baseQuery.AddFromStatement("JOIN [UserLibrary].[PlaylistTrack] pt ON pt.TrackId = track.Id AND pt.PlaylistId = @PlaylistId");
baseQuery.AddParameter("PlaylistId", filter.PlaylistId.Value);
}
if (filter.AlbumId.HasValue)
{
baseQuery.AddFilter(filter.AlbumId.Value, "AlbumId", p => $"track.AlbumId = {p}");
}
if (filter.PlayedByUserId.HasValue)
{
baseQuery.AddFromStatement("JOIN [UserLibrary].[PlayCount] pc ON pc.TrackId = track.Id AND pc.UserId = @UserId1");
baseQuery.AddParameter("UserId1", filter.PlayedByUserId.Value);
}
if (filter.IsInLibraryForUserId.HasValue)
{
baseQuery.AddFromStatement("JOIN [UserLibrary].[UserTrack] ut ON ut.TrackId = track.Id AND ut.UserId = @UserId2");
baseQuery.AddParameter("UserId2", filter.IsInLibraryForUserId.Value);
}
}
if (page != null)
{
if (page.OrderBy != null)
{
switch (page.OrderBy.Column)
{
case TrackOrderColumnDo.TrackNumber:
baseQuery.AddOrderByStatement("DiscNumber", page.OrderBy.Direction);
baseQuery.AddOrderByStatement("TrackNumber", page.OrderBy.Direction);
break;
case TrackOrderColumnDo.LastPlayed:
baseQuery.AddSelectStatement("(SELECT MAX(Timestamp) FROM [UserLibrary].[PlayCount] WHERE TrackId = track.Id) AS Timestamp");
baseQuery.AddOrderByStatement("Timestamp", page.OrderBy.Direction);
break;
case TrackOrderColumnDo.CreatedOn:
baseQuery.AddOrderByStatement("CreatedOn", page.OrderBy.Direction);
break;
case TrackOrderColumnDo.Title:
baseQuery.AddOrderByStatement("track.Title", page.OrderBy.Direction);
break;
}
}
if (page.Skip.HasValue && page.Take.HasValue)
{
baseQuery.AddPaging(page.Skip, page.Take);
}
}
return(baseQuery);
}
