Kysely•12mo ago

CASE with subquery clause

How can I return the column value from the subquery in a CASE? With this example, Kysely thinks the second condition is { name: string }:

eb.case()
.when('re.type', '=', 'company')
.then(eb.ref('cd.name'))
.else(
    eb
        .selectFrom('rise.companies_data as cd')
        .select(['cd.name'])
        .where(
            'cd.riseid',
            '=',
            eb.selectFrom('rise.rise_entities as re2')
                .select(['re2.parent_riseid'])
                .where('re2.type', '=', 'team')
                .whereRef('re2.riseid', '=', 're.riseid'),
            ),
    )
    .end()
    .as('company_name')

eb.case()
.when('re.type', '=', 'company')
.then(eb.ref('cd.name'))
.else(
    eb
        .selectFrom('rise.companies_data as cd')
        .select(['cd.name'])
        .where(
            'cd.riseid',
            '=',
            eb.selectFrom('rise.rise_entities as re2')
                .select(['re2.parent_riseid'])
                .where('re2.type', '=', 'team')
                .whereRef('re2.riseid', '=', 're.riseid'),
            ),
    )
    .end()
    .as('company_name')

7 Replies

Igal•12mo ago

Hey 👋🏻 We probably allowed expressions, but didn't really handle select queries there at the type-level. Ideally, we'd only allow selects with a single result column and use it's type as the subquery's result instead of an object.

thelinuxlichOP•12mo ago

But that would be tricky right Would need a helper like $scalar because probably there are use cases where the user expects a tuple

koskimas•12mo ago

Yeah we already handle SelectQueryBuilder<DB, TB, Record<string, T>> specifically in some places for this very reason. We need to do it here as well. In other places, we just do something like R[keyof R] and assume only a single column is selected by the subquery. I don't think it's possible to make sure of that in the type level. At least without something crazy. There's this helper for the job

export type ExtractTypeFromValueExpression<VE> =
  VE extends SelectQueryBuilderExpression<Record<string, infer SV>>
    ? SV
    : VE extends Expression<infer V>
    ? V
    : VE

export type ExtractTypeFromValueExpression<VE> =
  VE extends SelectQueryBuilderExpression<Record<string, infer SV>>
    ? SV
    : VE extends Expression<infer V>
    ? V
    : VE

thelinuxlichOP•12mo ago

maybe this is recipe worthy?

koskimas•12mo ago

No I meant we should fix this internally Could you create an issue?

thelinuxlichOP•12mo ago

koskimas•11mo ago

This is now fixed in master

Gaming

Programming

CASE with subquery clause