initial commit

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