TheMelonAssassin Posts - Answer Overflow

TheMelonAssassin

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

Creating helper functions

I've gotten to the point in my application where a lot of the objects I'm building in my queries are returning in other queries. In the following example I'd like to extract the module (with color) into something I can use in other queries. I've been trying to find the correct (and working) way to this

export const getSchematicByLessonID = async (lessonID: number) => {
  const schematic = await db
    .with("get_groups", () => findGroupsByLessonID(lessonID))
    .with("get_attendances", () => findAttendancesByLessonID(lessonID))
    .selectFrom("lesson")
    .innerJoin("activity", "lesson.activityID", "activity.ID")
    .innerJoin("module", "activity.moduleID", "module.ID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonObjectFrom(
        eb
          .selectFrom("module")
          .select([
            "module.ID",
            "module.name",
            "module.abbreviation",
            jsonObjectFrom(
              eb
                .selectFrom("color")
                .select([
                  "color.ID",
                  "color.name",
                  "color.hex",
                  "color.transparency",
                  "color.textColor",
                ])
                .whereRef("color.ID", "=", "module.colorID")
            ).as("color"),
          ])
          .whereRef("module.ID", "=", "activity.moduleID")
      ).as("module"),
       /* Other data */
    ])
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return schematic;
};

export const getSchematicByLessonID = async (lessonID: number) => {
  const schematic = await db
    .with("get_groups", () => findGroupsByLessonID(lessonID))
    .with("get_attendances", () => findAttendancesByLessonID(lessonID))
    .selectFrom("lesson")
    .innerJoin("activity", "lesson.activityID", "activity.ID")
    .innerJoin("module", "activity.moduleID", "module.ID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonObjectFrom(
        eb
          .selectFrom("module")
          .select([
            "module.ID",
            "module.name",
            "module.abbreviation",
            jsonObjectFrom(
              eb
                .selectFrom("color")
                .select([
                  "color.ID",
                  "color.name",
                  "color.hex",
                  "color.transparency",
                  "color.textColor",
                ])
                .whereRef("color.ID", "=", "module.colorID")
            ).as("color"),
          ])
          .whereRef("module.ID", "=", "activity.moduleID")
      ).as("module"),
       /* Other data */
    ])
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return schematic;
};

14 replies

KKysely

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

Making CTE's reusable

I wanted to make certain cte's in my project reusable and came up with the following solution. Is there a "kysely" way to do this?

export const getPiggyBank = async (teacherID: number) => {
  const lessons = await db
    .with("get_attendances", () => Attendance.byTeacherID(teacherID))
    .selectFrom("lesson")
    .innerJoin("get_attendances", "lesson.ID", "get_attendances.lessonID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonArrayFrom(
        eb
          .selectFrom("get_attendances as a")
          .select([
            "a.ID",
            "a.function",
            "a.hours",
            "a.paymentType",
            "a.hourlyWage",
          ])
          .whereRef("a.lessonID", "=", "lesson.ID")
      ).as("attendances"),
    ])
    .where("lesson.isActive", "=", true)
    .groupBy(["lesson.ID", "lesson.date", "lesson.index"])
    .execute();

  return lessons;
};

// CTE's

export const Attendance = {
  byTeacherID: (teacherID: number) =>
    db
      .selectFrom("attendance")
      .innerJoin("function", "attendance.functionID", "function.ID")
      .innerJoin("group", "attendance.groupID", "group.ID")
      .select([
        "attendance.ID",
        "attendance.lessonID",
        "group.hours",
        "group.paymentType",
        "function.name as function",
        "function.hourlyWage",
      ])
      .where("attendance.teacherID", "=", teacherID)
      .where("attendance.attendanceType", "=", ATTENDANCETYPES.PRESENT)
      .where("attendance.isActive", "=", true),
};

export const getPiggyBank = async (teacherID: number) => {
  const lessons = await db
    .with("get_attendances", () => Attendance.byTeacherID(teacherID))
    .selectFrom("lesson")
    .innerJoin("get_attendances", "lesson.ID", "get_attendances.lessonID")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      jsonArrayFrom(
        eb
          .selectFrom("get_attendances as a")
          .select([
            "a.ID",
            "a.function",
            "a.hours",
            "a.paymentType",
            "a.hourlyWage",
          ])
          .whereRef("a.lessonID", "=", "lesson.ID")
      ).as("attendances"),
    ])
    .where("lesson.isActive", "=", true)
    .groupBy(["lesson.ID", "lesson.date", "lesson.index"])
    .execute();

  return lessons;
};

// CTE's

export const Attendance = {
  byTeacherID: (teacherID: number) =>
    db
      .selectFrom("attendance")
      .innerJoin("function", "attendance.functionID", "function.ID")
      .innerJoin("group", "attendance.groupID", "group.ID")
      .select([
        "attendance.ID",
        "attendance.lessonID",
        "group.hours",
        "group.paymentType",
        "function.name as function",
        "function.hourlyWage",
      ])
      .where("attendance.teacherID", "=", teacherID)
      .where("attendance.attendanceType", "=", ATTENDANCETYPES.PRESENT)
      .where("attendance.isActive", "=", true),
};

4 replies

KKysely

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

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)

14 replies

KKysely

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

Issue with migrating from knex

I've recently learned about Kysely and how it is very compatible with TS compared to Knex. I'm also very new to coding in general and have been following the structure I was taught at school earlier this year but I'm struggling right now to migrate the file that initializes data and shuts it down. If anyone would be so kind to help me figure out how to move this from Knex to Kysely!

5 replies

Gaming

Programming