Kysely•2mo ago

Dynamic query parts based on user input

Based on certain user input (REST API), I need to filter and/or sort by provided fields. This could mean that some joins need to be added as well. The fields to filter and sort are not always a 1-1 mapping to a database column, the names can deviate. I came of with a mapping like:

const countryJoin = { type: 'left', sql: 'country AS coun ON coun.id = country_id']
const countryContinentJoin = { type: 'left', sql: 'continent AS coun_cont ON coun_cont.id = cons.id']
    
const queryFields = [
  'name': { col: 'name' },
  // .. more constructor fields
  'country.code': { col: 'coun.alpha2_code', join: [countryJoin] },
  // .. more country fields
  'country.continent.code': { col: 'cont.code', join: [countryJoin, countryContinentJoin] },
  // .. more continent fields
]

const countryJoin = { type: 'left', sql: 'country AS coun ON coun.id = country_id']
const countryContinentJoin = { type: 'left', sql: 'continent AS coun_cont ON coun_cont.id = cons.id']
    
const queryFields = [
  'name': { col: 'name' },
  // .. more constructor fields
  'country.code': { col: 'coun.alpha2_code', join: [countryJoin] },
  // .. more country fields
  'country.continent.code': { col: 'cont.code', join: [countryJoin, countryContinentJoin] },
  // .. more continent fields
]

and this is an example case of how to use it:

let query = db
  .selectFrom('constructor')
  .selectAll('constructor');
  
if (_sort_on_country_code_) { // ignore for now how resolved.
  const field = queryFields['country.code'];
  if (field.join && field.join.length > 1) {
    field.join.forEach((join) => {
      query.leftJoin(join.sql)
    });
   }
   const direction: 'asc' | 'desc' = 'asc'; // ignore for how resolved.
   query.orderBy(sql`${field.col}`, direction);
}
    
const result = await query;

let query = db
  .selectFrom('constructor')
  .selectAll('constructor');
  
if (_sort_on_country_code_) { // ignore for now how resolved.
  const field = queryFields['country.code'];
  if (field.join && field.join.length > 1) {
    field.join.forEach((join) => {
      query.leftJoin(join.sql)
    });
   }
   const direction: 'asc' | 'desc' = 'asc'; // ignore for how resolved.
   query.orderBy(sql`${field.col}`, direction);
}
    
const result = await query;

above is just for illustration, in fact the call will be something like:

let query = db
  .selectFrom('constructor')
  .selectAll('constructor');

this.enhanceQuery(query); // enhanceQuery has access to API args + queryFields config.

const result = await query;

let query = db
  .selectFrom('constructor')
  .selectAll('constructor');

this.enhanceQuery(query); // enhanceQuery has access to API args + queryFields config.

const result = await query;

and this works! But the thing is it depends havily on the sql tag, so none is typesafe unfortuantely. Kylesy itself does a tremendous job about type safety. Is there a way to make that queryFields with the joins type safe? e.g. with helper methods?

3 Replies

Marcel Overdijk•2mo ago

e.g. I did a similar experiment with Drizzle ORM, and there I did something like:

const countryTableAlias = aliasedTable(countryTable, 'coun');
const countryJoin = leftJoin(countryTableAlias, eq(countryTableAlias.id, constructorTable.countryId));

const countryContinentTableAlias = aliasedTable(continentTable, 'coun_cont');
const countryContinentJoin = leftJoin(countryContinentTableAlias, eq(countryContinentTableAlias.id, countryTableAlias.continentId));

queryFields : {
  'name': { col: constructorTable.name },
  'country.code': { col: countryTableAlias.alpha2Code, join: countryJoin },
  'country.continent.code': { col: countryContinentTableAlias.code, join: [countryJoin, countryContinentJoin] },
}

const countryTableAlias = aliasedTable(countryTable, 'coun');
const countryJoin = leftJoin(countryTableAlias, eq(countryTableAlias.id, constructorTable.countryId));

const countryContinentTableAlias = aliasedTable(continentTable, 'coun_cont');
const countryContinentJoin = leftJoin(countryContinentTableAlias, eq(countryContinentTableAlias.id, countryTableAlias.continentId));

queryFields : {
  'name': { col: constructorTable.name },
  'country.code': { col: countryTableAlias.alpha2Code, join: countryJoin },
  'country.continent.code': { col: countryContinentTableAlias.code, join: [countryJoin, countryContinentJoin] },
}

which if fully type safe.

koskimas•2mo ago

Drizzle is just so much better in pretty much everything. I really think you should select it.

Marcel Overdijk•2mo ago

Is that a serious answer, or being sarcastic? Nevertheless, I'm looking to see if this is possible with Kylesy or not...

Gaming

Programming

Dynamic query parts based on user input