feat: add lib query builder

masb0ymas · masb0ymas · commit 1f04805bdc8c · 2025-04-01T18:48:23.000+07:00
diff --git a/src/lib/query-builder/filtered.ts b/src/lib/query-builder/filtered.ts
@@ -0,0 +1,50 @@
+import { ObjectLiteral } from 'typeorm'
+import { validate } from '../validate'
+import { ApplyFilterParams, QueryFilters } from './types'
+
+export function applyFilter<T extends ObjectLiteral>({
+  query,
+  filters,
+  model,
+  options,
+}: ApplyFilterParams<T>) {
+  let filtered: QueryFilters[] = []
+
+  if (Array.isArray(filters)) {
+    filtered = filters
+  } else {
+    filtered = JSON.parse(filters) as QueryFilters[]
+  }
+
+  if (filtered.length > 0) {
+    for (let i = 0; i < filtered.length; i += 1) {
+      const item = filtered[i]
+
+      const check_uuid = validate.uuid(item.value)
+      const check_numeric = validate.number(item.value)
+      const check_boolean = validate.boolean(item.value)
+      const expect_number_uuid_boolean = !check_numeric && !check_uuid && !check_boolean
+
+      const postgres_driver = options?.type === 'postgres'
+      const mysql_driver = ['mysql', 'mariadb'].includes(String(options?.type))
+
+      if (check_uuid || check_numeric || check_boolean) {
+        query.andWhere(`${model}.${item.id} = :${item.id}`, {
+          [`${item.id}`]: `${item.value}`,
+        })
+      }
+
+      if (mysql_driver && expect_number_uuid_boolean) {
+        query.andWhere(`${model}.${item.id} LIKE :${item.id}`, {
+          [`${item.id}`]: `%${item.value}%`,
+        })
+      }
+
+      if (postgres_driver && expect_number_uuid_boolean) {
+        query.andWhere(`${model}.${item.id} ILIKE :${item.id}`, {
+          [`${item.id}`]: `%${item.value}%`,
+        })
+      }
+    }
+  }
+}
diff --git a/src/lib/query-builder/index.ts b/src/lib/query-builder/index.ts
@@ -0,0 +1,35 @@
+import _ from 'lodash'
+import { ObjectLiteral } from 'typeorm'
+import { validate } from '../validate'
+import { applyFilter } from './filtered'
+import { applyPagination } from './pagination'
+import { applySort } from './sorted'
+import { QueryBuilderParams } from './types'
+
+export function QueryBuilder<T extends ObjectLiteral>({ params, options }: QueryBuilderParams<T>) {
+  const { query, model, reqQuery, options: opt } = params
+
+  const queryPage = _.get(reqQuery, 'page', 1)
+  const queryPageSize = _.get(reqQuery, 'pageSize', 10)
+  const queryFilters = _.get(reqQuery, 'filtered', [])
+  const querySorts = _.get(reqQuery, 'sorted', [])
+
+  const limit = _.get(opt, 'limit', 100)
+  const orderKey = _.get(opt, 'orderKey', 'created_at')
+
+  applyFilter({ query, filters: queryFilters, model, options })
+
+  applySort({
+    query,
+    sorts: querySorts,
+    model,
+    orderKey,
+  })
+
+  applyPagination({
+    query,
+    page: validate.number(queryPage),
+    pageSize: validate.number(queryPageSize),
+    limit,
+  })
+}
diff --git a/src/lib/query-builder/pagination.ts b/src/lib/query-builder/pagination.ts
@@ -0,0 +1,31 @@
+import { ObjectLiteral } from 'typeorm'
+import { validate } from '../validate'
+import { ApplyPaginationParams, CalculatePageSizeParams } from './types'
+
+function _calculatePageSize({ pageSize, limit }: CalculatePageSizeParams) {
+  const min = 10
+  const parsePageSize = validate.number(pageSize)
+
+  if (parsePageSize > 0) {
+    return Math.min(parsePageSize, limit)
+  }
+
+  return min
+}
+
+export function applyPagination<T extends ObjectLiteral>({
+  query,
+  page,
+  pageSize,
+  limit = 100,
+}: ApplyPaginationParams<T>) {
+  const parsePage = validate.number(page) || 1
+  let parsePageSize = _calculatePageSize({ pageSize, limit })
+
+  if (parsePageSize <= 0) {
+    parsePageSize = 10
+  }
+
+  query.skip((parsePage - 1) * parsePageSize)
+  query.take(parsePageSize)
+}
diff --git a/src/lib/query-builder/sorted.ts b/src/lib/query-builder/sorted.ts
@@ -0,0 +1,26 @@
+import { ObjectLiteral } from 'typeorm'
+import { ApplySortParams, QuerySorts } from './types'
+
+export function applySort<T extends ObjectLiteral>({
+  query,
+  sorts,
+  model,
+  orderKey,
+}: ApplySortParams<T>) {
+  let sorted: QuerySorts[] = []
+
+  if (Array.isArray(sorts)) {
+    sorted = sorts
+  } else {
+    sorted = JSON.parse(sorts) as QuerySorts[]
+  }
+
+  if (sorted.length > 0) {
+    for (let i = 0; i < sorted.length; i += 1) {
+      const item = sorted[i]
+      query.addOrderBy(`${model}.${item.sort}`, item.order)
+    }
+  } else {
+    query.orderBy(`${model}.${orderKey || 'created_at'}`, 'DESC')
+  }
+}
diff --git a/src/lib/query-builder/types.ts b/src/lib/query-builder/types.ts
@@ -0,0 +1,62 @@
+import { DataSourceOptions, ObjectLiteral, SelectQueryBuilder } from 'typeorm'
+
+export type CalculatePageSizeParams = {
+  pageSize: number
+  limit: number
+}
+
+export type ApplyPaginationParams<T extends ObjectLiteral> = {
+  query: SelectQueryBuilder<T>
+  page: number
+  pageSize: number
+  limit?: number
+}
+
+export type ApplyFilterParams<T extends ObjectLiteral> = {
+  query: SelectQueryBuilder<T>
+  filters: any
+  model: string
+  options?: DataSourceOptions
+}
+
+export type QueryFilters = {
+  id: string
+  value: string
+}
+
+export type ApplySortParams<T extends ObjectLiteral> = {
+  query: SelectQueryBuilder<T>
+  sorts: any
+  model: string
+  orderKey?: string
+}
+
+export type QuerySorts = {
+  sort: string
+  order: 'ASC' | 'DESC'
+}
+
+type RequestQuery = {
+  filtered?: QueryFilters[]
+  sorted?: QuerySorts[]
+  page?: string | number
+  pageSize?: string | number
+  [key: string]: any
+}
+
+type QueryOptions = {
+  limit?: number
+  orderKey?: string
+}
+
+export type QueryParams<T extends ObjectLiteral> = {
+  model: string
+  query: SelectQueryBuilder<T>
+  reqQuery: RequestQuery
+  options?: QueryOptions
+}
+
+export type QueryBuilderParams<T extends ObjectLiteral> = {
+  params: QueryParams<T>
+  options?: DataSourceOptions
+}
