Sequelize column Table.createdAt does not exist

Question

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

You can add `logging: console.log` to the options object to see what the generated SQL looks like in both situations. That might shed some light on what's happening. — 5ar, Oct 21 '18 at 13:31
Thanks. I've added the SQL query. I'm not sure why "AdminUser"."createdAt" does not exist. It is present in the DB. — Mayur, Oct 21 '18 at 13:51

5ar · Accepted Answer · 2023-04-25T12:52:27.397

4

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.

edited Apr 25 '23 at 12:52

answered Oct 21 '18 at 14:04

5ar

2,069
10
27

1

`subQuery: false` did it. Thanks. – Mayur Oct 21 '18 at 14:51
how you add `subquery: false`, which options object are you referring to? I'm having a similar issue – guest Apr 15 '23 at 22:57
The first argument of `findAll` is called "options" in the [documentation](https://sequelize.org/api/v6/class/src/model.js~model#static-method-findAll). It has an undocumented property called `subQuery` and you use it like so: `Model.findAll({ subQuery: false, /* other stuff */ })` – 5ar Apr 25 '23 at 12:50

score 2 · Answer 2 · edited Apr 12 '22 at 03:42

2

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"

edited Apr 12 '22 at 03:42

bguiz

27,371
47
154
243

answered Apr 09 '22 at 14:52

Bachisheo

21
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 09 '22 at 20:41

score 1 · Answer 3 · answered Oct 21 '18 at 12:56

1

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.

answered Oct 21 '18 at 12:56

ZeroCho

1,358
10
23

That did not work. Actually I never set timestamps to false so they were always enabled. Also, I can see the columns created in the table in the postgres UI tool so they are definitely present. – Mayur Oct 21 '18 at 13:04
@Edl Oh, I thought you disabled it following SO answer you linked. What happens when you put createdAt column definition manually in AdminUser? – ZeroCho Oct 21 '18 at 13:07
No change, I still get the same error. That SO answer actually mentioned that the problem occurs due to, "But when you join, each column of the joined table will be aliased".. I'm not sure what this is though – Mayur Oct 21 '18 at 13:10

score 1 · Answer 4 · edited Mar 27 '20 at 21:38

1

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
});

edited Mar 27 '20 at 21:38

stealthyninja

10,343
11
51
59

answered Mar 27 '20 at 19:31

ali idrees

11
1

Why would they remove the timestamps if they want to use the `createdAt` timestamp in the query, and stated in the question that they want to keep the timestamps? It's possible that you have a different use case / issue in your code where this helped, however this is not an answer to the user's question. – 5ar Jan 29 '21 at 16:04

Sequelize column Table.createdAt does not exist

4 Answers4