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); }