Dynamic case building

[MrVhek]

Hi,

Is there any way to build a dynamic case that will be build with a tab ? Was thinking of something like this:

interface CaseCondition {
    when: string
    then: string
}

export function multipleCases(cases: CaseCondition[], defaultValue: string): any {
    let sqlQuery: string = `CASE `
    for (const c of cases) {
        sqlQuery += `WHEN ${c.when} THEN ${c.then} `
    }
    return sqlQuery + `ELSE ${defaultValue} END`
}

 await tx.$kysely.updateTable("VariableCategoricalValues")
        .leftJoin("VariableValues", "VariableValues.uid", "VariableCategoricalValues.variableValueUid")
        .set(() => ({
                value: multipleCases(Object.values(variableLevelsCorrespondancy).map(
                        (value: {oldIndex: number, newIndex: number}) => {
                                return {
                        when: `"VariableCategoricalValues"."value" = ${value.oldIndex}`,
                    then: value.newIndex.toString()
                }
            }
        ), `"VariableCategoricalValues"."value"`)
    })).where("VariableValues.variableUid", "=", variable.variableUid)
    .execute()

Thanks !

[koskimas]

You can use the case builder in the expression builder dynamically.

Having said that, it's currently quite hard as there are so many different builders that different methods return. We should probably streamline that a, even though it comes at the expense of type safety.

Building dynamic conditions should be simple.

[MrVhek]

export interface CaseCondition {
    when: Expression<unknown>
    then: Expression<unknown>
}

export function multipleCases<TB extends keyof DB>(
    cases: CaseCondition[],
    defaultValue: string,
    eb: ExpressionBuilder<DB, TB>,
): UpdateObject<DB, TB> {
    let cwb: CaseWhenBuilder<DB, TB, unknown, unknown> = eb
        .case()
        .when(cases[0].when)
        .then(cases[0].then)
    for (const c of cases) {
        if (c !== cases[0]) {
            cwb = cwb.when(c.when).then(c.then)
        }
    }
    return cwb.else(defaultValue).end()
}

I've tried something like this too but indeed the typing is quite difficult... Can you help me on this ? Or is there any helpers integrated in kysely ?

[koskimas]

The only things that's not working there is the incorrect return type UpdateObject. Just leave it out and you should be good https://kyse.link/21EAl