有没有办法使用 findoptions 在 where 子句中使用 Coalesce?

问题描述

我对 typeorm 比较陌生，在使用 FindOptions 重新创建我们的一个查询时遇到了一些问题.我找到了一种使用 typeorms QueryBuilder 编写它的方法，但希望在使用它们的 findOptions 时利用 typeorm 提供的一些其他功能(例如定义实体关系时的eager标志).

I'm relatively new to typeorm and am having some issues recreating one of our queries using FindOptions. I found a way to write it using typeorms QueryBuilder, but would like leverage some of the other functionality that typeorm offers when using their findOptions (such as the eager flag when defining entity relationships).

const sql = this.repository
      .createQueryBuilder('table1','t1')
      .where('COALESCE(t1.deleted_at, t1.updated_at, t1.created_at) >= :timestamp', {
        timestamp: timestamp,
      })

我找不到任何与在 typeorm 中使用合并相关的文档或示例.具体来说，这是我似乎无法用 findOptions 重写的部分

I haven't been able to find any documentation or examples that relate to using coalesce in typeorm. Specifically, this is the part I can't seem to rewrite with findOptions

.where('COALESCE(t1.deleted_at, t1.updated_at, t1.created_at) >= :timestamp', {
        timestamp: timestamp,
      })

我尝试过类似的东西

this.repository.find({
      where: Raw('COALESCE(t1.deleted_at, t1.updated_at, t1.created_at) >= :timestamp', {
        timestamp: timestamp,
      }),

但显然这不是有效的语法，因为 Raw() 需要列别名.希望这已经充分描述了我的问题！

but obviously that is not valid syntax, as Raw() expects the column alias. Hopefully this has adequately described my issue!

附注.我是 SO 新手，将感谢有关如何提高我的问题质量的任何反馈

PS. I'm new to SO and will appreciate any feedback regarding how to improve the quality of my question(s)

推荐答案

目前 TypeORM 在 find-where 中没有 COALESCE operator子句，因为(正如您所写的那样)它需要一个别名.

Currently TypeORM does not have a COALESCE operator in find-where clause since (as you correclty wrote) it expects an alias.

你可以欺骗 TypeORM 结合 where 子句和 Raw(忽略带有 _ 的别名)使用有效的柱子.请参阅此了解更多信息.然而，这有点令人困惑，并且完全不正确.

You can trick TypeORM combining where clause and Raw (ignoring the alias with _) using a valid column.See this for more information.However, this is a little bit confusing and not purely correct.

const result = await this.repository.find({
  where: {
    created_at: Raw(_ => `COALESCE(deleted_at, updated_at, created_at) >= :timestamp`, { timestamp })
  }
});

最正确的方法是使用 QueryBuilder:

The most correct approach is using QueryBuilder:

const result = await this.repository
  .createQueryBuilder('table1', 't1')
  .where('COALESCE(t1.deleted_at, t1.updated_at, t1.created_at) >= :timestamp', {
    timestamp: timestamp,
  })
  .getMany();

这篇关于有没有办法使用 findoptions 在 where 子句中使用 Coalesce?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！