Kysely•7mo ago

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),
            ])
        );
}

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>'.

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....

Jump to solution

6 Replies

kroltanOP•7mo ago

https://kyse.link/#cN4IgpgJglgLg9gJwM4gFyhgTwA5jSKAW20RgAJgAdAOzLIHExqwEBDGSAGhroGUwANmADGMVgCMh3WmQCq2COzASpPMgElqSFmMlhpAXzIAzBHEJlKIANaZtAzFYDcNGmAAeJBOSjUOCY1ZhMDIAEXYJVm0KNWgEERgAWTAkJFYAcxTUGJk6KAhspBgEX3S1Om0-AEEYbPCOcrJKiBYABQBXSShhAGkwTELi0sbhOD8mWqah6jLcsnjhKGwoCY6u3v7BkpnG7E6Bbr6Bqe3ZugNXagMQThAAR3aWTHwsXDIAeWwYJDIAXhy6O1tAg1gcNpgAPxbYZXS6jLTkOBfJDZT7fP4AshAtr7Q6bSwgYxwOBWGgXag0CDiAB09gSADEzIQABTMsAASj+AD5GmBGnRaYIGUzmVY4glkqkMikrOz+WRBUJRKyOdyyABteV0PlzOh6hXCKJgZly3X6hUAdwAFkxRWbzQTxaJJWlMkhBdQWiDceCrNIHQ6rL8-VrzUjvtTsd71kcyBCIWRqO0BAJ-QG6Kb0wKYDbqGzqfFjKKQE6kilXSkCyIlis-KC8Y4QOzM1nqYJtPnC8XSy7pe7mjiY-1ZS3023PSbQwKosW9kPG+y04GS1AFmWpW7qfkQ-bHauJeW+x6YDUd+mxfvnYfN-COH4z+aALqjvXU6BFXyid55tmc35csgwCrIsrDnMEjhHeVqWtFhjRCf9ALtdMgIAN1YARmXDd0o3rcE4wTJMU0XUMrF8B8HU1XdtWA7tL3XCt+yYL1cIgpslwDICuwvNde03BYa1WH1WOI3dH3lZsoMQL0ACFMFnIThybSSEBkuSrEqE8YD9R0UmEWUoJnKx4RQlg0hgKAxiQWU0xbNt3BEdoOBNEADFuaB0ISfAkDuA4OBuGw7EEZ50BAV48FQUKMn86hWEIcKQAABmpAAmAB2akABYXIMIA

Solution

koskimas•7mo ago

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!
        );
}

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"))

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.

koskimas•7mo ago

How is that playground you posted related?

kroltanOP•7mo ago

huh this loads something to do with publicKey or whatever which is the code from another question I had clicked earlier, idk what's up with that. must have not clicked to regenerate the hash or something, thoug i didnt see a buton for that. anyways thank you, that makes more sense yeah. what would be the return type I should use here? AliasableExpression<{ is_friend: SqlBool }> is not enough for kysely to resolve it as a single boolean. on the call site:

.select((eb) => [
    "edit_mode",
    session.queryIsFriendOf(eb.ref("author_id")).as("is_friend"),
])

.select((eb) => [
    "edit_mode",
    session.queryIsFriendOf(eb.ref("author_id")).as("is_friend"),
])

Then when accessing row.is_friend, I get back an object:

Deno: Argument of type { is_friend: SqlBool; } is not assignable to parameter of type boolean

(I can't use an implicit return type since this is actually an abstract method)

koskimas•7mo ago

Just leave out the return type. The implicit type will get resolved correctly But if you really want to have an explicit return type that works, It's something like

SelectQueryBuilder<
  DB & { check_is_friend: FriendshipTable },
  'check_is_friend',
  { is_friend: boolean },
>

SelectQueryBuilder<
  DB & { check_is_friend: FriendshipTable },
  'check_is_friend',
  { is_friend: boolean },
>

The next version will have a $asScalar() type helper for these cases. Then you could use AliasableExpression<boolean> as the result type and chain $asScalar() at the end of the query. Or you can use .$castTo<boolean>() at the end of the query for now and then an explicit AliasableExpression<boolean> as the return type. But that's not type-safe

kroltanOP•7mo ago

Fair, yeah i need an explicit type here since it's an implementation of an abstract method, and the abstract method requires an explicit return type. $asScalar would be perfect here, since the other implementation of this method would be just a simple eb.lit(false) nice, I'll use a the cast for now with a comment to upgrade when it comes out thank you for taking your time to explain things so clearly

Gaming

Programming

Subquery from a function

Did you find this page helpful?