表"token“缺少FROM-子句条目

Question

我正在使用NestJs和TypeORM (Postgres数据库)，我想定期从数据库中删除过期的令牌。这是我的令牌实体：

@Entity('Token')
export class Token extends BaseEntity {
  @PrimaryColumn()
  public encoding: string;

  @Column()
  public expiresOn: Date;
}

我想从数据库中删除所有已过期的令牌。因此，expiresOn返回一个比当前日期更早的日期。

当我调用这个的时候

    await this.tokensRepository
      .createQueryBuilder('token')
      .delete()
      .from(Token) // I also tried it with 'token'
      .where('token.expiresOn <= :currentDate', { currentDate: new Date() })
      .execute();

我得到以下错误：

query: DELETE FROM "Token" WHERE token.expiresOn <= $1 -- PARAMETERS: ["2020-01-11T16:37:21.964Z"]
query failed: DELETE FROM "Token" WHERE token.expiresOn <= $1 -- PARAMETERS: ["2020-01-11T16:37:21.964Z"]
error: error: missing FROM-clause entry for table "token"
    at Connection.parseE (/home/matthias/Projects/nest-api/node_modules/pg/lib/connection.js:604:13)
    at Connection.parseMessage (/home/matthias/Projects/nest-api/node_modules/pg/lib/connection.js:403:19)
    at Socket.<anonymous> (/home/matthias/Projects/nest-api/node_modules/pg/lib/connection.js:123:22)
    at Socket.emit (events.js:210:5)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:290:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:182:23) {
  name: 'error',
  length: 116,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '27',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3240',
  routine: 'errorMissingRTE'
}
(node:18572) UnhandledPromiseRejectionWarning: QueryFailedError: missing FROM-clause entry for table "token"
    at new QueryFailedError (/home/matthias/Projects/nest-api/node_modules/typeorm/error/QueryFailedError.js:11:28)
    at Query.callback (/home/matthias/Projects/nest-api/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:176:38)
    at Query.handleError (/home/matthias/Projects/nest-api/node_modules/pg/lib/query.js:145:17)
    at Connection.connectedErrorMessageHandler (/home/matthias/Projects/nest-api/node_modules/pg/lib/client.js:214:17)
    at Connection.emit (events.js:210:5)
    at Socket.<anonymous> (/home/matthias/Projects/nest-api/node_modules/pg/lib/connection.js:128:12)
    at Socket.emit (events.js:210:5)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:290:11)
    at Socket.Readable.push (_stream_readable.js:224:10)

如果您想看一下存储库，它就在那里

https://github.com/matthiashermsen/nest-api/blob/master/src/authentications/expired-tokens-removal.service.ts#L60

如果您想尝试NestJs API，这是我的.env文件配置

SERVER_PORT = 3000

DATABASE_TYPE = postgres
DATABASE_HOST = localhost
DATABASE_PORT = 5432
DATABASE_USERNAME = postgres
DATABASE_PASSWORD = postgres
DATABASE_NAME = api
DATABASE_LOGGING = true
DATABASE_SYNCHRONIZE = true

NODE_ENV = development

AUTHENTICATION_SALT_ROUNDS = 12
TOKEN_SECRET = secret
TOKEN_EXPIRATION_IN_SECONDS = 36000
EXPIRED_TOKEN_REMOVAL_IN_MILLISECONDS = 5000

有人知道怎么解决这个问题吗？

Answer 1

如果在SQL中将标识符括在引号中，则必须按照存储的原样编写它。名称"Token"不等于token。您可能应该将实体声明更改为@Entity('token')。

Answer 2

我通过从令牌实体中的实体装饰器中删除名称来解决这个问题。

@Entity()
export class Token extends BaseEntity {
  @PrimaryColumn()
  public encoding: string;

  @Column()
  public expiresOn: Date;
}

接下来，我从文档https://github.com/typeorm/typeorm/blob/master/docs/delete-query-builder.md#delete-using-query-builder中获取样本并创建这个delete查询，这对我来说似乎很好用

await this.tokensRepository
  .createQueryBuilder()
  .delete()
  .from(Token)
  .where('expiresOn <= :currentDate', { currentDate: new Date() })
  .execute();