Dynamically adding joins based on relation?

I have this setup:

export const circuitTable = sqliteTable('circuit', {
  id: text('id').notNull().primaryKey(),
  name: text('name').notNull(),
  placeName: text('place_name').notNull(),
  countryId: text('country_id')
    .notNull()
    .references(() => countryTable.id),
});

export const circuitRelations = relations(circuitTable, ({ one }) => ({
  country: one(countryTable, {
    fields: [circuitTable.countryId],
    references: [countryTable.id],
  }),
}));

export const circuitTable = sqliteTable('circuit', {
  id: text('id').notNull().primaryKey(),
  name: text('name').notNull(),
  placeName: text('place_name').notNull(),
  countryId: text('country_id')
    .notNull()
    .references(() => countryTable.id),
});

export const circuitRelations = relations(circuitTable, ({ one }) => ({
  country: one(countryTable, {
    fields: [circuitTable.countryId],
    references: [countryTable.id],
  }),
}));

I can easily refer to columns within the circuit table like circuitTable.name or circuitTable.placeName. Can I also refer to relations to dynamically add a join? E.g.:

const baseQuery = ctx.mydb
  .select({
    ...getTableColumns(circuitTable),
  })
  .from(circuitTable)
  .$dynamic();

const baseQuery = ctx.mydb
  .select({
    ...getTableColumns(circuitTable),
  })
  .from(circuitTable)
  .$dynamic();

and then based on user input I want to add some joins dynamically. E.g. the user input could be sort on placeName or country.name, and in the latter case I want to add the country join. As I have this in many places I would like to do something smart like:

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: circuitRelations.country, // not possible
  }
};

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: circuitRelations.country, // not possible
  }
};

based on such configs I wanted to apply the sort based on user input sort field. Would something like that be possible?

3 Replies

Marcel Overdijk•2mo ago

Bump, anyone an idea? @⚡Z.E.U.S⚡ sorry to bother you directly, but maybe you have an idea?

⚡Z.E.U.S⚡•2mo ago

Maybe something like this?

function withCountries<T extends PgSelect>(qb: T) {
  return qb.leftJoin(countryTable, eq(circuitTable.countryId, countryTable.id));
}

const sortFields: Record<string, Column> = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': countryTable.name,
};

const field = 'countryName';

let baseQuery = ctx.mydb
  .select()
  .from(circuitTable)
  .$dynamic();

const sortField = sortFields[field];
if (sortField.table === countryTable) {
  baseQuery = withCountries(baseQuery);
}

...

function withCountries<T extends PgSelect>(qb: T) {
  return qb.leftJoin(countryTable, eq(circuitTable.countryId, countryTable.id));
}

const sortFields: Record<string, Column> = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': countryTable.name,
};

const field = 'countryName';

let baseQuery = ctx.mydb
  .select()
  .from(circuitTable)
  .$dynamic();

const sortField = sortFields[field];
if (sortField.table === countryTable) {
  baseQuery = withCountries(baseQuery);
}

...

Marcel Overdijk•2mo ago

thank you. it would not be possible to - how must I explain - without the qb to define the leftJoin. e.g.

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: leftJoin(countryTable, eq(circuitTable.countryId, countryTable.id))
  }
};

const sortFields = {
  'id': circuitTable.id,
  'name': circuitTable.name,
  'placeName': circuitTable.placeName,
  'countryId': circuitTable.countryId,
  'countryName': {
    column: countryTable.name,
    join: leftJoin(countryTable, eq(circuitTable.countryId, countryTable.id))
  }
};

and the apply that join field toward the qb without the if? if not , your solution already helps me a lot!

Gaming

Programming

Dynamically adding joins based on relation?