TheMelonAssassin Comments - Answer Overflow

Topics

TheMelonAssassin

•Created by TheMelonAssassin on 9/13/2024 in #help

Creating helper functions

Okay I understand, thanks a lot again

14 replies

•Created by TheMelonAssassin on 9/13/2024 in #help

Creating helper functions

I was wondering about two things after messing around more with extracting parts of my queries. 1. Why did you go with whereRef("module.ID", "=", moduleId) here? Isn't the moduleID always a value in this scope? I understand it's passed down as a column reference from the main function. 2. Say I want to use this function to use this function with an actual moduleID rather then a column ref. Would I just need to the following:

function moduleWithColor(moduleId: Expression<number> | number)

function moduleWithColor(moduleId: Expression<number> | number)

14 replies

•Created by TheMelonAssassin on 9/13/2024 in #help

Creating helper functions

Yeah I totally understand haha, I meant that before asking my question I did the same but didn't think to include the first jsonObjectFrom in the return of my helper That's why it wasn't working

14 replies

•Created by TheMelonAssassin on 9/13/2024 in #help

Creating helper functions

Appreciate the anwser, I tried with the expression builder but didn't realise I needed the first jsonObjectFrom. Also wanted to say thanks in general for the work you guys put in. Been loving working with this as I'm creating my first typescrip backend

14 replies

•Created by WhyDoThis on 9/5/2024 in #help

Updateable primary key question & .set() type safety

Could be me misunderstanding typescript (still getting used to it), but when I hover over my InsertableFoo type it returns the columns with a ?.

13 replies

•Created by WhyDoThis on 9/5/2024 in #help

Updateable primary key question & .set() type safety

Is it possible to do the opposite? For instance I want some col to always be present in Insertable or Updatable?

13 replies

•Created by TheMelonAssassin on 8/14/2024 in #help

Issue with 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")
    )
    .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();

14 replies

•Created by TheMelonAssassin on 8/14/2024 in #help

Issue with select

I'll post my full query if someone stumbles upon this

14 replies

•Created by TheMelonAssassin on 8/14/2024 in #help

Issue with select

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

14 replies

•Created by TheMelonAssassin on 8/14/2024 in #help

Issue with select

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

14 replies

•Created by TheMelonAssassin on 8/14/2024 in #help

Issue with select

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

14 replies

•Created by TheMelonAssassin on 8/13/2024 in #help

Issue with migrating from knex

The code just made the connection, ran migrations and if in development ran seeds, I would like to get to a point where my Kysely code does the same as the Knex code did

5 replies

•Created by TheMelonAssassin on 8/13/2024 in #help

Issue with migrating from knex

Yes I did

5 replies