public async Task <IActionResult> HotestComments()
{
// 子查询可以有，但是只能查询一个scalar。outer join应当用正规的outer join
var query = from p in Context.Comments
select new
{
comment = p,
agreeCount = Context.Attitudes.Where(o => o.Agree == true && o.CommentId == p.Id).Count(),
}
into k
join u in Context.Users
on k.comment.SenderId equals u.Id
join w in Context.Works
on k.comment.WorkId equals w.Id
orderby k.comment.AgreeCount descending
select new
{
comment = k.comment,
agreeCount = k.agreeCount,
work = w,
user = u
};
var comments = await query.Take(20).ToListAsync();
var qcomments = comments.Select(
p => QComment.NormalView(p.comment, QUser.NormalView(p.user), null, QWork.NormalView(p.work)))
.ToList();
return(Ok(qcomments));
}
public async Task <QComment> GetById(string id)
{
var commentId = XUtils.ParseId(id);
if (commentId == null)
{
return(null);
}
var query = from p in Context.Comments.Where(r => r.Id == commentId)
join q in Context.Users on p.SenderId equals q.Id
select new
{
comment = p,
user = q,
myAtt = Context.Attitudes.Where(o => o.CommentId == p.Id && o.SenderId == AuthStore.UserId).FirstOrDefault()
};
var data = await query.FirstOrDefaultAsync();
var user = QUser.NormalView(data.user);
var commentQ = QComment.NormalView(data.comment, user, data.myAtt?.Agree);
return(commentQ);
}
public async Task <IActionResult> GetByWork(string workId, OrderByType order, int page)
{
if (!Enum.IsDefined(typeof(OrderByType), order))
{
return(new ApiError(MyErrorCode.ModelInvalid, "Invalid 'order'").Wrap());
}
page = Math.Max(page, 0);
const int pageSize = 20;
var wid = XUtils.ParseId(workId);
if (wid == null)
{
return(new ApiError(MyErrorCode.ModelInvalid, "wordId parse error").Wrap());
}
var query = from p in Context.Comments
where p.WorkId == wid
join q in Context.Users on p.SenderId equals q.Id
join o in Context.Attitudes.Where(z => z.SenderId == AuthStore.UserId) on p.Id equals o.CommentId
into xx
from x in xx.DefaultIfEmpty()
select new
{
comment = p,
user = q,
myatt = x
};
if (order == OrderByType.Hottest)
{
query = query.OrderByDescending(p => p.comment.AgreeCount);
}
else
{
query = query.OrderByDescending(p => p.comment.CreatedAt);
}
var ll = await query.Take(10).ToListAsync();
query = query.Skip(page * pageSize).Take(pageSize);
var data = await query.ToListAsync();
var commentsQ = data.Select(p => QComment.NormalView(p.comment, QUser.NormalView(p.user), p.myatt?.Agree)).ToList();
return(Ok(commentsQ));
}