Base Operations Architecture

The TypeOrmQueryBuilder is the query engine at the bottom of the Clean Architecture stack. It handles everything from basic pagination to complex multi-condition OR queries — all through URL parameters, all automatically validated against the entity schema before any SQL is executed.

You do not call TypeOrmQueryBuilder directly. It is invoked internally by BaseServiceOperations.findPaginated() when you call super.findPaginated(query) from a controller. Your job is to structure the URL correctly.

---

Pagination

?limit=20&page=3
?offset=50&limit=10    # offset takes precedence over page when both are present

---

Sorting

?sort=last_name:asc
?sort=created_at:desc,last_name:asc    # Multi-column sort
?sort=order.created_at:asc             # Nested relation sort (dot notation)

All sort field names are automatically validated against your entity schema. Sorting on a non-existent field returns 400 Bad Request — not a 500 database error.

# ✅ Valid
?sort=created_at:desc
?sort=name:asc,email:desc

# ❌ Invalid — returns 400
?sort=nonExistentField:asc
# Error: Invalid field 'nonExistentField'. Allowed fields are: [id, name, email, ...]

---

Filtering Method 1: s (JSON Search)

Use s for complex, nested queries. The value is a URL-encoded JSON object.

?s={"reference_number": "ORD-001"}                          # Exact match
?s={"total_amount": {">=": 500}}                            # Greater than or equal
?s={"name": {"like": "Acme"}}                               # ILIKE (case-insensitive)
?s={"status": {"in": ["pending", "processing"]}}            # IN list
?s={"created_at": {"between": ["2024-01-01", "2024-12-31"]}} # Date range

Supported operators:

Operator	SQL equivalent
>	MoreThan
>=	MoreThanOrEqual
<	LessThan
<=	LessThanOrEqual
!=	Not
like	ILike (case-insensitive LIKE)
in	In
between	Between

---

Filtering Method 2: filter

Use filter for simple, flat conditions — ideal for building queries from UI filter forms. Format: filter=field||operator||value.

?filter=status||$eq||active
?filter=name||$cont||acme              # ILIKE contains
?filter=created_at||$gte||2024-01-01
?filter=status||$in||pending,processing
?filter=status||$eq||active&filter=category||$eq||premium    # Multiple conditions (AND)
?filter[]=status||$eq||active&filter[]=category||$eq||premium # Array notation

Supported operators: $eq, $ne, $gt, $lt, $gte, $lte, $cont, $starts, $ends, $in, $notin, $isnull, $notnull, $between

Multiple Filters on the Same Field

When multiple filter parameters target the same field, they are combined with AND logic:

?filter=total_amount||$gt||100&filter=total_amount||$lt||500
# Generated: total_amount > 100 AND total_amount < 500

Prior to v1.0, only the last filter on a field was applied. This was a bug that caused silent range query failures. Both conditions are now always applied.

---

The between Operator

With JSON Search (s)

?s={"created_at": {"between": ["2024-01-01", "2024-12-31"]}}
?s={"quantity": {"between": [1, 100]}}
?s={"scheduled_at": {"between": ["2024-01-01T00:00:00", "2024-01-31T23:59:59"]}}

With Filter Syntax

?filter=created_at||$between||2024-01-01,2024-12-31
?filter=quantity||$between||1,100

Automatic Date Expansion

When you provide date-only values (YYYY-MM-DD) for datetime columns, the system automatically expands them to cover the full day:

Input:

?s={"scheduled_at": {"between": ["2024-01-15", "2024-01-20"]}}

Internally expanded to:

{
  "scheduled_at": {
    "between": ["2024-01-15T00:00:00+00:00", "2024-01-20T23:59:59.999+00:00"]
  }
}

This ensures “all records on January 15th” actually includes records throughout that day, not just at midnight.

---

Merging s and filter

Both methods can be combined in a single request — they are merged with AND logic:

?s={"total_amount":{">=":500}}&filter=status||$eq||active&filter=category||$in||premium,enterprise

All three conditions must be satisfied for a record to appear.

---

Timezone Handling

The query builder applies timezone conversions consistently for date queries.

Default Timezone

The system uses the configured application timezone (default: UTC) for interpreting date-only values. All queries are converted to UTC before reaching the database.

Specifying a Timezone

Override with the timezone query parameter (accepts any IANA timezone identifier):

?timezone=Asia/Tokyo&s={"created_at": {"between": ["2024-01-15", "2024-01-20"]}}
?timezone=America/New_York&filter=scheduled_at||$gte||2024-01-15
?timezone=UTC&filter=created_at||$between||2024-01-15,2024-01-20

