Kysely•15mo 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•15mo 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•15mo 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•15mo 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•15mo ago

maybe this is recipe worthy?

koskimas•15mo ago

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

thelinuxlichOP•15mo ago

koskimas•15mo ago

This is now fixed in master

Gaming

Programming

CASE with subquery clause

Did you find this page helpful?