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