diff options
Diffstat (limited to 'lib/filter-columns.ts')
| -rw-r--r-- | lib/filter-columns.ts | 193 |
1 files changed, 193 insertions, 0 deletions
diff --git a/lib/filter-columns.ts b/lib/filter-columns.ts new file mode 100644 index 00000000..4b995925 --- /dev/null +++ b/lib/filter-columns.ts @@ -0,0 +1,193 @@ +import { isEmpty, isNotEmpty } from "@/db/utils" +import type { Filter, JoinOperator } from "@/types/table" +import { addDays, endOfDay, startOfDay } from "date-fns" +import { + and, + eq, + gt, + gte, + ilike, + inArray, + lt, + lte, + ne, + notIlike, + notInArray, + or, + type AnyColumn, + type SQL, + type Table, +} from "drizzle-orm" +import type { PgTable, PgView } from "drizzle-orm/pg-core" + +type TableOrView = PgTable | PgView<any> + +/** + * Construct SQL conditions based on the provided filters for a specific table. + * + * This function takes a table and an array of filters, and returns a SQL + * expression that represents the logical combination of these conditions. The conditions + * are combined using the specified join operator (either 'AND' or 'OR'), which is determined + * by the first filter's joinOperator property. + * + * Each filter can specify various operators (e.g., equality, inequality, + * comparison for numbers and dates, etc.) and the function will generate the appropriate + * SQL expressions based on the filter's type and value. + * + * @param table - The table to apply the filters on. + * @param filters - An array of filters to be applied to the table. + * @param joinOperator - The join operator to use for combining the filters. + * @returns A SQL expression representing the combined filters, or undefined if no valid + * filters are found. + */ + +export function filterColumns<T extends TableOrView>({ + table, + filters, + joinOperator, +}: { + table: T + filters: Filter<T>[] + joinOperator: JoinOperator +}): SQL | undefined { + + const joinFn = joinOperator === "and" ? and : or + + const conditions = filters.map((filter) => { + const column = getColumn(table, filter.id) + + switch (filter.operator) { + case "eq": + if (Array.isArray(filter.value)) { + return inArray(column, filter.value) + } else if ( + column.dataType === "boolean" && + typeof filter.value === "string" + ) { + return eq(column, filter.value === "true") + } else if (filter.type === "date") { + const date = new Date(filter.value) + const start = startOfDay(date) + const end = endOfDay(date) + return and(gte(column, start), lte(column, end)) + } else { + return eq(column, filter.value) + } + case "ne": + if (Array.isArray(filter.value)) { + return notInArray(column, filter.value) + } else if (column.dataType === "boolean") { + return ne(column, filter.value === "true") + } else if (filter.type === "date") { + const date = new Date(filter.value) + const start = startOfDay(date) + const end = endOfDay(date) + return or(lt(column, start), gt(column, end)) + } else { + return ne(column, filter.value) + } + case "iLike": + return filter.type === "text" && typeof filter.value === "string" + ? ilike(column, `%${filter.value}%`) + : undefined + case "notILike": + return filter.type === "text" && typeof filter.value === "string" + ? notIlike(column, `%${filter.value}%`) + : undefined + case "lt": + return filter.type === "number" + ? lt(column, filter.value) + : filter.type === "date" && typeof filter.value === "string" + ? lt(column, endOfDay(new Date(filter.value))) + : undefined + case "lte": + return filter.type === "number" + ? lte(column, filter.value) + : filter.type === "date" && typeof filter.value === "string" + ? lte(column, endOfDay(new Date(filter.value))) + : undefined + case "gt": + return filter.type === "number" + ? gt(column, filter.value) + : filter.type === "date" && typeof filter.value === "string" + ? gt(column, startOfDay(new Date(filter.value))) + : undefined + case "gte": + return filter.type === "number" + ? gte(column, filter.value) + : filter.type === "date" && typeof filter.value === "string" + ? gte(column, startOfDay(new Date(filter.value))) + : undefined + case "isBetween": + return filter.type === "date" && + Array.isArray(filter.value) && + filter.value.length === 2 + ? and( + filter.value[0] + ? gte(column, startOfDay(new Date(filter.value[0]))) + : undefined, + filter.value[1] + ? lte(column, endOfDay(new Date(filter.value[1]))) + : undefined + ) + : undefined + case "isRelativeToToday": + if (filter.type === "date" && typeof filter.value === "string") { + const today = new Date() + const [amount, unit] = filter.value.split(" ") ?? [] + let startDate: Date + let endDate: Date + + if (!amount || !unit) return undefined + + switch (unit) { + case "days": + startDate = startOfDay(addDays(today, parseInt(amount))) + endDate = endOfDay(startDate) + break + case "weeks": + startDate = startOfDay(addDays(today, parseInt(amount) * 7)) + endDate = endOfDay(addDays(startDate, 6)) + break + case "months": + startDate = startOfDay(addDays(today, parseInt(amount) * 30)) + endDate = endOfDay(addDays(startDate, 29)) + break + default: + return undefined + } + + return and(gte(column, startDate), lte(column, endDate)) + } + return undefined + case "isEmpty": + return isEmpty(column) + case "isNotEmpty": + return isNotEmpty(column) + + default: + throw new Error(`Unsupported operator: ${filter.operator}`) + } + }) + + const validConditions = conditions.filter( + (condition) => condition !== undefined + ) + + + return validConditions.length > 0 ? joinFn(...validConditions) : undefined +} + +/** + * Get table column. + * @param table The table to get the column from. + * @param columnKey The key of the column to retrieve from the table. + * @returns The column corresponding to the provided key. + */ + +export function getColumn<T extends TableOrView>( + table: T, + columnKey: keyof T +): AnyColumn { + return table[columnKey] as AnyColumn +}
\ No newline at end of file |