-
Notifications
You must be signed in to change notification settings - Fork 25
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Row-wise comparison operations #215
Comments
This is my current workaround. Not sure if the Postgres optimizer deals with this well. // Postgres has row-wise comparison syntax:
// (a, b, c) > ($1, $2, $3)
// But Mammoth doesn't yet support it. So this function generates the long version:
// (a > $1) OR (a = $1 AND (b > $2 OR (b = $2 AND c > $3)))
// Not sure if the Postgres optimizer deals with this well.
export function rowWiseCompare<Columns extends Expression<any, true, string>[]>(
comparisonFn: ComparisonFn,
columns: [...Columns],
values: ColumnValuesT<Columns>,
): DefaultExpression<boolean> {
// Build it from right to left.
let i = columns.length - 1;
let expr = comparisonFn(columns[i], values[i]);
while (i > 0) {
i--;
expr = comparisonFn(columns[i], values[i]).or(columns[i].eq(values[i]).and(expr));
}
return expr;
}
type ColumnValuesT<Columns extends unknown[]> = {[I in keyof Columns]: Columns[I] extends Expression<infer T, true, string> ? T : never};
export type ComparisonFn = <T>(
expr: Expression<T, boolean, string>,
value: T | Expression<T, boolean, any>,
) => DefaultExpression<boolean>;
export const gt: ComparisonFn = (e, v) => e.gt(v);
export const lt: ComparisonFn = (e, v) => e.lt(v);
export const gte: ComparisonFn = (e, v) => e.gte(v);
export const lte: ComparisonFn = (e, v) => e.lte(v); |
You can use raw nowadays to insert custom SQL. So you can also use this to do a row-wise compare: db
.select(star())
.from(db.foo)
.where(raw`(name, value)`.gt(raw`(${'Test'}, ${123})`)); It's not the best but at least it's better than the whole dance to create a custom rowWiseCompare. Maybe at some point we can have a specific |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Is there a way to do write a row-wise comparison, e.g.
This is useful when paging through a table with a cursor that is based on multiple columns. There's a way to do it without row-wise comparisons, but it's a little more complex to write and read.
If there were a way to insert a custom SQL fragment into a larger Mammoth query, that would be a pretty good workaround.
The text was updated successfully, but these errors were encountered: