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