Kysely•2y ago

Extacting where clause generation into dedicated function

I'm building up a query dynamically and need to use some of the logic embedded in where clauses from multiple functions. Therefore, I'd like to encapulate this in a separate function that can be reused but I'm struggling with the type signatures for that function. An example of a query that I'd like to encapsulate is:

query = query.where((qb) => {
  let subQuery = qb.where('activityType', '<>', 'ZSRM');
  subQuery = subQuery.orWhere((qb) =>
    qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', '<=', sevenDaysInFuture),
  );
  subQuery = subQuery.orWhere((qb) => qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', 'is', null));
  return subQuery;
});

query = query.where((qb) => {
  let subQuery = qb.where('activityType', '<>', 'ZSRM');
  subQuery = subQuery.orWhere((qb) =>
    qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', '<=', sevenDaysInFuture),
  );
  subQuery = subQuery.orWhere((qb) => qb.where('activityType', '=', 'ZSRM').where('CONTRACT_DATES_START', 'is', null));
  return subQuery;
});

If I try to extract this into a function using the IDE it gives me:

function buildContactDatesClause(query: SelectQueryBuilder<From<Database, string>, never, {} & AllSelection<From<Database, string>, never>>, sevenDaysInFuture: Date) {
 ...
}

function buildContactDatesClause(query: SelectQueryBuilder<From<Database, string>, never, {} & AllSelection<From<Database, string>, never>>, sevenDaysInFuture: Date) {
 ...
}

The Database and AllSelection interfaces are not exported by kysely. I assume for the Database interface I can use the type of the row, i.e.

function buildContactDatesClause(query: SelectQueryBuilder<From<ActivityTableItem, string>, never, Record<string, never> & AllSelection<From<ActivityTableItem, string>, never>>, sevenDaysInFuture: Date) {
}

function buildContactDatesClause(query: SelectQueryBuilder<From<ActivityTableItem, string>, never, Record<string, never> & AllSelection<From<ActivityTableItem, string>, never>>, sevenDaysInFuture: Date) {
}

But I'm still stuck with what I need to do with AllSelection which makes me think there's probably a better way to solve this problem. Any thoughts, cheers?

4 Replies

Igal•2y ago

Hey 👋 Something like:

function buildContactDatesClause<DB, TB extends keyof DB>(
  wb: WhereInterfaces<DB, TB>
): WhereInterface<DB, TB> {
  // return wb.where ...
}

function buildContactDatesClause<DB, TB extends keyof DB>(
  wb: WhereInterfaces<DB, TB>
): WhereInterface<DB, TB> {
  // return wb.where ...
}

RossOP•2y ago

Hi - thank you. That's closer, I'm now getting TS2345: Argument of type '"activityType"' is not assignable to parameter of type 'ReferenceExpression '. Tried dropping the generics since this will always be working on a specific table like this but the error is the same.

function buildContactDatesClause(
  wb: WhereInterface<ActivityTableItem, keyof ActivityTableItem>,
  sevenDaysInFuture: Date,
): WhereInterface<ActivityTableItem, keyof ActivityTableItem> {
...
}

function buildContactDatesClause(
  wb: WhereInterface<ActivityTableItem, keyof ActivityTableItem>,
  sevenDaysInFuture: Date,
): WhereInterface<ActivityTableItem, keyof ActivityTableItem> {
...
}

It's not clear to me exactly what the generic type parameters DB and TB refer to, so that could be the issue

Igal•2y ago

You got DB generic wrong

type DBWithActivity = Pick<DB, 'activity'>

function buildContactDatesClause<
    WB extends WhereInterface<DBActivity, keyof DBActivity>
>(
  wb: WB,
  sevenDaysInFuture: Date,
): WB {
    return wb.where(
        "activityType", '<>', 'ZSRM'
    ) as any;
}

type DBWithActivity = Pick<DB, 'activity'>

function buildContactDatesClause<
    WB extends WhereInterface<DBActivity, keyof DBActivity>
>(
  wb: WB,
  sevenDaysInFuture: Date,
): WB {
    return wb.where(
        "activityType", '<>', 'ZSRM'
    ) as any;
}

this shouldn't error https://wirekang.github.io/kysely-playground/?p=f&i=-NQ7RnBArbNCPVKKqDBu

RossOP•2y ago

Fantastic, that makes more sense to me, I'll try it out later. Thank you 🙏

Gaming

Programming

Extacting where clause generation into dedicated function