统计数据
.count()获取统计数据
async getSignInCount(userId: number) {
let totalCountValue = await this.signInModel.count({
where: {
userId: userId,
isDeleted: 0
}
})
let annualCountValue = await getRepository(SignInModel)
.createQueryBuilder("party_sign_in")
.select("COUNT(*) count")
.where("party_sign_in.user_id=:userId",{userId:userId})
.andWhere("party_sign_in.is_deleted=:isDeleted",{isDeleted:'0'})
.andWhere("YEAR(sign_time)=YEAR(NOW())")
.getRawOne();
let monthlyCountValue = await getRepository(SignInModel)
.createQueryBuilder("party_sign_in")
.select("COUNT(*) count")
.where("party_sign_in.user_id=:userId",{userId:userId})
.andWhere("party_sign_in.is_deleted=:isDeleted",{isDeleted:'0'})
.andWhere("month(sign_time)=month(NOW())")
.getRawOne();
let counts = {
totalCount:totalCountValue,
annualCount:annualCountValue.count,
monthlyCount:monthlyCountValue.count
};
return counts;
}
格式化时间
通过createQueryBuilder写原生SQL格式化时间字段
DATE_FORMAT(confirm_time,’%Y-%m-%d %H:%i:%s’) confirmTime
let data = await getRepository(SignInModel)
.createQueryBuilder("party_sign_in")
.select("location,DATE_FORMAT(confirm_time,'%Y-%m-%d %H:%i:%s') confirmTime")
.where("party_sign_in.user_id=:userId",{userId:userId})
.andWhere("party_sign_in.is_deleted=:isDeleted",{isDeleted:'0'})
.getRawMany();