Function buildWhereSQLQuery

packages/infra/src/Store/SQL/query.ts:154–603

(
  dialect: SQLDialect,
  idKey: PropertyKey,
  filter: readonly FilterResult[],
  tableName: string,
  defaultValues: Record<string, unknown>,
  select?: NonEmptyReadonlyArray<
    string | {
      key: string
      subKeys: readonly string[]
    } | {
      key: string
      computed: ComputedProjectionIrExpression
    } | {
      key: string
      path: string
    } | {
      key: string
      aggregate: AggregateIrExpression
    }
  >,
  order?: NonEmptyReadonlyArray<{ key: string; direction: "ASC" | "DESC" }>,
  skip?: number,
  limit?: number,
  namespace?: string
)

const sqlStringLiteral = (value: string) => `'${value.replaceAll("'", "''")}'`

export function buildWhereSQLQuery(
  dialect: SQLDialect,
  idKey: PropertyKey,
  filter: readonly FilterResult[],
  tableName: string,
  defaultValues: Record<string, unknown>,
  select?: NonEmptyReadonlyArray<
    string | {
      key: string
      subKeys: readonly string[]
    } | {
      key: string
      computed: ComputedProjectionIrExpression
    } | {
      key: string
      path: string
    } | {
      key: string
      aggregate: AggregateIrExpression
    }
  >,
  order?: NonEmptyReadonlyArray<{ key: string; direction: "ASC" | "DESC" }>,
  skip?: number,
  limit?: number,
  namespace?: string
) {
  const params: unknown[] = []
  let paramIndex = 1

  const addParam = (value: unknown): string => {
    params.push(dialect.serializeScalar(value))
    return dialect.placeholder(paramIndex++)
  }

  const fieldExpr = (path: string, relation?: string): string => {
    if (path === idKey || path === "id") return "id"
    if (relation && path.includes(".-1.")) {
      const subPath = path.split(".-1.")[1]!
      if (subPath.endsWith(".length")) {
        // TODO: array length inside relation element
        return dialect.jsonExtractElement(`_${relation}`, subPath.slice(0, -".length".length))
      }
      return dialect.jsonExtractElement(`_${relation}`, subPath)
    }
    if (path.endsWith(".length")) {
      const arrPath = dottedToJsonPath(path.slice(0, -".length".length))
      return dialect.arrayLength(arrPath)
    }
    const jsonPath = dottedToJsonPath(path)
    const expr = dialect.jsonExtract(jsonPath)
    const topKey = path.split(".")[0]
    if (topKey in defaultValues) {
      return `COALESCE(${expr}, ${addParam(defaultValues[topKey])})`
    }
    return expr
  }

  const statement = (x: FilterR, relation?: string): string => {