KyselyK
Kysely•16mo ago•
4 replies
Marcel Overdijk

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
]

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;


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;


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?
Was this page helpful?