Is there a way to use Coalesce in where clause using findoptions?

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

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

I tried something like

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

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

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

Solution

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

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

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