I’m trying to translate the following SQL query into Drizzle ORM in TypeScript:

await this.drizzleDB.execute(
        sql`
          SELECT i.id, i.title, iu.description, iu.last_updated_timestamp
          FROM incidents i
          LEFT JOIN LATERAL (
            SELECT *
            FROM incident_updates iu
            WHERE iu.incident_id = i.id
            ORDER BY iu.last_updated_timestamp DESC
            LIMIT 1
          ) iu ON TRUE
          ORDER BY i.id
        `
      ))

Table Defenition:

export const incident = pgTable('incidents', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
});

export const incidentUpdates = pgTable('incident_updates', {
  id: serial('id').primaryKey(),
  incidentId: integer('incident_id').references(() => incidents.id),
  description: text('description').notNull(),
  lastUpdatedTimestamp: timestamp('last_updated_timestamp').defaultNow(),
  status: text('status').notNull(),
});

Question:

How can I replicate the above SQL query, that selects the latest incident_update per incident, using Drizzle ORM without resorting to raw SQL? Is there a way to perform this query type using Drizzle’s query builder methods?

Stack: PostgreSQL, Drizzle ORM, TypeScript