Kysely•3mo ago

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;
};

Solution:

```ts function moduleWithColor(moduleId: Expression<string>) { const eb = expressionBuilder<Database, never>() return jsonObjectFrom(...

Jump to solution

9 Replies

Unknown User•3mo ago

Message Not Public

koskimas•3mo ago

See the helper examples at the end of this recipe https://kysely.dev/docs/recipes/relations

Solution

koskimas•3mo ago

function moduleWithColor(moduleId: Expression<string>) {
  const eb = expressionBuilder<Database, never>()

  return jsonObjectFrom(
    eb
      .selectFrom("module")
      .select(eb => [
        "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", "=", moduleId)
  )
}

function moduleWithColor(moduleId: Expression<string>) {
  const eb = expressionBuilder<Database, never>()

  return jsonObjectFrom(
    eb
      .selectFrom("module")
      .select(eb => [
        "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", "=", moduleId)
  )
}

and call it like this

moduleWithColor(eb.ref("activity.moduleID")).as("module")

moduleWithColor(eb.ref("activity.moduleID")).as("module")

by the way, there's no reason to join module using innerJoin in your example. You don't use it anywhere. You probably joined it to be able to reference module.colorID but all you needed to do is get another expression builder that has the module table in context. See my helper above.

koskimas•3mo ago

Just commenting something here to get this back to the top. The fucking answer overflow plugin hides these once marked solved Only way to find them is to remember the title and search for it

TheMelonAssassinOP•3mo ago

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

koskimas•3mo ago

It's the same code as in your example. I just moved a part of it to a function. There's exactly the same jsonObjectFrom calls.

TheMelonAssassinOP•3mo ago

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 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)

koskimas•3mo ago

The dependencies to the outside context (scope) should be passed in as expressions (or plain values) so that the helper makes no assumptions about the tables or their names outside the helper. That allows you to use the helper anywhere. Not just when there's a table called activity in the context. That's why the created expression builder has never as the context (last type argument). You can create value expressions using eb.val(value). No need for Expression<number> | number.

TheMelonAssassinOP•3mo ago

Okay I understand, thanks a lot again

Gaming

Programming

Creating helper functions