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 <IActionResult> GetWorkById(Guid id)
{
var work = await Context.Works.AsNoTracking().Where(p => p.Id == id).FirstOrDefaultAsync();
var qwork = QWork.NormalView(work);
return(Ok(qwork));
}
public async Task <IActionResult> HotestWorks(WorkType type)
{
var query = from p in Context.Works.Where(p => p.Type == type)
select new
{
work = p,
commentCount = Context.Comments.Where(o => o.WorkId == p.Id).Count()
}
into k
orderby k.commentCount descending
select k;
var works = await query.Take(20).ToListAsync();
var qworks = works.Select(p => QWork.NormalView(p.work, p.commentCount));
return(Ok(qworks));
}
public async Task <IActionResult> GetWorkByKeyword(WorkType?type, string keyword, int page)
{
page = Math.Max(0, page);
const int pageSize = 20;
if (keyword == null)
{
return(new ApiError(MyErrorCode.ModelInvalid, "Keyword is null").Wrap());
}
if (keyword.Length >= 20)
{
keyword = keyword.Substring(0, 20);
}
var filteredTypeQuery = Context.Works.AsNoTracking().AsQueryable();
if (type != null)
{
filteredTypeQuery = filteredTypeQuery.Where(p => p.Type == type);
}
var query = from p in filteredTypeQuery
select new
{
work = p,
qr = EF.Functions.WebSearchToTsQuery("testzhcfg", keyword)
} into p
select new
{
p.work,
p.qr,
rank = p.work.Tsv.RankCoverDensity(p.qr, NpgsqlTsRankingNormalization.DivideBy1PlusLogLength)
} into p
where p.work.Tsv.Matches(p.qr)
orderby p.rank descending
select p.work;
var works = await query.Skip(page *pageSize).Take(pageSize).ToListAsync();
var qworks = works.Select(p => QWork.NormalView(p)).ToList();
return(Ok(qworks));
}
public static QComment NormalView(Comment c, QUser user, bool?myAttitude, QWork work = null)
{
return(c == null ? null : new QComment
{
Id = c.Id,
CreatedAt = c.CreatedAt,
UpdatedAt = c.UpdatedAt,
SenderId = c.SenderId,
WorkId = c.WorkId,
Title = c.Title,
Text = c.Text,
Rating = c.Rating,
AgreeCount = c.AgreeCount,
DisagreeCount = c.DisagreeCount,
User = user,
MyAttitude = myAttitude,
Work = work
});
}
