Function computedSelectExpr

packages/infra/src/Store/SQL/query.ts:400–504

(key: string, computed: ComputedProjectionIrExpression)

  }

  const computedSelectExpr = (key: string, computed: ComputedProjectionIrExpression): string => {
    const relationPath = dottedToJsonPath(computed.path)
    const relationAlias = `_${computed.path}`
    const relationFrom = dialect.jsonEachFrom(relationPath, relationAlias)
    const toNumber = (expr: string) =>
      dialect.jsonColumnType === "JSON" ? `CAST(${expr} AS REAL)` : `(${expr})::numeric`
    const compileExpr = (expression: ComputedProjectionMathIrExpression): string => {
      switch (expression._tag) {
        case "field":
          return toNumber(dialect.jsonExtractElement(relationAlias, expression.field))
        case "mul":
          return `(${compileExpr(expression.left)} * ${compileExpr(expression.right)})`
        default:
          return assertUnreachable(expression)
      }
    }
    const factorCaseExpr = (unitExpr: string, toBase: string, factors: Readonly<Record<string, number>>) => {
      const entries = Object.entries(factors).filter(([, factor]) => Number.isFinite(factor))
      const cases = entries.map(([unit, factor]) => ` WHEN ${sqlStringLiteral(unit)} THEN ${factor}`).join("")
      return `CASE ${unitExpr} WHEN ${sqlStringLiteral(toBase)} THEN 1${cases} ELSE NULL END`
    }
    const filter = "filter" in computed ? computed.filter : []
    const whereClause = () =>
      filter.length > 0
        ? ` WHERE ${print(filter, computed.path, false)}`
        : ""
    const boolExpr = (sqlExpr: string) =>
      dialect.jsonColumnType === "JSON"
        ? `CASE WHEN ${sqlExpr} THEN 'true' ELSE 'false' END AS "${key}"`
        : `${sqlExpr} AS "${key}"`
    switch (computed._tag) {
      case "relation-count":
        return `(SELECT COUNT(1) FROM ${relationFrom}${whereClause()}) AS "${key}"`
      case "relation-any":
        return boolExpr(`EXISTS(SELECT 1 FROM ${relationFrom}${whereClause()})`)
      case "relation-every":
        // ∀x.P(x) ≡ ¬∃x.¬P(x). When no filter, no element exists that violates ⊤ → true.
        return boolExpr(
          filter.length === 0
            ? `1=1`
            : `NOT EXISTS(SELECT 1 FROM ${relationFrom} WHERE NOT (${print(filter, computed.path, false)}))`
        )
      case "relation-distinct-count": {
        const fieldExtract = dialect.jsonExtractElement(relationAlias, computed.field)
        return `(SELECT COUNT(DISTINCT ${fieldExtract}) FROM ${relationFrom}${whereClause()}) AS "${key}"`
      }
      case "relation-sum": {
        const fieldExtract = dialect.jsonExtractElement(relationAlias, computed.field)
        return `(SELECT COALESCE(SUM(${toNumber(fieldExtract)}), 0) FROM ${relationFrom}${whereClause()}) AS "${key}"`
      }
      case "relation-sum-expr": {
        const expression = compileExpr(computed.expression)
        return `(SELECT COALESCE(SUM(${expression}), 0) FROM ${relationFrom}${whereClause()}) AS "${key}"`
      }
      case "relation-sum-expr-by": {
        const expression = compileExpr(computed.expression)
        const unitExpr = dialect.jsonExtractElement(relationAlias, computed.unit)
        if (dialect.jsonColumnType === "JSON") {