Kysely•3mo ago

select expression helper

I got this selectExpression that i use in many file .select([ 'e.id', eventStartDate('e'), eventEndDate('e'), 'e.title', 'e.type', 'e.status', 'e.banner', 'e.event_end_date as end_date', 'e.location', 'e.duration_in_minute', 'e.participant_count_limit', 'e.registration_date_limit', 'e.provider', coordinates('e'), sql<boolean>is_full_of_participants(e).as('isFullOfParticipants'), sql<string[]>get_event_sports(e.id).as('sports'), ]) how can i create a helper function in order to reuse it ?

Solution:

Hey 👋 Try this - https://kyse.link/QI1Q6 ```ts...

Jump to solution

6 Replies

TheMelonAssassin•3mo ago

Might help you along the way, since I'd need some more context for your example. I created the following helper:

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

    return jsonObjectFrom(
      eb
        .selectFrom("color")
        .select([
          "color.ID",
          "color.name",
          "color.hex",
          "color.transparency",
          "color.textColor",
        ])
        .whereRef("color.ID", "=", colorID)
    );
  },
};

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

    return jsonObjectFrom(
      eb
        .selectFrom("color")
        .select([
          "color.ID",
          "color.name",
          "color.hex",
          "color.transparency",
          "color.textColor",
        ])
        .whereRef("color.ID", "=", colorID)
    );
  },
};

I use this pattern to add some helper functions per entity I have. This is how the helper is called:

export const getAllModules = async () => {
  const modules = db
    .selectFrom("module")
    .select((eb) => [
      "module.ID",
      "module.name",
      "module.abbreviation",
      Color.byID(eb.ref("module.colorID")).as("color"),
    ])
    .where("module.isActive", "=", true)
    .execute();

  return modules;
};

export const getAllModules = async () => {
  const modules = db
    .selectFrom("module")
    .select((eb) => [
      "module.ID",
      "module.name",
      "module.abbreviation",
      Color.byID(eb.ref("module.colorID")).as("color"),
    ])
    .where("module.isActive", "=", true)
    .execute();

  return modules;
};

Nicolas BourdinOP•3mo ago

thanks, but how to make the select query at the root select and not in a "sub item" ?

Solution

Igal•3mo ago

Hey 👋 Try this - https://kyse.link/QI1Q6

await db
  .selectFrom(["event as e", "event as c"])
  .select(projectEvent())
  .execute();

function projectEvent() {
  const eb = expressionBuilder<{ e: Event }, "e">();

  return [
    "e.id",
    "e.title",
    "e.type",
    "e.status",
    "e.banner",
    "e.event_end_date as end_date",
    "e.location",
    "e.duration_in_minute",
    "e.participant_count_limit",
    "e.registration_date_limit",
    "e.provider",
    sql<boolean>`is_full_of_participants(${eb.table("e")})`.as(
      "isFullOfParticipants",
    ),
    sql<string[]>`get_event_sports(${eb.ref("e.id")})`.as("sports"),
  ] satisfies SelectExpression<{ e: Event }, "e">[];
}

await db
  .selectFrom(["event as e", "event as c"])
  .select(projectEvent())
  .execute();

function projectEvent() {
  const eb = expressionBuilder<{ e: Event }, "e">();

  return [
    "e.id",
    "e.title",
    "e.type",
    "e.status",
    "e.banner",
    "e.event_end_date as end_date",
    "e.location",
    "e.duration_in_minute",
    "e.participant_count_limit",
    "e.registration_date_limit",
    "e.provider",
    sql<boolean>`is_full_of_participants(${eb.table("e")})`.as(
      "isFullOfParticipants",
    ),
    sql<string[]>`get_event_sports(${eb.ref("e.id")})`.as("sports"),
  ] satisfies SelectExpression<{ e: Event }, "e">[];
}

Igal•3mo ago

Follows pattern found in https://kysely.dev/docs/recipes/expressions#creating-reusable-helpers

Expressions | Kysely

An Expression is the basic type-safe query building block in Kysely. Pretty much all methods accept expressions as inputs. Most internal classes like SelectQueryBuilder and RawBuilder (the return value of the sql tag) are expressions themselves.

Igal•3mo ago

(bumping in case answer overflow made this thread hard to find)

Nicolas BourdinOP•3mo ago

thanks !

Gaming

Programming

select expression helper