FINDarkside Posts - Answer Overflow

FINDarkside

•Created by FINDarkside on 4/24/2023 in #help

How to do join with raw sql snippets?

How can I do this join with kysely? left join videos on videos.id = CAST(highlights_content.item AS INT) AND highlights_content.collection = 'videos' I tried using the provided sql helper but can't really figure out which parts I need to replace with raw SQL for this to be possible.

5 replies

KKysely

•Created by FINDarkside on 4/22/2023 in #help

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.

5 replies

Gaming

Programming