CODEWITHSUNDEEP
node.jssequelize.js
lilbiscuit
lilbiscuit

Reputation: 2249

How to add ORDER BY into sequelize method in NodeJS

I have a nodeJS app accessing a MySQL database using Sequelize.

There are two tables for users: user and user_password

There are multiple entries for each user in user_password table if the user has changed their password at least once. I want to validate a user via findUserWithUsername(username) as shown:

async findOne(query = {}, include = [] , attributes = ['id', 'username', 'email'] ) {
    const user = await db.user.findOne({include: include, where: query,  attributes: attributes});
    return user;
}

async findUserWithUsername(username) {
    const include = [
        {
            model: db.user_password,
            attributes: ['id','algorithm', 'password', 'salt'],
            order: 'id desc'
        }
    ];
    return await this.findOne({username: username}, include); // need to get last one since there could be multiples
}

This is not working : order: 'id desc'

Basically, the tables are joined and the first password entry inuser_password is returned, but order: id desc doesn't do anything...I still get the first (oldest) entry. Here's the query that's run now from the findUserWithUsername() method:

    SELECT `user`.`id`, `user`.`username`, `user`.`email`, 
   `user_password`.`id` AS `user_password.id`, 
   `user_password`.`algorithm` AS `user_password.algorithm`, 
   `user_password`.`password` AS `user_password.password`, 
   `user_password`.`salt` AS `user_password.salt` 
    FROM `user` AS `user` 
    LEFT OUTER JOIN `user_password` AS `user_password` 
    ON `user`.`id` = `user_password`.`user_id` WHERE `user`.`username` = 'joe' LIMIT 1

So....How can I add the sql equivalent of ORDER BY id to the findUserWithUsername() method?

Upvotes: 3

Views: 16648

Answers (3)

Vidur Singla
Vidur Singla

Reputation: 305

I think there is some problem in the syntax of your code.

order: [
  ['id', 'DESC']
]

Should do the trick.

Docs: http://docs.sequelizejs.com/manual/tutorial/querying.html#ordering

Upvotes: 1

Dajalmar Gutierrez
Dajalmar Gutierrez

Reputation: 466

Maybe you should do it backwards:

userPassword.findAll({
   include:[{model:db.user, required: true, where:{ ...add here username... }}],
   order: 'id desc'
})

Get alll the userPasswords including that username

Maybe you should check this issue seqeuelize issue 3173

Upvotes: 2

Andrei Karpuszonak
Andrei Karpuszonak

Reputation: 9034

Adding should work for you

order: 'id DESC'

More examples are here: http://docs.sequelizejs.com/en/latest/docs/querying/#ordering

Upvotes: 1

Related Questions