SequelizeJS Order by renamed attribute

1
votes

I came across an exception, cannot find solution anywhere. It looks like a bug of sequelizeJs.

Here is the question:

I have two model

module.exports = db.sequelize.define('user', {
  // id is added automatically
  createdAt: {
    type: Sequelize.DATE,
    field: 'created_at'
  }
})

module.exports = db.sequelize.define('question', {
  // id is added automatically
  userId: {
    type: Sequelize.BIGINT,
    field: 'user_id'
  }
})

User.hasMany(Question,{foreignKey:'userId',as:'questions'})

then I have a query:

  models.User.findAll({
      limit:limit,
      order:[['createdAt','DESC']],
      include : [{
      {
        model:models.Question,
        as:"questions"
      },]
    })

I got an exception saying: "SequelizeDatabaseError: Unknown column 'user.created_at' in 'order clause'"

Here is the generated sql code:

SELECT ***** FROM (SELECT user.id, user.created_at AS createdAt FROM user AS user ORDER BY user.created_at DESC LIMIT 10) AS user LEFT OUTER JOIN question AS questions ON user.id = questions.user_id ORDER BY user.created_at DESC

It seems that order by is applied twice. At the second time, the 'created_at' has been renamed as 'createdAt'. That's why db complains 'cannot find user.created_at'.

My question is how to fix this problem so that I can run my query. I cannot find any available solution. And why Sequelize apply 'order by' twice? it looks like unnecessary.

2
ormsequelize.js

2 Answers

0
votes

This is an open issue it seems

See GitHub

However, you could try explicitly stating the attributes you want to query for on both models.

0
votes

There is no issue with sequelize ,

Issue in below code : there one extra { inside the include block , remove that first

models.User.findAll({
    limit:limit,
    order:[['createdAt','DESC']],
    include : [{
    {
        model:models.Question,
        as:"questions"
    },]
})

Query is 100% correct, I have executed the same way and its working fine from both side (sequelize and sql).

Issue you may have is :

  1. your user table must have createdAt field named created_at
  2. inside models.User.findAll() you might have used attributes and forgot to add created_at

Try to run query inside your sql, and check for the error

SELECT ***** FROM (SELECT user.id, user.created_at AS createdAt FROM user AS user ORDER BY user.created_at DESC LIMIT 10) AS user LEFT OUTER JOIN question AS questions ON user.id = questions.user_id ORDER BY user.created_at DESC

And you will figure it out whats wrong with the query