K
Kysely10mo ago
Adophilus

Issue Getting TypeScript to work with nested query

Hi there 👋 . I'm experiencing an issue with getting typescript to play nice with Kysely. I should add that I'm still a newbie to Kysely and typescript in general. Here's the code:
await db.selectFrom((eb) => {
return eb.selectFrom("faqs")
.selectAll()
.offset((page - 1) * perPage)
.limit(perPage)
.as("faqs");
})
.select((eb) => [
eb.fn<number>("cast", [
eb.fn("count", [sql`faqs.id`]).as(
sql`int`,
),
]).as("count"),
eb.fn<Selectable<FaqsTable>[]>("json_agg", [sql`faqs`]).as("items"),
])
.executeTakeFirst(),
await db.selectFrom((eb) => {
return eb.selectFrom("faqs")
.selectAll()
.offset((page - 1) * perPage)
.limit(perPage)
.as("faqs");
})
.select((eb) => [
eb.fn<number>("cast", [
eb.fn("count", [sql`faqs.id`]).as(
sql`int`,
),
]).as("count"),
eb.fn<Selectable<FaqsTable>[]>("json_agg", [sql`faqs`]).as("items"),
])
.executeTakeFirst(),
Here's the expected sql code
select cast(count (*) as int) as "count", json_agg(faqs) as "items" from (
select * from "faqs" limit 20 offset 0
) as "faqs"
select cast(count (*) as int) as "count", json_agg(faqs) as "items" from (
select * from "faqs" limit 20 offset 0
) as "faqs"
But I keep getting this error with the typescript code above:
typescript: Type 'AliasedExpression<unknown, string>' is not assignable to type 'ReferenceExpression<{ faqs: AllSelection<Database, keyof Database>; ad_plans: AdPlansTable; users: UsersTable; roommate_profiles: RoommateProfilesTable; ... 9 more ...; tokens: TokensTable; }, "faqs">'.
Type 'AliasedExpression<unknown, string>' is missing the following properties from type 'SelectQueryBuilder<any, any, { [x: string]: any; }>': #private, expressionType, where, whereRef, and 48 more.
typescript: Type 'AliasedExpression<unknown, string>' is not assignable to type 'ReferenceExpression<{ faqs: AllSelection<Database, keyof Database>; ad_plans: AdPlansTable; users: UsersTable; roommate_profiles: RoommateProfilesTable; ... 9 more ...; tokens: TokensTable; }, "faqs">'.
Type 'AliasedExpression<unknown, string>' is missing the following properties from type 'SelectQueryBuilder<any, any, { [x: string]: any; }>': #private, expressionType, where, whereRef, and 48 more.
No description
7 Replies
Adophilus
Adophilus10mo ago
here's the screenshot of the code
No description
koskimas
koskimas10mo ago
That particular query is cleanest when you just use raw sql for the selections https://kyse.link/?p=s&i=q01BOoZU4VnOd28lWqrQ But that query seems a bit odd. count will always be equal to the limit parameter's value, in this case 20
Adophilus
Adophilus10mo ago
okay I don't actually know too much sql is there a better way of writing it? I want to get a list of rows based on an offset and limit and also get the total number of rows in the table is it possible? thanks for this
koskimas
koskimas10mo ago
Which dialect are you using? I'm guessing postgres from the json_agg(table) syntax I'd do something like this https://kyse.link/?p=s&i=vXniPbSUVtN4mUuUxSou
Adophilus
Adophilus10mo ago
yeah thanks so much for this 😁 how do I mark this post as solved 🥲
koskimas
koskimas10mo ago
Three dots --> Apps --> Mark solution
No description
Adophilus
Adophilus10mo ago
Okay Thanks