KyselyK
Kysely•17mo ago•
13 replies
TheMelonAssassin

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;
};
Solution
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")


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.
Jump to solution
Was this page helpful?