public async Task <CommentReadView?> FindById(int id, User?user = null)
{
var comments = await Connection.QueryAsync <CommentRecord>(
@"WITH RECURSIVE commenttree AS (
SELECT r.* FROM comment r WHERE id = @Id AND was_deleted = FALSE
UNION ALL
SELECT c.* FROM comment c
INNER JOIN commenttree ct ON ct.id = c.parent_id
WHERE c.was_deleted = FALSE
) SELECT * FROM commenttree
ORDER BY parent_id, creation_date ASC;",
new { Id = id }
);
IUserReader userReader = GetReader <IUserReader>();
IVoteReader voteReader = GetReader <IVoteReader>();
List <CommentReadView> views = new List <CommentReadView>();
foreach (CommentRecord comment in comments)
{
CommentReadView view = Map(comment);
view.User = (await userReader.FindById(comment.UserId)) !;
if (user != null)
{
view.Vote = await voteReader.FindByCommentAndUser(comment.Id, user.Id);
}
views.Add(view);
}
// We're assuming there will always be one top level comment.
return(BuildCommentTree(views)[0]);
}
public async Task <IEnumerable <CommentReadView> > FindByPost(int postId, User?user = null)
{
var comments = (await Connection.QueryAsync <CommentRecord>(
@"WITH RECURSIVE commenttree AS (
SELECT r.* FROM comment r WHERE post_id = @PostId AND parent_id = 0 AND was_deleted = FALSE
UNION ALL
SELECT c.* FROM comment c
INNER JOIN commenttree ct ON ct.id = c.parent_id
WHERE c.was_deleted = FALSE
) SELECT * FROM commenttree
ORDER BY parent_id, creation_date DESC;",
new { PostId = postId }
));
IUserReader userReader = GetReader <IUserReader>();
IVoteReader voteReader = GetReader <IVoteReader>();
List <CommentReadView> views = new List <CommentReadView>();
foreach (CommentRecord comment in comments)
{
CommentReadView view = Map(comment);
view.User = (await userReader.FindById(comment.UserId)) !;
if (user != null)
{
view.Vote = await voteReader.FindByCommentAndUser(comment.Id, user.Id);
}
views.Add(view);
}
return(BuildCommentTree(views));
}
public async Task <PostReadView?> FindById(int id, User?user = null)
{
var post = await Connection.QueryFirstOrDefaultAsync <PostRecord>(
@"SELECT * FROM post
WHERE was_deleted = FALSE
ORDER BY post.creation_date DESC
LIMIT @Limit
OFFSET @Offset",
new {
Id = id
}
);
//Get total count
int totalCount = await Connection.ExecuteScalarAsync <int>(
"SELECT COUNT(*) FROM post;"
);
IUserReader userReader = GetReader <IUserReader>();
ISpaceReader spaceReader = GetReader <ISpaceReader>();
PostReadView view = Map(post);
view.User = (await userReader.FindById(post.UserId)) !;
view.Space = (await spaceReader.FindById(post.SpaceId)) !;
return(view);
}
public async Task <IEnumerable <SpaceReadView> > FindDefault()
{
var defaults = await Connection.QueryAsync <SpaceRecord>(
@"SELECT * FROM space WHERE is_default = TRUE"
);
var views = defaults.Select(s => Map(s)).ToArray();
IUserReader userReader = GetReader <IUserReader>();
for (int i = 0; i < views.Length; i++)
{
views[i].User = (await userReader.FindById(defaults.ElementAt(i).UserId)) !;
}
return(views);
}
public async Task <PagedResultSet <CommentReadView> > FindByUser(string username, PaginationInfo paging, User?user = null)
{
var comments = await Connection.QueryAsync <CommentRecord>(
@"SELECT comment.* FROM comment
LEFT JOIN ""user"" ON comment.user_id = ""user"".id
WHERE ""user"".username = @Username AND was_deleted = FALSE
ORDER BY creation_date DESC
LIMIT @Limit
OFFSET @Offset ",
new {
Username = username,
Limit = paging.PageNumber,
Offset = paging.Offset
}
);
//Get total count
int totalCount = await Connection.ExecuteScalarAsync <int>(
@"SELECT COUNT(*) FROM comment
LEFT JOIN ""user"" ON comment.user_id = ""user"".id
WHERE ""user"".username = @Username AND was_deleted = FALSE",
new { Username = username }
);
IUserReader userReader = GetReader <IUserReader>();
IVoteReader voteReader = GetReader <IVoteReader>();
List <CommentReadView> views = new List <CommentReadView>();
foreach (CommentRecord comment in comments)
{
CommentReadView view = Map(comment);
view.User = (await userReader.FindById(comment.UserId)) !;
if (user != null)
{
view.Vote = await voteReader.FindByCommentAndUser(comment.Id, user.Id);
}
views.Add(view);
}
return(new PagedResultSet <CommentReadView>(views, paging));
}
public async Task <PagedResultSet <PostReadView> > FindByUser(string username, PaginationInfo paging, User?user = null)
{
var posts = await Connection.QueryAsync <PostRecord>(
@"SELECT post.* FROM post
LEFT JOIN ""user"" u1 ON u1.id = post.user_id
WHERE u1.username = @Username AND post.was_deleted = FALSE
ORDER BY post.creation_date DESC
LIMIT @Limit
OFFSET @Offset",
new {
Username = username,
Limit = paging.PageSize,
Offset = paging.Offset
}
);
//Get total count
int totalCount = await Connection.ExecuteScalarAsync <int>(
@"SELECT COUNT(*) FROM post
LEFT JOIN ""user"" ON post.user_id = ""user"".id
WHERE ""user"".username = @Username AND post.was_deleted = FALSE",
new { Username = username }
);
IUserReader userReader = GetReader <IUserReader>();
ISpaceReader spaceReader = GetReader <ISpaceReader>();
List <PostReadView> views = new List <PostReadView>();
foreach (PostRecord post in posts)
{
PostReadView view = Map(post);
view.User = (await userReader.FindById(post.UserId)) !;
view.Space = (await spaceReader.FindById(post.SpaceId)) !;
views.Add(view);
}
return(new PagedResultSet <PostReadView>(views, new PaginationInfo(paging.PageNumber, paging.PageSize, totalCount)));
}
public async Task <IEnumerable <SpaceReadView> > FindSubscribed(User user)
{
var subscribes = await Connection.QueryAsync <SpaceRecord>(
@"SELECT * FROM space
LEFT JOIN ""user"" ON space.user_id = ""user"".id
LEFT JOIN subscription ON space.id = subscription.space_id
WHERE subscription.user_id = @Id",
user
);
var views = subscribes.Select(s => Map(s)).ToArray();
IUserReader userReader = GetReader <IUserReader>();
for (int i = 0; i < views.Length; i++)
{
views[i].User = (await userReader.FindById(subscribes.ElementAt(i).UserId)) !;
}
return(views);
}
public async Task <SpaceReadView?> FindByName(string name)
{
var space = (await Connection.QueryFirstOrDefaultAsync <SpaceRecord>(
@"SELECT * FROM space WHERE LOWER(space.name) = LOWER(@Name)",
new { Name = name }
));
if (space == null)
{
return(null);
}
SpaceReadView view = Map(space);
IUserReader userReader = GetReader <IUserReader>();
view.User = (await userReader.FindById(space.UserId)) !;
return(view);
}
public async Task <SpaceReadView?> FindById(int id)
{
var space = (await Connection.QueryFirstOrDefaultAsync <SpaceRecord>(
@"SELECT * FROM space WHERE space.id = @Id",
new { Id = id }
));
if (space == null)
{
return(null);
}
SpaceReadView view = Map(space);
IUserReader userReader = GetReader <IUserReader>();
view.User = (await userReader.FindById(space.UserId)) !;
return(view);
}
public async Task <PagedResultSet <PostReadView> > FindByNew(PaginationInfo paging, User?user = null)
{
var posts = await Connection.QueryAsync <PostRecord>(
@"SELECT * FROM post
WHERE was_deleted = FALSE
ORDER BY post.creation_date DESC
LIMIT @Limit
OFFSET @Offset",
new {
Limit = paging.PageSize,
Offset = paging.Offset
}
);
//Get total count
int totalCount = await Connection.ExecuteScalarAsync <int>(
"SELECT COUNT(*) FROM post;"
);
IUserReader userReader = GetReader <IUserReader>();
ISpaceReader spaceReader = GetReader <ISpaceReader>();
List <PostReadView> views = new List <PostReadView>();
foreach (PostRecord post in posts)
{
PostReadView view = Map(post);
view.User = (await userReader.FindById(post.UserId)) !;
view.Space = (await spaceReader.FindById(post.SpaceId)) !;
views.Add(view);
}
return(new PagedResultSet <PostReadView>(views, new PaginationInfo(paging.PageNumber, paging.PageSize, totalCount)));
}
