Ejemplo n.º 1
0
        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}%"
                }));
            }
        }
Ejemplo n.º 2
0
 public Task <IEnumerable <CountdownAggregate> > GetAggregatesAsync(CountdownFilter filter)
 {
     return(Task.Run(() => CountdownAggregates.AsEnumerable()));
 }