CODEWITHSUNDEEP
node.jstypescriptsequelize.js
Bilal
Bilal

Reputation: 182

Sequelize generating wrong query? sequelize-auto-ts and sequelize

I am using Typescript,NodeJS and sequelize-auto-ts for model generation. The two models where I want to place a include with find query is giving me wrong result which in turn is due to wrong query being fired. here are the two models in question;

rewardcodes = <types.rewardcodesModel> sequelize.define<types.rewardcodeInstance, types.rewardcodePojo>('rewardcodes', {
    'rewardid': { type: 'number', primaryKey: true, autoIncrement: true },
    'code':'string',
    'amount':'string'
    },
    {
        timestamps: false,
        classMethods: {
            getrewardcode:(rewardcode:types.rewardcodePojo) => {
                var where:{[key:string]:any} = {};
                if (rewardcode['rewardid'] !== undefined) { where['rewardid'] = rewardcode['rewardid']}
                if (rewardcode['code'] !== undefined) { where['code'] = rewardcode['code']}
                if (rewardcode['amount'] !== undefined) { where['amount'] = rewardcode['amount']}
                return this.find({where: where});
            }
        }
    });

and 

userfreecredits = <types.userfreecreditsModel> sequelize.define<types.userfreecreditInstance, types.userfreecreditPojo>('userfreecredits', {
    'id': { type: 'number', primaryKey: true, autoIncrement: true },
    'userid': { type: 'number', references:user },
    'rewardid': {type: 'number',references: rewardcodes},
    'isexpired':'Buffer',
    'isused':'Buffer',
    'isactive':'Buffer',
    'expireydate':'Date'
    },
    {
        timestamps: false,
        classMethods: {
            getuserfreecredit:(userfreecredit:types.userfreecreditPojo) => {
                var where:{[key:string]:any} = {};
                if (userfreecredit['id'] !== undefined) { where['id'] = userfreecredit['id']}
                if (userfreecredit['userid'] !== undefined) { where['userid'] = userfreecredit['userid']}
                if (userfreecredit['rewardid'] !== undefined) { where['rewardid'] = userfreecredit['rewardid']}
                if (userfreecredit['isexpired'] !== undefined) { where['isexpired'] = userfreecredit['isexpired']}
                if (userfreecredit['isused'] !== undefined) { where['isused'] = userfreecredit['isused']}
                if (userfreecredit['isactive'] !== undefined) { where['isactive'] = userfreecredit['isactive']}
                if (userfreecredit['expireydate'] !== undefined) { where['expireydate'] = userfreecredit['expireydate']}
                return this.find({where: where});
            }
        }
    });

and the db call I am making is as follows;

 var association = <sequelize.AssociationOptions>{};
    association.foreignKey = "rewardid";
    models.userfreecredits.hasOne(models.rewardcodes,association);
    models.rewardcodes.hasMany(models.userfreecredits);

    var findOptions = <sequelize.FindOptions>{};
    findOptions.where = { userid: userId, isexpired: false, isused: false, isactive: true, expireydate: { gte: new Date() } };
   findOptions.include = models.rewardcodes;

    return models.userfreecredits.find(findOptions).complete((err: Error, credits: types.userfreecreditInstance) => {
        return credits;
    });

But the generated query from above is wrong and says userfreecredit.id=rewardcodes.id

SELECT `userfreecredits`.*, `rewardcode`.`rewardid` AS `rewardcode.rewardid`,
`rewardcode`.`code` AS `rewardcode.code`, `rewardcode`.`amount` AS 
   `rewardcode.amount` FROM `userfreecredits` LEFT OUTER JOIN `rewardcodes` AS
        `rewardcode` ON `userfreecredits`.`id` = `rewardcode`.`rewardid` 
         WHERE `userfreecredits`.`userid`='1' AND `userfreecredits`.`isexpired`=false
        AND `userfreecredits`.`isused`=false AND `userfreecredits`.`isactive`=true
        AND`userfreecredits`.`expireydate` >= '2014-08-29 21:49:08' LIMIT 1;

Please help me in pointing out what am I doing wrong or missing.

Upvotes: 1

Views: 1328

Answers (1)

Bilal
Bilal

Reputation: 182

So I found my mistake instead of hasOne I had to use belongsTo and it worked.For reference the documentation http://sequelizejs.com/docs/latest gave me some sense but after a while gist.github.com/sdepold/1757695

Upvotes: 2

Related Questions