K
Kysely2y ago
Ross

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
Igal2y 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 ...
}
Ross
Ross2y 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
Igal2y 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
Ross
Ross2y ago
Fantastic, that makes more sense to me, I'll try it out later. Thank you 🙏