public async Task <IEnumerable <CountdownAggregate> > GetAggregatesAsync(CountdownFilter filter)
{
using (IDbConnection connection = Connection) {
string sql =
$@"SELECT TOP {filter.Limit} Q.* FROM (
SELECT T.*, ROW_NUMBER() OVER ({BuildAggregateOrderBy(filter.DisplayOrderType)}) AS RN FROM (
SELECT
c.Id,
c.Description,
c.CreatedByAccountId,
c.CreatedOn,
c.EndsOn,
a.DisplayName AS CreatedByDisplayName,
a.Email AS CreatedByEmail,
ISNULL((SELECT SUM(Value) FROM Votes WHERE CountdownId = c.Id), 0) AS VoteScore,
(SELECT Value FROM Votes WHERE CountdownId = c.Id AND CastedByAccountId = @CurrentUserAccountId) AS CurrentUserVote,
(SELECT COUNT(Id) FROM Comments WHERE CountdownId = c.Id) AS CommentsCount,
(CASE WHEN CreatedByAccountId = @CurrentUserAccountId THEN 1 ELSE 0 END) AS IsCreatedByCurrentUser
FROM Countdowns c
INNER JOIN Accounts a ON c.CreatedByAccountId = a.Id
WHERE c.EndsOn >= ISNULL(@EndsAfter, EndsOn)
AND c.CreatedByAccountId = ISNULL(@CreatedByUserAccountId, CreatedByAccountId)
AND c.Description LIKE @query
) AS T
) AS Q
WHERE Q.RN > {(filter.Page - 1)*filter.Limit} ORDER BY Q.RN";
return(await connection.QueryAsync <CountdownAggregate>(sql, new {
filter.CurrentUserAccountId,
filter.SubFilter.EndsAfter,
filter.SubFilter.CreatedByUserAccountId,
query = $"%{filter.SubFilter.Query}%"
}));
}
}
public Task <IEnumerable <CountdownAggregate> > GetAggregatesAsync(CountdownFilter filter)
{
return(Task.Run(() => CountdownAggregates.AsEnumerable()));
}