public Task <Paged <EventEntity> > QueryEventPaged(EventSearchParameter parameter)
{
var sql = new StringBuilder(256);
var condition = new StringBuilder(64);
var parameters = new DynamicParameters();
condition.Append(" WHERE EventAt >= @From AND EventAt < @To");
parameters.Add("From", parameter.From);
parameters.Add("To", parameter.To);
if (parameter.EventType.HasValue)
{
condition.Append(" AND EventType = @EventType");
parameters.Add("EventType", parameter.EventType.Value);
}
sql.Append("SELECT * FROM Event");
sql.Append(condition);
sql.Append(" ORDER BY EventAt DESC OFFSET @Offset ROWS FETCH NEXT @Size ROWS ONLY");
parameters.Add("Offset", parameter.Offset);
parameters.Add("Size", parameter.Size);
sql.Append("; ");
sql.Append("SELECT COUNT(*) FROM Event");
sql.Append(condition);
return(ConnectionFactory.UsingAsync(async con =>
{
var reader = await con.QueryMultipleAsync(sql.ToString(), parameters);
var list = (await reader.ReadAsync <EventEntity>(false)).ToList();
var count = (await reader.ReadAsync <int>(false)).First();
return Paged.From(parameter, list, count);
}));
}
public async Task <Paged <StoryDto> > HandleAsync(BrowseStories query)
{
var now = query.Now.ToUnixTimeMilliseconds();
var documents = _database.GetCollection <StoryDocument>($"{Schema}.stories")
.AsQueryable()
.Where(x => x.From <= now && x.To >= now);
var input = query.Query;
if (!string.IsNullOrWhiteSpace(input))
{
documents = documents.Where(x =>
x.Title.Contains(input) || x.Author.Name.Contains(input) || x.Tags.Contains(input));
}
var result = await documents.OrderByDescending(x => x.CreatedAt).PaginateAsync(query);
var storyIds = result.Items.Select(x => x.Id);
var rates = await _database.GetCollection <StoryRatingDocument>($"{Schema}.ratings")
.AsQueryable()
.Where(x => storyIds.Contains(x.StoryId))
.ToListAsync();
var pagedResult = Paged <StoryDto> .From(result, result.Items.Select(x => x.ToDto(rates)));
return(new Paged <StoryDto>
{
CurrentPage = pagedResult.CurrentPage,
TotalPages = pagedResult.TotalPages,
ResultsPerPage = pagedResult.ResultsPerPage,
TotalResults = pagedResult.TotalResults,
Items = pagedResult.Items
});
}
public async Task <Paged <UserDto> > HandleAsync(BrowseUsers query)
{
var result = await _database.GetCollection <UserDocument>($"{Schema}.users")
.AsQueryable()
.OrderByDescending(x => x.CreatedAt)
.PaginateAsync(query);
var followers = new HashSet <Guid>();
if (query.UserId.HasValue)
{
var userId = query.UserId.Value;
followers = new HashSet <Guid>(await _database.GetCollection <FollowerDocument>($"{Schema}.followers")
.AsQueryable()
.Where(x => x.FollowerId == userId)
.Select(x => x.FolloweeId)
.ToListAsync());
}
var pagedResult = Paged <UserDto> .From(result, result.Items.Select(x => Map(x, followers)));
return(new Paged <UserDto>
{
CurrentPage = pagedResult.CurrentPage,
TotalPages = pagedResult.TotalPages,
ResultsPerPage = pagedResult.ResultsPerPage,
TotalResults = pagedResult.TotalResults,
Items = pagedResult.Items
});
}
public Task <Paged <ItemEntity> > QueryItemPaged(Pageable parameter)
{
return(ConnectionFactory.UsingAsync(async con =>
{
var reader = await con.QueryMultipleAsync(
"SELECT * FROM Item ORDER BY Id OFFSET @Offset ROWS FETCH NEXT @Size ROWS ONLY;" +
"SELECT COUNT(*) FROM Item",
parameter);
var list = (await reader.ReadAsync <ItemEntity>(false)).ToList();
var count = (await reader.ReadAsync <int>(false)).First();
return Paged.From(parameter, list, count);
}));
}
public async Task <IPagedEnumerable <ProductDto> > Ask(UberProductSpec spec)
{
using (var sqlConnection
= new SqlConnection(""))
{
await sqlConnection.OpenAsync();
var res1 = await sqlConnection
.QueryAsync <ProductDto>("SELECT p.Id as Id, c.Name as CategoryName, p.Price as Price " +
"FROM Products p INNER JOIN Categories c ON p.Category_Id = c.Id " +
"WHERE Price > @Price " +
"ORDER BY Id OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY", new { Price });
var res2 = await sqlConnection.QueryAsync <int>("SELECT COUNT(*) FROM Products " +
"WHERE Price > @Price", new { Price });
return(Paged.From(res1, res2.Single()));
}
}