Kysely•5mo ago

select / groupBy combined helper

I realize this usecase looks awful, but our API takes an input of a list of fields to group by, which also means it will return those fields in the result. What I have works, but there's a lot of duplication:

      .$if(isGroupedBy(STOP_ID), (qb) =>
        qb.select('stopid as stopId').groupBy('stopid'),
      )

      .$if(isGroupedBy(STOP_ID), (qb) =>
        qb.select('stopid as stopId').groupBy('stopid'),
      )

but these 3 lines are repeated for each possible parameter a dozen times; and some are more complicated such as extracting multiple fields out of a timestamp. I would prefer something like this but I can't figure out how to get it to typecheck

const selectAndGroupBy = (qb: SelectQueryBuilder<Database, 'person', any>,
  selection: SelectExpression<Database, 'person'>
) => qb.select(selection).groupBy(selection);

const groupBys = ['age']

const person = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .$if(groupBys.includes('age'), (qb) => selectAndGroupBy(qb, 'age'))

const selectAndGroupBy = (qb: SelectQueryBuilder<Database, 'person', any>,
  selection: SelectExpression<Database, 'person'>
) => qb.select(selection).groupBy(selection);

const groupBys = ['age']

const person = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .$if(groupBys.includes('age'), (qb) => selectAndGroupBy(qb, 'age'))

Playground here: https://kyse.link/C4mRA

Solution:

But here's how you'd do it if you really really want to https://kyse.link/4PswR

Jump to solution

4 Replies

chadOP•5mo ago

I have an almost working solution now, with AnyColumn<Database, 'person'> but it doesn't work in the general case, for example some of my columns are RawBuilders

koskimas•5mo ago

It's almost impossible to make this work. If you somehow manage to write the needed types, it will definitely be harder to maintain and understand than just having the copypasta

Solution

koskimas•5mo ago

But here's how you'd do it if you really really want to https://kyse.link/4PswR

chadOP•4mo ago

I have another approach for anyone who finds this. It's a bit of a failed experiment for my use-case but for a simpler one it might work ok: https://kyse.link/gz42N Basically I define a type that can hold a select and a groupBy

export type SelectAndGroupBy<DB, TB extends keyof DB> = {
  select: SelectExpression<DB, TB>;
  groupBy: StringReference<DB, TB> | RawBuilder<never>;
};

export type SelectAndGroupBy<DB, TB extends keyof DB> = {
  select: SelectExpression<DB, TB>;
  groupBy: StringReference<DB, TB> | RawBuilder<never>;
};

and then some helpers that can infer the right types at the call-site, for example:

export const fromColumn = <DB, TB extends keyof DB>(
  col: StringReference<DB, TB>,
  alias: string,
): SelectAndGroupBy<DB, TB> => ({
  select: expressionBuilder<DB, TB>().ref(col).as(alias),
  groupBy: col,
});

export const fromColumn = <DB, TB extends keyof DB>(
  col: StringReference<DB, TB>,
  alias: string,
): SelectAndGroupBy<DB, TB> => ({
  select: expressionBuilder<DB, TB>().ref(col).as(alias),
  groupBy: col,
});

and if you have a list of these you can .map(f => f.select) to get a list you can pass to db.select(...) plus .map(f => f.groupBy) to pass to db.groupBy(...).

Gaming

Programming

select / groupBy combined helper