How Timezone Conversion Works

Example: Date-only value with custom timezone

Request: ?timezone=America/New_York&s={"scheduled_at": "2024-01-15"}

Step 1: Interpret "2024-01-15" in New York time
        → 2024-01-15T00:00:00-05:00 (EST)

Step 2: Expand to full day
        → Start: 2024-01-15T00:00:00-05:00
        → End:   2024-01-15T23:59:59.999-05:00

Step 3: Convert to UTC for database query
        → Start: 2024-01-15T05:00:00Z
        → End:   2024-01-16T04:59:59.999Z

Example: DateTime with explicit timezone in the value

?s={"created_at": "2024-01-15T14:30:00+07:00"}
# The timezone in the value takes precedence → converted to UTC: 2024-01-15T07:30:00Z

Best practices:

• ✅ Always specify timezone when querying date ranges for multi-timezone applications
• ✅ Use YYYY-MM-DD for “entire day” queries (auto-expanded)
• ✅ Use full ISO 8601 for precise datetime queries
• ❌ Do not mix date-only and datetime formats in the same between query

---

Eager Loading Relations

Use relations to load related entities in a single JOIN query — eliminating N+1 problems.

Security: Relation Whitelisting

Each service must declare which relations are allowed. Requests for non-whitelisted relations return 400 Bad Request.

@Injectable()
export class OrdersService extends BaseServiceOperations<Order, CreateOrderDTO, UpdateOrderDTO> {
    protected readonly allowedRelations = [
        'line_items',         // Direct relation
        'customer',           // Direct relation
        'line_items.product', // Nested relation (dot notation)
    ];
}

Usage

?relations=line_items
?relations=line_items,customer
?relations=line_items.product    # Nested relation

Security Error on Unauthorized Relation

GET /orders?relations=internal_audit_log

→ 400 Bad Request
{
  "status": { "code": 400002, "message": "Invalid Parameter" },
  "errors": [{
    "field": "relations",
    "message": "Invalid relation: 'internal_audit_log'. Allowed: [line_items, customer, line_items.product]"
  }]
}

N+1 Problem Solved

// ❌ Without relations: causes N+1 queries
const orders = await this.findAll({ limit: 100 }); // 1 query
for (const order of orders) {
    order.items = await this.itemRepo.find({ order_id: order.id }); // +100 queries
}
// Total: 101 queries

// ✅ With relations parameter: single JOIN
// GET /orders?relations=line_items&limit=100
// Total: 1 query

Best practices:

• ✅ Always whitelist relations in the service layer
• ✅ Combine with fields to reduce payload size
• ✅ Use dot notation for nested relations (line_items.product)
• ❌ Avoid deep nesting beyond 2–3 levels
• ❌ Never expose sensitive relations (e.g., audit_logs, password_resets)

---

Field Selection (Sparse Fieldsets)

Request only the columns you need to reduce payload size and optimize queries.

?fields=id,reference_number,status
?fields=id,name&page=1&limit=10
?fields=id,email,created_at&filter=status||$eq||active&sort=created_at:desc

Fields are validated against the entity schema — requesting a non-existent field returns 400 Bad Request.

Impact: Selecting 5 of 30 fields can reduce payload size by 80% and remove unnecessary column reads from the database.

---

Setting Default Query Parameters

Configure endpoint-level defaults in the controller using @ValidatedQuery with the defaults option:

// Default field selection to keep payloads lean
@Get()
@RequirePermission('order:view')
findAll(
    @ValidatedQuery({
        dto: QueryParamsDTO,
        defaults: {
            fields: 'id,reference_number,status,created_at',
            limit: 20,
            sort: 'created_at:desc',
        },
    })
    query: QueryParamsDTO,
) {
    return super.findPaginated(query);
}

Defaults are applied only when the parameter is absent. Client-provided values always override defaults.

// Default filter for specialized endpoints
@Get('active')
@RequirePermission('order:view')
findActive(
    @ValidatedQuery({
        dto: QueryParamsDTO,
        defaults: {
            fields: 'id,reference_number,status',
            s: JSON.stringify({ status: 'active' }),
        },
    })
    query: QueryParamsDTO,
) {
    return super.findPaginated(query);
}

---

Programmatic Filter Injection

Inject server-side filters that clients cannot override — useful for route parameter scoping:

