Kysely•4mo ago

Issue with select

How would I handle doing what I'm doing in the raw part during the select?

 const result = await db
    .with("townhallDistribution", (qb) =>
      qb
        .selectFrom("account")
        .innerJoin("townhall", "account.townhallID", "townhall.ID")
        .select(["townhall.level", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("townhall.level")
    )
    .selectFrom("clan")
    .select([
      "clan.ID",
      "clan.name",
      "clan.level",
      "clan.location",
      "clan.language",
      "clan.cwl",
      "clan.longestWinStreak",
      sql`
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'level', townhall_level,
                'amount', amount
              )
            )
            FROM townhallDistribution
          ),
          '[]'
        ) AS townhallDistribution
      `,
    ])
    .executeTakeFirst();

 const result = await db
    .with("townhallDistribution", (qb) =>
      qb
        .selectFrom("account")
        .innerJoin("townhall", "account.townhallID", "townhall.ID")
        .select(["townhall.level", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("townhall.level")
    )
    .selectFrom("clan")
    .select([
      "clan.ID",
      "clan.name",
      "clan.level",
      "clan.location",
      "clan.language",
      "clan.cwl",
      "clan.longestWinStreak",
      sql`
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'level', townhall_level,
                'amount', amount
              )
            )
            FROM townhallDistribution
          ),
          '[]'
        ) AS townhallDistribution
      `,
    ])
    .executeTakeFirst();

I get the following error: No overload matches this call. Overload 1 of 3, '(selections: readonly SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">[]): SelectQueryBuilder<Database & { ...; }, "clan", { ...; }>', gave the following error. Type 'RawBuilder<unknown>' is not assignable to type 'SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">'. Type 'RawBuilder<unknown>' is missing the following properties from type 'DynamicReferenceBuilder<any>': #private, dynamicReference, refType Overload 2 of 3, '(callback: SelectCallback<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">): SelectQueryBuilder<Database & { ...; }, "clan", { ...; }>', gave the following error. Argument of type '(string | RawBuilder<unknown>)[]' is not assignable to parameter of type 'SelectCallback<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">'. Type '(string | RawBuilder<unknown>)[]' provides no match for the signature '(eb: ExpressionBuilder<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">): readonly SelectExpression<Database & { ...; }, "clan">[]'. Overload 3 of 3, '(selection: SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">): SelectQueryBuilder<Database & { ...; }, "clan", { ...; }>', gave the following error. Argument of type '(string | RawBuilder<unknown>)[]' is not assignable to parameter of type 'SelectExpression<Database & { townhallDistribution: { level: number; amount: string | number | bigint; }; }, "clan">'.ts(2769)

8 Replies

koskimas•4mo ago

Did you notice the documentation is full of examples? There's also an examples section in kysely.dev.

TheMelonAssassinOP•4mo ago

I've been scrolling through that to understand the builder, but I must've missed the example that pertains to my issue

koskimas•4mo ago

You need to provide a type and an alias for the raw SQL snippet.

      sql<{ level: string, amount: number }[]>`
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'level', townhall_level,
                'amount', amount
              )
            )
            FROM townhallDistribution
          ),
          '[]'
        )
      `.as('townhallDistribution'),

      sql<{ level: string, amount: number }[]>`
        COALESCE(
          (
            SELECT json_agg(
              json_build_object(
                'level', townhall_level,
                'amount', amount
              )
            )
            FROM townhallDistribution
          ),
          '[]'
        )
      `.as('townhallDistribution'),

TheMelonAssassinOP•4mo ago

I'm sorry but which example? It's been a long day so it's probably the fatigue

koskimas•4mo ago

https://kysely.dev/docs/examples/select/complex-selections https://kysely-org.github.io/kysely-apidoc/interfaces/SelectQueryBuilder.html#select On the second one, I can't link to the exact position, but it's there

koskimas•4mo ago

koskimas•4mo ago

You can see that same documentation just by hovering over the select method

TheMelonAssassinOP•4mo ago

Really must've missed it, didn't mean to annoy you my bad. Very new to kysely and TS, and tbh as I've learned in this project coding in general I'll post my full query if someone stumbles upon this

  const result = await db
    .with("townhallDistribution", (qb) =>
      qb
        .selectFrom("account")
        .innerJoin("townhall", "account.townhallID", "townhall.ID")
        .select(["townhall.level", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("townhall.level")
    )
    .with("nationalityDistribution", (qb) =>
      qb
        .selectFrom("account")
        .select(["account.nationality", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("account.nationality")
    )
    .with("roleDistribution", (qb) =>
      qb
        .selectFrom("account")
        .select(["account.role", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("account.role")
    )
    .with("cwlDistribution", (qb) =>
      qb
        .selectFrom("cwl")
        .select([
          "year",
          sql`COUNT(CASE WHEN "placementType" = ${PLACEMENTTYPES.PROMOTION} THEN 1 END)`.as(
            "promotions"
          ),
          sql`COUNT(CASE WHEN "placementType" = ${PLACEMENTTYPES.SAFE} THEN 1 END)`.as(
            "safes"
          ),
          sql`COUNT(CASE WHEN "placementType" = ${PLACEMENTTYPES.DEMOTION} THEN 1 END)`.as(
            "demotions"
          ),
        ])
        .where("clanID", "=", id)
        .groupBy("year")
    )
    .selectFrom("clan")
    .select([
      "clan.ID",
      "clan.name",
      "clan.level",
      "clan.location",
      "clan.language",
      "clan.cwl",
      "clan.longestWinStreak",
      sql<{ level: string; amount: number }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'level', level,
              'amount', amount
            )
          )
          FROM "townhallDistribution"
        ),
        '[]'
      )
    `.as("townhallDistribution"),
      sql<{ nationality: string; amount: number }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'nationality', nationality,
              'amount', amount
            )
          )
          FROM "nationalityDistribution"
        ),
        '[]'
      )
    `.as("nationalityDistribution"),
      sql<{ role: string; amount: number }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'role', role,
              'amount', amount
            )
          )
          FROM "roleDistribution"
        ),
        '[]'
      )
    `.as("roleDistribution"),
      sql<{
        year: number;
        promotions: number;
        safes: number;
        demotions: number;
      }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'year', year,
              'promotions', promotions,
              'safes', safes,
              'demotions', demotions
            )
          )
          FROM "cwlDistribution"
        ),
        '[]'
      )
    `.as("cwlDistribution"),
    ])
    .where("clan.ID", "=", id)
    .executeTakeFirst();

  const result = await db
    .with("townhallDistribution", (qb) =>
      qb
        .selectFrom("account")
        .innerJoin("townhall", "account.townhallID", "townhall.ID")
        .select(["townhall.level", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("townhall.level")
    )
    .with("nationalityDistribution", (qb) =>
      qb
        .selectFrom("account")
        .select(["account.nationality", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("account.nationality")
    )
    .with("roleDistribution", (qb) =>
      qb
        .selectFrom("account")
        .select(["account.role", db.fn.count("account.ID").as("amount")])
        .where("account.clanID", "=", id)
        .groupBy("account.role")
    )
    .with("cwlDistribution", (qb) =>
      qb
        .selectFrom("cwl")
        .select([
          "year",
          sql`COUNT(CASE WHEN "placementType" = ${PLACEMENTTYPES.PROMOTION} THEN 1 END)`.as(
            "promotions"
          ),
          sql`COUNT(CASE WHEN "placementType" = ${PLACEMENTTYPES.SAFE} THEN 1 END)`.as(
            "safes"
          ),
          sql`COUNT(CASE WHEN "placementType" = ${PLACEMENTTYPES.DEMOTION} THEN 1 END)`.as(
            "demotions"
          ),
        ])
        .where("clanID", "=", id)
        .groupBy("year")
    )
    .selectFrom("clan")
    .select([
      "clan.ID",
      "clan.name",
      "clan.level",
      "clan.location",
      "clan.language",
      "clan.cwl",
      "clan.longestWinStreak",
      sql<{ level: string; amount: number }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'level', level,
              'amount', amount
            )
          )
          FROM "townhallDistribution"
        ),
        '[]'
      )
    `.as("townhallDistribution"),
      sql<{ nationality: string; amount: number }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'nationality', nationality,
              'amount', amount
            )
          )
          FROM "nationalityDistribution"
        ),
        '[]'
      )
    `.as("nationalityDistribution"),
      sql<{ role: string; amount: number }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'role', role,
              'amount', amount
            )
          )
          FROM "roleDistribution"
        ),
        '[]'
      )
    `.as("roleDistribution"),
      sql<{
        year: number;
        promotions: number;
        safes: number;
        demotions: number;
      }>`
      COALESCE(
        (
          SELECT json_agg(
            json_build_object(
              'year', year,
              'promotions', promotions,
              'safes', safes,
              'demotions', demotions
            )
          )
          FROM "cwlDistribution"
        ),
        '[]'
      )
    `.as("cwlDistribution"),
    ])
    .where("clan.ID", "=", id)
    .executeTakeFirst();

Gaming

Programming

Issue with select