Kysely•15mo ago

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.

7 Replies

AdophilusOP•15mo ago

here's the screenshot of the code

koskimas•15mo 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

AdophilusOP•15mo 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•15mo 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

AdophilusOP•15mo ago

yeah thanks so much for this 😁 how do I mark this post as solved 🥲

koskimas•15mo ago

Three dots --> Apps --> Mark solution

AdophilusOP•15mo ago

Okay Thanks

Gaming

Programming

Issue Getting TypeScript to work with nested query