8

I have a SQL query (using mysql as DB) that I now need to rewrite as a sequelize.js query in node.js.

SQL Query

SELECT p.UserID, SUM(p.score), u.username
FROM Picks p
LEFT JOIN Users u
ON p.UserId = u.id
GROUP BY p.UserId;

not quite sure how this query needs to be structured to get the same results with sequelize.

Patrick Fain
  • 81
  • 1
  • 1
  • 2
  • I hope this would help. > https://stackoverflow.com/questions/66100779/how-do-i-add-conditions-in-sub-sub-child-models-in-sequelize-which-should-impact/66328066#66328066 – kamran186 Feb 23 '21 at 06:25

4 Answers4

3

This should do what you're needing:

db.Pick.findAll({
  attributes: [
    'UserID',
    [db.sequelize.fn('SUM', db.sequelize.col('score')), 'score']
  ],
  include: [{
    model: db.User,
    required: true,
    attributes: ['username']
  }],
  group: ['UserID']
}).then((results) => {
  ...
})
aecend
  • 2,432
  • 14
  • 16
0

Maybe try this (I assume you already associate Picks and Users), and you can access user.name by pick.user.username:

Picks.findAll({
    attributes: ['UserID', [sequelize.fn('SUM', 'score'), 'sumScore']]
    groupBy: ['UserID']
    include: [
        model: Users
    ]
});
Yu-Lin Chen
  • 559
  • 5
  • 12
0

The website at this domain no longer provides this tool. It's now filled with ads and likely malware.

I know this question is old but this answer may help others. I have found an online converter that can convert raw SQL to Sequelize.

The link is https://pontaku-tools.com/english/

When converted from this site I got the following reponse.

Picks.hasMany(Users,{foreignKey: '', as: 'u'});

var _q = Picks;
_q.findAll({
include: [{model: Users, as: 'u', required: false,}, ],
attributes: [[sequelize.fn('SUM', sequelize.col('p.score')), 'p.score'],['p.UserID', 'p.UserID'],['u.username', 'u.username']],
group: [''],
});
Regular Jo
  • 5,190
  • 3
  • 25
  • 47
Suhail Akhtar
  • 1,718
  • 15
  • 29
0

Writing a sql query may not always be very simple with sequelize functions. Sometimes I recommend to run your plain sql query by combining it with this function.

const { QueryTypes } = require('sequelize');

async message_page (req,res) {  
      const messagePage = await db.query("SELECT * FROM ..", { type: QueryTypes.SELECT });
      return messagePage;},
shepherdly
  • 1
  • 1