Kysely•3y 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 }

{ 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')

Igal•12/16/23, 10:54 PM

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•12/16/23, 11:14 PM

But that would be tricky right

thelinuxlichOP•12/16/23, 11:15 PM

Would need a helper like $scalar

thelinuxlichOP•12/17/23, 12:14 AM

because probably there are use cases where the user expects a tuple

koskimas•12/17/23, 4:27 PM

Yeah we already handle specifically in some places for this very reason. We need to do it here as well.

In other places, we just do something like 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.

koskimas•12/17/23, 4:31 PM

There's this helper for the job

thelinuxlichOP•12/18/23, 10:25 PM

maybe this is recipe worthy?

koskimas•12/19/23, 7:37 AM

No I meant we should fix this internally

koskimas•12/19/23, 9:11 AM

Could you create an issue?

thelinuxlichOP•12/19/23, 11:23 AM

koskimas•12/29/23, 11:04 PM

This is now fixed in master