Sequelize - Associate table column value in where condition

Question

Sequelize - Associate table column in where condition

I want to perform this query in sequelize models:

SELECT * FROM `model_game` AS `Game` INNER JOIN `model_activity` AS `Activity` ON `Game`.`ActivityId` = `Activity`.`id`  WHERE `Game`.`startAt` > ('2017-03-25 07:37:36'-`Activity.duration`) AND `Game`.`status` = 'NotStarted';

I tried using sequelize.col() function, But still cannot poulate the value. My code is below

My Game model . table name model_game

var Game = sequelize.define("Game", {
    startAt: DataTypes.DATE,
    status: DataTypes.ENUM('NotStarted','Completed')
  }, {
classMethods: {
      associate: function(models) {
        Activity.belongsTo(models.Activity);
      }
}
});

My Activity model, table name model_activity

var Activity = sequelize.define("Activity", {
    title: DataTypes.STRING,
    duration: DataTypes.INTEGER
  },{
classMethods: {
      associate: function(models) {
        Activity.hasMany(models.Game);
      }
}
});

Find Query, which now returns value but sequelize.col("Activity.duration") has no effect at all

var currentTime = moment();
models.Game.findAndCountAll({
            where: { status: 'NotStarted',   
            startAt: {gt: currentTime.subtract(moment.duration(sequelize.col("Activity.duration"), 'minutes'))}, 
            },
            include: [{
                model: models.Activity             
            }]
        }).then(function(result) {
     //Success
});

But the above code does not populate "Activity.duration" duration value. And there is no error, What should be done to rectify this. Thanks in Advance

Answer 1

You can try to use DATE_SUB() function combined with sequelize.literal.

models.Game.findAndCountAll({
    where: {
        status: 'NotStarted',
        startAt: {
            $gt: models.sequelize.fn(
                'DATE_SUB',
                models.sequelize.literal('NOW()'),
                models.sequelize.literal('INTERVAL Activity.duration MINUTE')
            )
        }
    },
    include: [models.Activity]
}).then(result => {
    // result...
});

Above code would generate query similar to this one below:

SELECT * FROM games INNER JOIN activities
ON games.activityId = activities.id
WHERE games.status = 'NotStarted'
AND games.startAt > DATE_SUB(NOW(), INTERVAL activities.duration MINUTE);

According to your code and the query you want to obtain, I have used NOW() as the date from which you want to subtract Activity.duration field. Moreover, I have used the MINUTE interval, but you would change it depending on what the Activity.duration field represents (hours, minutes, seconds etc.).