How to do this specific sql left join using sequelize?

Question

I tried to make a DB structure for voting system using sequelize like below:

PollAnswer = sequelize.define('PollAnswer', {answer: DataTypes.STRING});
PollQuestion = sequelize.define('PollQuestion', {question: DataTypes.STRING});
VotingHistory = sequelize.define('VotingHistory', {});
User = sequelize.define('User', {name: DataTypes.STRING});

PollAnswer.belongsTo(PollQuestion); //every question has many answers
VotingHistory.belongsTo(PollQuestion);
VotingHistory.belongsTo(PollAnswer);
VotingHistory.belongsTo(User);

I want to write a query to receive the answers of a specific question and their vote counts in VotingHistory table, I did it using folowing sql command and response was what I expected:

select count(VotingHistory.PollAnswerId), PollAnswer.answer from PollAnswers
left join VotingHistories on PollAnswer.id = VotingHistory.PollAnswerId group by PollAnswers.id

expected response:

answer    count
------------
ans1       10
ass2       2
ans3       0

But when using sequelize to query the same thing like below:

VotingHistory.findAll({
  where:{PollQuestionId: req.params.poll},
  attributes:[[sequelize.fn('count', sequelize.col('PollAnswerId')), 'count']],
  group:['PollAnswerId'],
  include:[{
    model:PollAnswer,
    attributes:['id','answer']
  }]
})

it just returns this:

answer    count
------------
ans1       10
ass2       2

my question is: how can I get the expected response that above sql command query returns, using sequelize?

score 0 · Accepted Answer · answered Feb 09 '20 at 13:52

0

You should use PollAnswer.findAll, sequelize.fn and include PollHistory, see a similar solution

answered Feb 09 '20 at 13:52

Anatoly

20,799
3
28
42

Thanks for your answer, but when I use **PollAnswer.findAll( ... include:[{ model:VotingHistory}] ...)** sequelize keep getting the error: `SequelizeEagerLoadingError: VotingHistory is not associated to PollAnswer!` – vocav Feb 10 '20 at 05:45
you need to add an association from PollAnswer to VotingHistory like PollAnswer.hasMany(VotingHistory) – Anatoly Feb 10 '20 at 20:05
Yes thanks, by adding PollAnswer.hasMany(VotingHistory) , error problem solved! – vocav Feb 11 '20 at 07:23

score 0 · Answer 2 · answered Feb 10 '20 at 10:53

Isn't it just a "typo"? The r is missing from attributes.

If not, pls see the followings:

Did you try to log what query does your script generates? You can do this, by adding logging: console.log to its config:

VotingHistory.findAll({
  logging: console.log,
  where:{PollQuestionId: req.params.poll},
  attributes:[[sequelize.fn('count', sequelize.col('PollAnswerId')), 'count']],
  group:['PollAnswerId'],
  include:[{
    model:PollAnswer,
    attributes:['id','answer']
  }]
})

However I suspect your problem is that you're using PollAnswerId instead of PollAnswer.id

VotingHistory.findAll({
  logging: console.log,
  where:{PollQuestionId: req.params.poll},
  attributes:[[sequelize.fn('count', sequelize.col('PollAnswer.id')), 'count']],
  group:['PollAnswer.id'],
  include:[{
    model:PollAnswer,
    attributes:['id','answer']
  }]
})

Thanks for your precision, that was just a typo, I edited it. — vocav, Feb 11 '20 at 07:19

How to do this specific sql left join using sequelize?

2 Answers2