Reputation: 2216
I am trying to write a query with that would roughly do:
SELECT Challenge.id, Challenge.name, count(AcceptedChallenge.userId) AS attempts, count(a.met) AS met, count(b.active) AS active, count(c.id) AS WHOLE
FROM Challange
LEFT JOIN AcceptedChallenge ON Challenge.id = AcceptedChallenge.challengeId,
LEFT JOIN AcceptedChallenge AS a ON Challenge.id = a.challengeId
LEFT JOIN AcceptedChallenge AS b ON Challenge.id = b.challengeId
LEFT JOIN AcceptedChallenge AS c ON Challenge.id = c.challengeId
WHERE a.met = true
AND b.userId = id and b.active = true
AND c.userId = id;
Tried multiple versions, including the below:
const Sequelize = require('sequelize');
const ChallengeController = async ({ user: { id } }) => {
const challenges = await Challenge
.findAll({
attributes: {
include: [[Sequelize.fn('count', Sequelize.col('AcceptedChallenges.userId')), 'attempts'],
[Sequelize.fn('count', Sequelize.col('a.met')), 'met']]
},
include: [{
model: AcceptedChallenge, attributes: [],
required: false,
}, {
model: AcceptedChallenge, attributes: [],
as: 'a',
where: { userId: id, met: true },
required: false,
}],
group: ['Challenge.id']
})
.catch((e) => {
console.log("error:", e);
throw new HTTP404Error('Challenges not found');
});
return challenges;
};
It is not recognizing my associations. Please advise. The version here results in: SequelizeEagerLoadingError: AcceptedChallenge is associated to Challenge using an alias. You've included an alias (a), but it does not match the alias(es) defined in your association (AcceptedChallenges).
When including the AcceptedChallenge
model just once, it calculates attempts just fine. I am perplexed as to how I could do the include/JOIN multiple times to get the result, which I need from a single SQL request.
Upvotes: 0
Views: 1256
Reputation: 1968
This works for me when the association is repeated, once for each alias needed in your query (example below, but obviously your association may be different).
ChallengeController.hasMany(AcceptedChallenge, {as: 'a', foreignKey: 'challengeId'});
ChallengeController.hasMany(AcceptedChallenge, {as: 'b', foreignKey: 'challengeId'});
ChallengeController.hasMany(AcceptedChallenge, {as: 'c', foreignKey: 'challengeId'});
Are you doing something similar?
Upvotes: 1