public PostQueryResult Find(PostQueryOption option) { object condition = null; var wherePart = string.Empty; if (!string.IsNullOrEmpty(option.SectionId) && !string.IsNullOrEmpty(option.AuthorId)) { condition = new { SectionId = option.SectionId, AuthorId = option.AuthorId }; wherePart = "WHERE p.SectionId = @SectionId and p.AuthorId = @AuthorId"; } else if (!string.IsNullOrEmpty(option.SectionId)) { condition = new { SectionId = option.SectionId }; wherePart = "WHERE p.SectionId = @SectionId"; } else if (!string.IsNullOrEmpty(option.AuthorId)) { condition = new { AuthorId = option.AuthorId }; wherePart = "WHERE p.AuthorId = @AuthorId"; } using (var connection = GetConnection()) { var countSql = string.Format(@"SELECT COUNT(1) FROM {0} p {1}", Constants.PostTable, wherePart); var totalCount = connection.Query<int>(countSql, condition).Single(); var pageIndex = option.PageInfo.PageIndex; var pageSize = option.PageInfo.PageSize; var sql = string.Format(@" SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY p.LastUpdateTime desc) AS RowNumber, p.*, a1.Name as AuthorName, a2.Name as MostRecentReplierName FROM {0} p LEFT JOIN {1} a1 ON p.AuthorId = a1.Id LEFT JOIN {1} a2 ON p.MostRecentReplierId = a2.Id {2}) AS Total WHERE RowNumber >= {3} AND RowNumber <= {4}", Constants.PostTable, Constants.AccountTable, wherePart, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize); var posts = connection.Query<PostInfo>(sql, condition); foreach (var post in posts) { post.AuthorName = FormatValue(post.AuthorName); post.MostRecentReplyId = FormatValue(post.MostRecentReplyId); post.MostRecentReplierId = FormatValue(post.MostRecentReplierId); post.MostRecentReplierName = FormatValue(post.MostRecentReplierName); } return new PostQueryResult { Posts = posts, TotalCount = totalCount }; } }
public PostQueryResult Find(PostQueryOption option) { object condition = null; var wherePart = string.Empty; if (!string.IsNullOrEmpty(option.SectionId) && !string.IsNullOrEmpty(option.AuthorId)) { condition = new { SectionId = option.SectionId, AuthorId = option.AuthorId }; wherePart = "WHERE p.SectionId = @SectionId and p.AuthorId = @AuthorId"; } else if (!string.IsNullOrEmpty(option.SectionId)) { condition = new { SectionId = option.SectionId }; wherePart = "WHERE p.SectionId = @SectionId"; } else if (!string.IsNullOrEmpty(option.AuthorId)) { condition = new { AuthorId = option.AuthorId }; wherePart = "WHERE p.AuthorId = @AuthorId"; } using (var connection = GetConnection()) { var countSql = string.Format(@"SELECT COUNT(1) FROM {0} p {1}", Constants.PostTable, wherePart); var totalCount = connection.Query<int>(countSql, condition).Single(); var pageIndex = option.PageInfo.PageIndex; var pageSize = option.PageInfo.PageSize; var sql = string.Format(@" SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY p.Sequence) AS RowNumber, p.*, a.Name as AuthorName, r.ReplyCount, r.MostRecentReplySequence FROM {0} p LEFT JOIN {1} a ON p.AuthorId = a.Id LEFT JOIN (SELECT PostId, COUNT(*) AS ReplyCount, MAX(Sequence) AS MostRecentReplySequence FROM {2} GROUP BY PostId) r on r.PostId = p.Id {3}) AS Total WHERE RowNumber >= {4} AND RowNumber <= {5}", Constants.PostTable, Constants.AccountTable, Constants.ReplyTable, wherePart, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize); var posts = connection.Query<PostInfo>(sql, condition); var sequenceIds = string.Join(",", posts.Select(x => x.MostRecentReplySequence)); if (sequenceIds.Count() > 0) { sql = string.Format(@" select r.Id, r.Sequence, r.AuthorId, a.Name as AuthorName, r.CreatedOn from {0} r left join {1} a on r.AuthorId = a.Id where r.Sequence in ({2})", Constants.ReplyTable, Constants.AccountTable, sequenceIds); var replies = connection.Query(sql); foreach (var post in posts) { post.AuthorName = FormatValue(post.AuthorName); var mostRecentReply = replies.SingleOrDefault(x => x.Sequence == post.MostRecentReplySequence); if (mostRecentReply != null) { post.MostRecentReplyId = FormatValue(mostRecentReply.Id); post.MostRecentReplierId = FormatValue(mostRecentReply.AuthorId); post.MostRecentReplierName = FormatValue(mostRecentReply.AuthorName); post.MostRecentReplyCreatedOn = mostRecentReply.CreatedOn; } } } return new PostQueryResult { Posts = posts, TotalCount = totalCount }; } }