TheMelonAssassin Posts - Answer Overflow

TheMelonAssassin

•Created by TheMelonAssassin on 11/1/2024 in #help

Making Updateable<X> fields required

Excuse me if this has already been posted, but I recently turned on strict mode after completing my migration to TS and I'm struggling to make certain fields required when updating an entity. When I hover over UpdateableActivity it shows:

type UpdateableActivity = {
    ID?: number | undefined;
    name?: string | undefined;
    year?: number | undefined;
    isCurrent?: boolean | undefined;
    venueID?: number | undefined;
    moduleID?: number | undefined;
}

type UpdateableActivity = {
    ID?: number | undefined;
    name?: string | undefined;
    year?: number | undefined;
    isCurrent?: boolean | undefined;
    venueID?: number | undefined;
    moduleID?: number | undefined;
}

but hovering over Updateable shows:

type UpdateablePerson = Updateable<PersonTable>
// {
//   id?: number,
//   first_name?: string
// }

type UpdateablePerson = Updateable<PersonTable>
// {
//   id?: number,
//   first_name?: string
// }

if possible I'd like it to be something like:

type UpdateableActivity = {
    ID?: number;
    name: string;
    year: number;
    isCurrent: boolean;
    venueID: number;
    moduleID: number;
}

type UpdateableActivity = {
    ID?: number;
    name: string;
    year: number;
    isCurrent: boolean;
    venueID: number;
    moduleID: number;
}

https://kyse.link/MA3jQ is my playground link if that helps!

6 replies

KKysely

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

Issue with how timestamptz is returned depending on main or subquery

I'm running into the following issue where my date is returned in two different ways. Date is a timestamptz type column When I grab the lesson as followed:

export const getLessonByID = async (lessonID: number) => {
  const lesson = await db
    .selectFrom("lesson")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      Category.byID(eb.ref("lesson.categoryID")).as("category"),
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return lesson;
};

export const getLessonByID = async (lessonID: number) => {
  const lesson = await db
    .selectFrom("lesson")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      Category.byID(eb.ref("lesson.categoryID")).as("category"),
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return lesson;
};

I receive "date": "2024-09-13T22:00:00.000Z" When I use a subquery like this:

export const Lesson = {
  byID: (lessonID: Expression<number>) => {
    const eb = expressionBuilder<Database, never>();

    return jsonObjectFrom(
      eb
        .selectFrom("lesson")
        .select(["lesson.ID", "lesson.date", "lesson.index"])
        .whereRef("lesson.ID", "=", lessonID)
    );
  },
}


export const getGroupByLessonID = async (groupID: number, lessonID: number) => {
  const group = await db
    .selectFrom("group")
    .select((eb) => [
      "group.ID",
      "group.name",
      "group.rank",
      "group.minTeachers",
      "group.maxTeachers",
      "group.hours",
      "group.timeframe",
      "group.paymentType",
      Lesson.byID(eb.val(lessonID)).as("lesson"),
    ])
    .where("group.ID", "=", groupID)
    .executeTakeFirst();

  return group;
};

export const Lesson = {
  byID: (lessonID: Expression<number>) => {
    const eb = expressionBuilder<Database, never>();

    return jsonObjectFrom(
      eb
        .selectFrom("lesson")
        .select(["lesson.ID", "lesson.date", "lesson.index"])
        .whereRef("lesson.ID", "=", lessonID)
    );
  },
}


export const getGroupByLessonID = async (groupID: number, lessonID: number) => {
  const group = await db
    .selectFrom("group")
    .select((eb) => [
      "group.ID",
      "group.name",
      "group.rank",
      "group.minTeachers",
      "group.maxTeachers",
      "group.hours",
      "group.timeframe",
      "group.paymentType",
      Lesson.byID(eb.val(lessonID)).as("lesson"),
    ])
    .where("group.ID", "=", groupID)
    .executeTakeFirst();

  return group;
};

I receive "date": "2024-09-14T00:00:00+02:00"

15 replies

KKysely

•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