CODEWITHSUNDEEP
node.jssequelize.js
Mayur
Mayur

Reputation: 770

Sequelize column Table.createdAt does not exist

I have these two tables:

const AdminUser = sequelize.define('AdminUser', {
    adminUserId: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4, 
      unique: true, 
      allowNull: false,
      primaryKey: true
    },
    adminUsername: {
      type: 'citext',
      unique: true,
      allowNull: false
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    role: {
      type: DataTypes.ENUM,
      values: ['super-admin', 'non-super-admin']  
    },
    active: {
      type: DataTypes.BOOLEAN
    }
  })

AdminUser.belongsToMany(MsTeam, {
      through: 'AdminUserTeam',
      foreignKey: {
        name: 'adminUserId',
        allowNull: false
      }
    })

And

const MsTeam = sequelize.define('MsTeam', {
    teamId: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4, 
      unique: true, 
      allowNull: false,
      primaryKey: true
    },
    msTeamId: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false
    },
    msTeamName: {
      type: DataTypes.STRING,
      allowNull: false
    },
    active: {
      type: DataTypes.BOOLEAN
    }
  })

MsTeam.belongsToMany(models.AdminUser, {
      through: 'AdminUserTeam',
      foreignKey: {
        name: 'teamId',
        allowNull: false
      }
    })

They have a n:m relationship through 'AdminUserTeam'. Timestamps are enabled.

But a simple query like,

// Include team data
  const includeMsTeam = {
    model: MsTeam,
    through: {
      attributes: []
    },
    attributes: ['teamId', 'msTeamName']
  }
const users = await AdminUser.findAll({
    attributes: ['adminUserId', 'adminUsername'],
    limit: 10,
    offset: 10,
    order: [['createdAt', 'DESC']],
    include: [includeMsTeam]
  })

fails with error,

{
    "error": "column AdminUser.createdAt does not exist"
}

Strangely, the query succeeds if I remove the include field

A solution to this was to disable timestamps as mentioned in this SO answer. But what if I want to keep timestamps? How do I order by "createdAt" using associations?

Edit: Add SQL query

Executing (default): SELECT "AdminUser".*, "MsTeams"."teamId" AS "MsTeams.teamId", "MsTeams"."msTeamName" AS "MsTeams.msTeamName", "MsTeams->AdminUserTeam"."createdAt" AS "MsTeams.AdminUserTeam.createdAt", "MsTeams->AdminUserTeam"."updatedAt" AS "MsTeams.AdminUserTeam.updatedAt", "MsTeams->AdminUserTeam"."adminUserId" AS "MsTeams.AdminUserTeam.adminUserId", "MsTeams->AdminUserTeam"."teamId" AS "MsTeams.AdminUserTeam.teamId" FROM (SELECT "AdminUser"."adminUserId", "AdminUser"."adminUsername" FROM "AdminUsers" AS "AdminUser" WHERE "AdminUser"."role" = 'non-super-admin' AND ( SELECT "AdminUserTeam"."adminUserId" FROM "AdminUserTeam" AS "AdminUserTeam" INNER JOIN "MsTeams" AS "MsTeam" ON "AdminUserTeam"."teamId" = "MsTeam"."teamId" WHERE ("AdminUser"."adminUserId" = "AdminUserTeam"."adminUserId") LIMIT 1 ) IS NOT NULL ORDER BY "AdminUser"."createdAt" DESC LIMIT 20 OFFSET 0) AS "AdminUser" LEFT OUTER JOIN ( "AdminUserTeam" AS "MsTeams->AdminUserTeam" INNER JOIN "MsTeams" AS "MsTeams" ON "MsTeams"."teamId" = "MsTeams->AdminUserTeam"."teamId") ON "AdminUser"."adminUserId" = "MsTeams->AdminUserTeam"."adminUserId" ORDER BY "AdminUser"."createdAt" DESC;

The error happens at, 

ORDER BY "AdminUser"."createdAt" DESC

Upvotes: 3

Views: 9229

Answers (4)

5ar
5ar

Reputation: 2210

It seems that sequelize is generating a lot of subqueries and that createdAt is not visible to the top-most ORDER BY, try adding subQuery: false to the options object (the object that is the first argument of findAll). If that doesn't help, adding createdAt to attributes should work.

Upvotes: 4

Bachisheo
Bachisheo

Reputation: 21

Sequelize will automatically add the attributes createdAt and updatedAt when you use the define method: https://sequelize.org/v3/docs/models-definition/

"If you do not want timestamps on your models, only want some timestamps, or you are working with an existing database where the columns are named something else, jump straight on to configuration to see how to do that"

Upvotes: 2

ali idrees
ali idrees

Reputation: 9

subQuery: false wont work..!

But below code will work:

var user = sequelize.define('user', { /* bla */ }, {
  // don't add the timestamp attributes (updatedAt, createdAt)
  timestamps: false,
  // your other configuration here
});

Upvotes: 0

ZeroCho
ZeroCho

Reputation: 1360

Why don't you use

sequelize.define('AdminUser', {
  // Columns
}, {
  timestamps: true,
});

and check if there's createdAt column in REAL DATABASE as changing sequelize model doesn't reflect your change to REAL DATABASE automatically.

Upvotes: 1

Related Questions