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