Kysely•11mo ago

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"
)

Is there a better way to express this?

13 Replies

Unknown User•11mo ago

Message Not Public

koskimas•11mo ago

Something like this. Didn't try to run it.

.leftJoin(
  "mz_catalog.mz_clusters as c",
  (join) => join.on(eb => eb(
    "c.id",
    "=",
    eb.fn.coalesce(
      "so.cluster_id",
      "si.cluster_id",
      "mv.cluster_id",
      "i.cluster_id",
    )
  ))
)

.leftJoin(
  "mz_catalog.mz_clusters as c",
  (join) => join.on(eb => eb(
    "c.id",
    "=",
    eb.fn.coalesce(
      "so.cluster_id",
      "si.cluster_id",
      "mv.cluster_id",
      "i.cluster_id",
    )
  ))
)

By the way @igalk that JoinBuilder is kinda useless right now. We could probably just drop it and pass an expression builder to the first callback We might later have some join-specific crap though

Unknown User•11mo ago

Message Not Public

koskimas•11mo ago

yep

Unknown User•11mo ago

Message Not Public

Robin•11mo ago

@koskimas thanks! Your example was missing an onRef but this works:

      .leftJoin("mz_catalog.mz_clusters as c", (join) =>
        join.on((eb) =>
          eb.onRef(
            "c.id",
            "=",
            eb.fn.coalesce(
              "so.cluster_id",
              "si.cluster_id",
              "mv.cluster_id",
              "i.cluster_id"
            )
          )
        )
      )

      .leftJoin("mz_catalog.mz_clusters as c", (join) =>
        join.on((eb) =>
          eb.onRef(
            "c.id",
            "=",
            eb.fn.coalesce(
              "so.cluster_id",
              "si.cluster_id",
              "mv.cluster_id",
              "i.cluster_id"
            )
          )
        )
      )

koskimas•11mo ago

Shouldn't need onRef there

Robin•11mo ago

This expression is not callable.
  Type 'OnExpressionBuilder<{ "mz_catalog.mz_array_types": MzCatalogMzArrayTypes; "mz_catalog.mz_audit_events": MzCatalogMzAuditEvents; "mz_catalog.mz_aws_privatelink_connections": MzCatalogMzAwsPrivatelinkConnections; ... 125 more ...; c: MzCatalogMzClusters; }, "o" | ... 6 more ... | "c">' has no call signatures. (tsserver 2349)

This expression is not callable.
  Type 'OnExpressionBuilder<{ "mz_catalog.mz_array_types": MzCatalogMzArrayTypes; "mz_catalog.mz_audit_events": MzCatalogMzAuditEvents; "mz_catalog.mz_aws_privatelink_connections": MzCatalogMzAwsPrivatelinkConnections; ... 125 more ...; c: MzCatalogMzClusters; }, "o" | ... 6 more ... | "c">' has no call signatures. (tsserver 2349)

that's the error your code gives me

koskimas•11mo ago

eb doesn't even have onRef method Which Kysely version are you using?

Robin•11mo ago

0.25.0

Unknown User•11mo ago

Message Not Public

Robin•11mo ago

oh I see, it's deprecated

koskimas•11mo ago

and already removed in 0.26.0 We move fast 😅

Gaming

Programming

How to use coalesce in a join