@Get()
@RequirePermission('order-item:view')
findItemsByOrder(
    @Param('orderId') orderId: string,
    @ValidatedQuery(QueryParamsDTO) query: QueryParamsDTO,
) {
    return super.findPaginated({
        ...query,
        // Preserve all client filters AND append the mandatory order_id filter
        filter: ([] as string[])
            .concat(query.filter ?? [])
            .concat(`order_id||$eq||${orderId}`),
    });
}

Key detail: ([] as string[]).concat(query.filter ?? []) handles filter being undefined, a string, or an array — all safely merged with the injected condition.

---

OR Queries (Disjunctive Conditions)

The or parameter applies OR logic — useful when records should match any of multiple conditions.

Basic OR

?or=is_public||$eq||true&or=created_by||$eq||user-123
# WHERE (is_public = true) OR (created_by = 'user-123')

Combining AND + OR

When filter and or are combined, AND conditions are applied to each OR branch:

?filter=status||$eq||active&or=is_public||$eq||true&or=created_by||$eq||user-123

Generated SQL:

WHERE (status = 'active' AND is_public = true)
   OR (status = 'active' AND created_by = 'user-123')
AND is_deleted = false

Advanced: Multiple AND + Multiple OR

?filter=status||$eq||active&filter=category||$eq||premium&or=is_public||$eq||true&or=owner_id||$eq||user-123&or=shared_with||$cont||user-123

WHERE (status = 'active' AND category = 'premium' AND is_public = true)
   OR (status = 'active' AND category = 'premium' AND owner_id = 'user-123')
   OR (status = 'active' AND category = 'premium' AND shared_with LIKE '%user-123%')
AND is_deleted = false

All filter operators supported by filter work identically in or.

---

Excluding Records (exclude_ids)

Skip specific records by ID — useful for “load more” pagination and deduplication:

?exclude_ids=id-1,id-2,id-3
?filter=status||$eq||active&exclude_ids=already-seen-id-1,already-seen-id-2

Generated SQL: WHERE id NOT IN ('id-1', 'id-2', 'id-3')

Use cases: “Load more” without re-fetching already-rendered items; skip processed records in a worker; implement “don’t show again” UI behavior.

---

Count-Only Mode (get_count_only)

Return just the pagination metadata — no data — for UI initialization:

?filter=status||$eq||active&get_count_only=true

{
  "data": [],
  "pagination": {
    "page": 1,
    "page_size": 10,
    "total": 245,
    "total_records": 1200,
    "total_pages": 25
  }
}

Use cases: Show “245 results found” before loading data; pre-calculate pagination controls; verify filter matches before fetching.

---

Ignore Pagination Limits (ignore_limit)

Return all matching records — useful for dropdown lists and reference data:

?ignore_limit=true
?ignore_limit=true&fields=id,name    # Combine with fields for efficiency
?filter=is_active||$eq||true&ignore_limit=true

⚠️ Use with care on large datasets. Always combine with fields to minimize payload.

---

Comprehensive Query Example

Combining all parameters in a single request:

GET /api/v1/orders?
  relations=line_items,customer&
  fields=id,reference_number,status,total_amount,created_at&
  filter=status||$eq||active&
  s={"total_amount":{">=":500}}&
  sort=created_at:desc,reference_number:asc&
  timezone=UTC&
  page=1&
  limit=20

What this does:

1. relations — Eager-loads line items and customer in a single JOIN (no N+1)
2. fields — Returns 5 columns instead of 30 (~83% smaller payload)
3. filter — Filters for active orders only
4. s — Adds high-value filter (total ≥ 500) via JSON search
5. sort — Orders by newest first, then by reference number
6. timezone — Interprets all date values in UTC
7. page + limit — Page 1 with 20 results

Generated SQL:

SELECT
  o.id, o.reference_number, o.status, o.total_amount, o.created_at,
  l.*, c.*
FROM orders o
LEFT JOIN line_items l ON l.order_id = o.id
LEFT JOIN customers c ON c.id = o.customer_id
WHERE
  o.status = 'active'
  AND o.total_amount >= 500
  AND o.is_deleted = false
ORDER BY o.created_at DESC, o.reference_number ASC
LIMIT 20 OFFSET 0;

Performance impact:

Scenario	Queries	Payload
Without optimizations (N+1, all fields)	41 queries	~150 KB
With relations + field selection	1 query	~25 KB (83% reduction)

---

Related Documentation

• Clean Architecture — Controller → Service → Repository layers and base class rules
• Entity & DTO Principle — Entity and DTO standards
• API Response & Error Handling — Exception hierarchy