Kysely•16mo 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

Igal (mobile)•16mo ago

Hey 👋 Cna you provide a https://kyse.link ?

koskimas•16mo 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

Igal (mobile)•16mo ago

@koskimas the one with on? yeah

koskimas•16mo ago

yep

Igal (mobile)•16mo ago

the extra nesting is ugly and not intuitive the examples should call the callback argument on once we change it

RobinOP•16mo 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•16mo ago

Shouldn't need onRef there

RobinOP•16mo 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•16mo ago

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

RobinOP•16mo ago

0.25.0

Igal (mobile)•16mo ago

if you can't upgrade to 0.26.x, use eb.cmpr

RobinOP•16mo ago

oh I see, it's deprecated

koskimas•16mo ago

and already removed in 0.26.0 We move fast 😅

Gaming

Programming

How to use coalesce in a join