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> GetPostById(string id)
{
var postId = XUtils.ParseId(id);
if (postId == null)
{
return(new ApiError(MyErrorCode.IdNotFound, "Id parse error").Wrap());
}
var query = from p in Context.Posts
where p.Id == postId
join q in Context.Users
on p.SenderId equals q.Id
select new
{
post = p,
user = q,
replyCount = Context.Replies.Count(z => z.PostId == p.Id),
lastReply = Context.Replies.Where(z => z.PostId == p.Id)
.DefaultIfEmpty().Max(p => p == null ? -2 : p.CreatedAt)
};
var data = await query.FirstOrDefaultAsync();
var qpost = QPost.NormalView(data.post, QUser.NormalView(data.user), data.replyCount, data.lastReply);
return(Ok(qpost));
}
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> GetUnhandledRequests(string topicId, int page, bool newest)
{
const int pageSize = 20;
var tid = XUtils.ParseId(topicId);
var query = from p in Context.AdminRequests
where p.TopicId == tid && p.Status == RequestStatus.Unhandled
join q in Context.Users
on p.SenderId equals q.Id
select new { request = p, user = q };
if (newest)
{
query = query.OrderByDescending(p => p.request.CreatedAt);
}
else
{
query = query.OrderBy(p => p.request.CreatedAt);
}
query = query.Skip(page * pageSize).Take(pageSize);
var requests = await query.ToListAsync();
var data = requests.Select(p => QAdminRequest.NormalView(p.request, QUser.NormalView(p.user))).ToList();
return(Ok(data));
}
public async Task <IActionResult> SearchPosts(string word, int page)
{
word ??= "";
page = Math.Max(0, page);
const int pageSize = 20;
var query = from p in Context.Posts
where p.Tsv.Matches(EF.Functions.WebSearchToTsQuery("testzhcfg", word))
join q in Context.Users
on p.SenderId equals q.Id
select new
{
post = p,
user = q,
replyCount = Context.Replies.Count(z => z.PostId == p.Id),
lastReply = Context.Replies.Where(z => z.PostId == p.Id)
.DefaultIfEmpty().Max(p => p == null ? -2 : p.CreatedAt)
};
query = query
.OrderByDescending(p => p.post.Tsv.RankCoverDensity(EF.Functions.WebSearchToTsQuery("testzhcfg", word)))
.Skip(pageSize * page).Take(pageSize);
var data = await query.ToListAsync();
var qposts = data.Select(
p => QPost.NormalView(p.post, QUser.NormalView(p.user), p.replyCount, p.lastReply)).ToList();
return(Ok(qposts));
}
public static QReply NormalView(Reply p, User user)
{
return(p == null ? null : new QReply
{
Id = p.Id,
SenderId = p.SenderId,
PostId = p.PostId,
Text = p.Text,
User = QUser.NormalView(user)
});
}
public static QDiscussion NormalView(Discussion p, User user)
{
return(p == null ? null : new QDiscussion
{
TopicId = p.TopicId,
SenderId = p.SenderId,
Text = p.Text,
ImageUrl = p.Image,
User = QUser.NormalView(user)
});
}
public static QAdminRequest NormalView(AdminRequest p, QUser user)
{
return(p == null ? null : new QAdminRequest
{
Id = p.Id,
TopicId = p.TopicId,
SenderId = p.SenderId,
Text = p.Text,
Status = p.Status,
User = user
});
}
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));
}
// TODO: Not Good
public IActionResult Me()
{
var user = Store.User;
// TODO: OK(NULL)
if (user == null)
{
return(new JsonResult(null));
}
else
{
return(Ok(QUser.NormalView(user)));
}
}
public async Task <IActionResult> GetRequestById(string id)
{
var requestId = XUtils.ParseId(id);
var query = from p in Context.AdminRequests
where p.Id == requestId
join q in Context.Users
on p.SenderId equals q.Id
select new { request = p, user = q };
var data = await query.FirstOrDefaultAsync();
var requestV = QAdminRequest.NormalView(data.request, QUser.NormalView(data.user));
return(Ok(requestV));
}
public static QPost NormalView(Post p, QUser user, int replyCount, long lastReply)
{
return(p == null ? null : new QPost
{
Id = p.Id,
CreatedAt = p.CreatedAt,
UpdatedAt = p.UpdatedAt,
SenderId = p.SenderId,
Title = p.Title,
Text = p.Text,
IsPinned = p.IsPinned,
IsEssense = p.IsEssence,
User = user,
ReplyCount = replyCount,
LastReply = lastReply
});
}
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
});
}
public async Task <IActionResult> GetPosts(string topicId, int page)
{
const int pageSize = 20;
page = Math.Max(0, page);
var tid = XUtils.ParseId(topicId);
if (tid == null)
{
return(new ApiError(MyErrorCode.IdNotFound, "topicId parse error").Wrap());
}
var query = from p in Context.Posts
where p.TopicId == tid
join q in Context.Users
on p.SenderId equals q.Id
select new
{
post = p,
user = q,
replyCount = Context.Replies.Count(z => z.PostId == p.Id),
lastReply = Context.Replies.Where(z => z.PostId == p.Id)
.DefaultIfEmpty().Max(p => p == null ? -2 : p.CreatedAt)
};
query = query.OrderByDescending(p => p.post.IsPinned)
.ThenByDescending(p => p.lastReply)
.Skip(page * pageSize)
.Take(pageSize);
var data = await query.ToListAsync();
var qposts = data.Select(
p => QPost.NormalView(p.post, QUser.NormalView(p.user), p.replyCount, p.lastReply)).ToList();
return(Ok(qposts));
}
