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