public async Task <IActionResult> GetRecommendArticleListAsync([FromBody] GetRecommendArticleListRequestDto request)
{
var response = await new RecommendBiz().GetRecommendArticleListAsync(request);
var dictionaryBiz = new DictionaryBiz();
foreach (var item in response.CurrentPage)
{
item.ArticleType = dictionaryBiz.GetNameById(item.ArticleTypeDic);
}
return(Success(response));
}
/// <summary>
/// 获取文章推荐列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task <GetRecommendArticleListResponseDto> GetRecommendArticleListAsync(GetRecommendArticleListRequestDto request)
{
var sqlWhere = $@" 1=1 AND type = @type AND ENABLE = @enable";
if (!string.IsNullOrWhiteSpace(request.RecommendGuid))
{
sqlWhere = $"{sqlWhere} AND recommend_guid=@RecommendGuid";
}
var sql = $@"
SELECT * FROM(
SELECT
A.type,
A.recommend_guid,
A.creation_date,
B.owner_guid,
B.detail_guid,
C.article_guid,
C.author_guid,
C.source_type,
C.creation_date as article_date,
E.nick_name AS author_name,
A.ENABLE,
C.title,
C.abstract,
C.picture_guid,
C.article_type_dic,
CONCAT( D.base_path, D.relative_path ) AS PictureUrl ,
IFNULL(hot.visit_count, 0) as PageView
FROM
t_manager_recommend A
INNER JOIN t_manager_recommend_detail B ON A.recommend_guid = B.recommend_guid
INNER JOIN t_utility_article C ON B.owner_guid = C.article_guid and C.`enable`=1
LEFT JOIN t_utility_accessory D ON D.accessory_guid = C.picture_guid
LEFT JOIN t_utility_user E ON E.user_guid = C.author_guid
LEFT JOIN t_utility_hot hot ON hot.owner_guid = C.article_guid
AND hot.`enable` = 1
) ____T
WHERE
{sqlWhere}
ORDER BY
creation_date desc" ;
var parameters = new
{
type = RecommendModel.TypeEnum.Article.ToString(),
enable = true,
request.RecommendGuid,
};
var pageSql = $"{sql} limit {(request.PageIndex - 1) * request.PageSize},{request.PageSize}";
using (var conn = MySqlHelper.GetConnection())
{
var result = await conn.QueryAsync <GetRecommendArticleListItemDto>(pageSql, parameters);
var count = await conn.QueryFirstOrDefaultAsync <int>($@"SELECT COUNT(1) AS count FROM({sql}) AS T", parameters);
return(new GetRecommendArticleListResponseDto
{
Total = count,
CurrentPage = result
});
}
}
