CODEWITHSUNDEEP
sequelize.js
Akhila
Akhila

Reputation: 3

Sequelize query for the following SQL:

I have a SQL query that I want to write as a Sequelize query.

SELECT users.username, players.points, tournaments.date
FROM USERS
JOIN players ON users.id = players.UserId
JOIN Tournaments ON tournaments.id = players.TournamentId
WHERE players.player_checkedin_flag = 1 AND users.role = "user";

I have the following association in Players table:

classMethods: {
    associate: function(models) {
      // Foreign key to user_id
      Player.belongsTo(models.User, {
        foreignKey: {
          allowNull: false
        }
      });
      // Foreign key to tournament_id
      Player.belongsTo(models.Tournament, {
        foreignKey: {
          allowNull: false
        }
      });

In Users table, 

{
  classMethods: {
    associate: function(models) {
      // User has many Player data
      User.hasMany(models.Player);
    }
  }
}

And in Tournaments table: 

{
  classMethods: {
    associate: function(models) {
      // Tournament is connected to many player rows
      Tournament.hasMany(models.Player);
    }
  }
}

What is the corresponding sequelize query for it?

Upvotes: 0

Views: 141

Answers (1)

piotrbienias
piotrbienias

Reputation: 7401

In this case you need to perform eager loading on those three models

User.all({
    attributes: ['username'], // return only 'username' column from 'users' table
    include: [
        {
            model: Player, // eager load players on players.userId = users.id
            attributes: ['points'], // return only 'points' column from 'players' table
            include: [{ model: Tournament, attributes: ['date'] }] // nested eager load of 'tournaments' table on tournaments.playerId = players.id
        }
    ],
    where: {
        playerCheckedinFlag: sequelize.where(sequelize.col('Player.player_checkedin_flag', '=', 1),
        role: 'user'
    },
    raw: true // used the raw attribute to prevent creating User instances from the query result due to the fact that we return 3 fields from 3 different tables
}).(result => {
    console.log(result);
});

sequelize in the code above is the instance of Sequelize in which the models were defined. You can also check the documentation of sequelize.where() method.

Upvotes: 1

Related Questions