Kysely•2y ago

More elegant way to handle nested relations?

Can someone come up with more elegant solution for handling relations than this? I've modified the examples a bit so that I can customize the selected fields and add filters when calling withSomething but it becomes quite verbose and not easy to read. Here's one of my relation helper functions:

export function withLinks<T>(
  eb: ExpressionBuilder<DB, 'banners'>,
  fn: (
    eb: SelectQueryBuilder<DB, 'banners' | 'links', object>,
  ) => Expression<T>,
) {
  const subQuery = eb
    .selectFrom('links')
    .whereRef('links.banner_id', '=', 'banners.id');

  return jsonArrayFrom(fn(subQuery)).as('links');
}

export function withLinks<T>(
  eb: ExpressionBuilder<DB, 'banners'>,
  fn: (
    eb: SelectQueryBuilder<DB, 'banners' | 'links', object>,
  ) => Expression<T>,
) {
  const subQuery = eb
    .selectFrom('links')
    .whereRef('links.banner_id', '=', 'banners.id');

  return jsonArrayFrom(fn(subQuery)).as('links');
}

And this is how the query ends up looking:

db.selectFrom('banners').select((eb) => [
  withLinks(eb, (eb2) =>
    eb2.select((eb3) => [
      'links.id',
      withLinkTranslations(eb3, (eb4) =>
        eb4
          .select('links_translations.content')
          .where('languages_code', '=', 'fi'),
      ),
    ]),
  ),
]);

db.selectFrom('banners').select((eb) => [
  withLinks(eb, (eb2) =>
    eb2.select((eb3) => [
      'links.id',
      withLinkTranslations(eb3, (eb4) =>
        eb4
          .select('links_translations.content')
          .where('languages_code', '=', 'fi'),
      ),
    ]),
  ),
]);

Which looks somewhat ugly given that I need to declare so many nested expression builders. I can't really even give them nice names like banners, links since both eb3 and eb4 are query builders related to links. In this particular instance I could just move the withLinkTranslations all to withTranslations , but I'm still interested if someone has more elegant way to do the case above without losing flexibility.

1 Reply

Igal•2y ago

Hey 👋 You could write it with builder pattern (probably?), but it would require more work

withLinks(eb)
  .select((eb) => [
    'links.id',
    withLinkTranslations(eb)
      .where('languages_code', '=', 'fi')
      .select('links_transactions.content')
      .as('translations')
  ])
  .as('links')

withLinks(eb)
  .select((eb) => [
    'links.id',
    withLinkTranslations(eb)
      .where('languages_code', '=', 'fi')
      .select('links_transactions.content')
      .as('translations')
  ])
  .as('links')

Gaming

Programming

More elegant way to handle nested relations?