How to use coalesce in a join

I'm selecting disparate object types, and then looking up details in per-type lookup tables, then I want to join on another table based on an ID in the type tables, so I need to coalesce the ID columns. Currently I'm just doing a cast to make it work:

.leftJoin(
  "mz_catalog.mz_clusters as c",
  "c.id",
  // I can't figure out a typesafe way to represent this :(
  sql`COALESCE(so.cluster_id, si.cluster_id, mv.cluster_id, i.cluster_id)` as unknown as "so.cluster_id"
)

.leftJoin(
  "mz_catalog.mz_clusters as c",
  "c.id",
  // I can't figure out a typesafe way to represent this :(
  sql`COALESCE(so.cluster_id, si.cluster_id, mv.cluster_id, i.cluster_id)` as unknown as "so.cluster_id"
)

.leftJoin(
  "mz_catalog.mz_clusters as c",
  "c.id",
  // I can't figure out a typesafe way to represent this :(
  sql`COALESCE(so.cluster_id, si.cluster_id, mv.cluster_id, i.cluster_id)` as unknown as "so.cluster_id"
)

.leftJoin(
  "mz_catalog.mz_clusters as c",
  "c.id",
  // I can't figure out a typesafe way to represent this :(
  sql`COALESCE(so.cluster_id, si.cluster_id, mv.cluster_id, i.cluster_id)` as unknown as "so.cluster_id"
)

Is there a better way to express this?

How to use coalesce in a join

Similar Threads

Similar Threads

Similar Threads