DT
Drizzle Team•15mo ago
Marcel Overdijk

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
Marcel OverdijkOP•15mo ago
Bump, anyone an idea? @⚡Z.E.U.S⚡ sorry to bother you directly, but maybe you have an idea?
⚡Z.E.U.S⚡
⚡Z.E.U.S⚡•15mo 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
Marcel OverdijkOP•15mo 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!

Did you find this page helpful?