I have two models; giftModel and userGiftModel. They are related to each other as follows:
userGiftModel.belongsTo(giftModel, { as: "gift", foreignKey: "giftId"});
giftModel.hasMany(userGiftModel, { as: "userGifts", foreignKey: "giftId"});
I count number of gifts for current user as userGiftCount and my goal is to return a status which is determined by userGiftCount.
Here is my query and the problem is that status always return 1.
const gifts = await models.giftModel.findAll({
include: {
model: models.userGiftModel,
as: 'userGifts',
where: {
userId: req.user.id
},
required: false
},
where: {
giftStatus: 1,
[Op.and]: [
{
[Op.or]: [
{
giftStartDate : {
[Op.lt] : moment().unix()
}
},
{
giftStartDate : null
}
],
},
{
[Op.or] : [
{
giftEndDate : {
[Op.gt] : moment().unix()
}
},
{
giftEndDate : null
}
],
}
]
},
attributes: [
['giftId', 'id'],
['giftName', 'name'],
['giftDescription', 'description'],
['giftAmount', 'amount'],
['giftLink', 'link'],
['giftStartDate', 'startDate'],
['giftEndDate', 'endDate'],
['giftReportCount', 'reportCount'],
['giftReportSum', 'reportSum'],
[fn("COUNT", col("userGifts.giftId")), "userGiftCount"],
//here is my main problem
[literal(`CASE WHEN 'userGiftCount' <> 0 THEN 0 ELSE 1 END`), 'status']
],
order: [
['giftId', 'desc']
],
group: ['giftId'],
includeIgnoreAttributes: false,
})