1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
import {
ColumnFiltersState,
SortingState,
PaginationState,
GroupingState
} from "@tanstack/react-table";
import {
SQL,
and,
or,
eq,
ilike,
like,
gt,
lt,
gte,
lte,
inArray,
asc,
desc,
not,
sql
} from "drizzle-orm";
import { PgTable } from "drizzle-orm/pg-core";
/**
* Table State를 Drizzle Query 조건으로 변환하는 유틸리티
*/
export class TableQueryBuilder {
private table: PgTable;
private searchableColumns: string[];
constructor(table: PgTable, searchableColumns: string[] = []) {
this.table = table;
this.searchableColumns = searchableColumns;
}
/**
* Pagination State -> Limit/Offset
*/
getPagination(pagination: PaginationState) {
return {
limit: pagination.pageSize,
offset: pagination.pageIndex * pagination.pageSize,
};
}
/**
* Sorting State -> Order By
*/
getOrderBy(sorting: SortingState) {
if (!sorting.length) return [];
return sorting.map((sort) => {
// 컬럼 이름이 테이블에 존재하는지 확인
const column = this.table[sort.id as keyof typeof this.table];
if (!column) return null;
return sort.desc ? desc(column) : asc(column);
}).filter(Boolean) as SQL[];
}
/**
* Column Filters -> Where Clause
*/
getWhere(columnFilters: ColumnFiltersState, globalFilter?: string) {
const conditions: SQL[] = [];
// 1. Column Filters
for (const filter of columnFilters) {
const column = this.table[filter.id as keyof typeof this.table];
if (!column) continue;
const value = filter.value;
// 값의 타입에 따라 적절한 연산자 선택 (기본적인 예시)
if (Array.isArray(value)) {
// 범위 필터 (예: 날짜, 숫자 범위)
if (value.length === 2) {
const [min, max] = value;
if (min !== null && max !== null) {
conditions.push(and(gte(column, min), lte(column, max))!);
} else if (min !== null) {
conditions.push(gte(column, min)!);
} else if (max !== null) {
conditions.push(lte(column, max)!);
}
}
// 다중 선택 (Select)
else {
conditions.push(inArray(column, value)!);
}
} else if (typeof value === 'string') {
// 텍스트 검색 (Partial Match)
conditions.push(ilike(column, `%${value}%`)!);
} else if (typeof value === 'boolean') {
conditions.push(eq(column, value)!);
} else if (typeof value === 'number') {
conditions.push(eq(column, value)!);
}
}
// 2. Global Filter (검색창)
if (globalFilter && this.searchableColumns.length > 0) {
const searchConditions = this.searchableColumns.map(colName => {
const column = this.table[colName as keyof typeof this.table];
if (!column) return null;
return ilike(column, `%${globalFilter}%`);
}).filter(Boolean) as SQL[];
if (searchConditions.length > 0) {
conditions.push(or(...searchConditions)!);
}
}
return conditions.length > 0 ? and(...conditions) : undefined;
}
/**
* Grouping State -> Group By & Select
* 주의: Group By 사용 시 집계 함수가 필요할 수 있음
*/
getGroupBy(grouping: GroupingState) {
return grouping.map(g => {
const column = this.table[g as keyof typeof this.table];
return column;
}).filter(Boolean);
}
}
|
