KyselyK
Kysely•2y ago
kroltan

Subquery from a function

I want to reuse a sub-query that refers to one of the fields in the root query's result.

I tried doing this: 
function queryIsFriendOf<DB extends DatabaseSchema, TB extends keyof DB>(
    eb: ExpressionBuilder<DB, TB>,
    on: AnyColumn<DB, TB>,
): AliasableExpression<boolean> {
    return eb.selectFrom("friendship as check_is_friend")
        .whereRef("check_is_friend.target_id", "=", on) // using field name from parent query
        .select((eb) =>
            eb.or([
                eb("check_is_friend.target_id", "is", null),
                eb("check_is_friend.target_id", "=", this.userId),
            ])
        );
}


However, I get a bunch o' errors I don't quite understand:
TS2345 [ERROR]: Argument of type 'string' is not assignable to parameter of type 'ReferenceExpression<DB & ("friendship" extends keyof DB ? { check_is_friend: DB[keyof DB & "friendship"]; } : never), "check_is_friend" | TB>'.


If I pick that exact expression and paste it directly in the .select(eb => [/* here */]) of the parent query, then there are no errors.

I assume that it's trying to tell me that check_is_friend might collide with the parent's scope or something like that? Am I doing something silly, is there a better way to do this?
Solution
Don't pass in the expression builder. Its type easily becomes incompatible in other contexts.

Don't use an explicit result type. The result type here is NOT boolean. It's { is_friend: boolean }. Yes, you can use that as a scalar in SQL and Kysely does handle that correctly. But don't explicitly set the wrong type.

You always need to provide a name for selections using the as method. The name is dialect-specific if you leave it out. Since kysely types don't know which dialect you're using, providing a name for that column automatically is impossible.

function queryIsFriendOf(
    // Assuming `friendship.target_id` is string.
    on: Expression<string>,
) {
    // You don't need an `ExpressionBuilder` for this.
    // Just use `db`.
    //
    // If your helpers DO need an expression builder,
    // you can create a context-agnostic builder using
    // const eb = expressionBuilder<DB, never>()
    return db.selectFrom("friendship as check_is_friend")
        .where("check_is_friend.target_id", "=", on)
        .select((eb) =>
            // Don't forget to use `as` to give a name
            // for your selections.
            eb.or([
                eb("check_is_friend.target_id", "is", null),
                eb("check_is_friend.target_id", "=", this.userId),
            ]).as("is_friend") // <-- needed!
        );
}


and then use it like this

queryIsFriendOf(eb.ref("some_column"))


If I pick that exact expression and paste it directly in the .select(eb => [/* here */]) of the parent query, then there are no errors.

This is definitely not true. Your code had a bunch of errors. If that worked, you've disabled typescript type-checks completely.

Yes, the compiled javascript probably works by accident. But you've compiled it without any type-checks if it does.
Jump to solution
Was this page helpful?