0
# SQL Conditions
1

2
Fluent SQL condition builder for complex queries and filtering with type-safe parameter binding. The SqlCondition class provides a chainable API for constructing SQL WHERE clauses with automatic parameter handling and column validation.
3

4
## Capabilities
5

6
### SqlCondition Class
7

8
Core SQL condition builder with fluent API for constructing complex WHERE clauses.
9

10
```typescript { .api }
11
/**
12
 * Fluent SQL condition builder for complex queries
13
 */
14
class SqlCondition {
15
  /** Predefined condition for "TRUE=TRUE" to match all records */
16
  static readonly ALL: SqlCondition;
17

18
  /**
19
   * Add column reference to the condition
20
   * @param columnName - Name of the database column
21
   * @returns SqlCondition instance for chaining
22
   */
23
  column(columnName: string): SqlCondition;
24

25
  /**
26
   * Add less-than operator (<)
27
   * @returns SqlCondition instance for chaining
28
   */
29
  smaller(): SqlCondition;
30

31
  /**
32
   * Add greater-than operator (>)
33
   * @returns SqlCondition instance for chaining
34
   */
35
  greater(): SqlCondition;
36

37
  /**
38
   * Add equals comparison (=)
39
   * @param value - Value to compare against (will be parameterized)
40
   * @returns SqlCondition instance for chaining
41
   */
42
  equals(value: SqlParameter): SqlCondition;
43

44
  /**
45
   * Add AND condition with nested logic
46
   * @param cb - Callback function to build nested condition
47
   * @returns SqlCondition instance for chaining
48
   */
49
  and(cb: (condition: SqlCondition) => SqlCondition): SqlCondition;
50

51
  /**
52
   * Add parameter value to the condition
53
   * @param value - Parameter value or Date object
54
   * @returns SqlCondition instance for chaining
55
   */
56
  param(value: SqlParameter | Date): SqlCondition;
57

58
  /**
59
   * Add current timestamp function (UNIX_TIMESTAMP(NOW(3))*1000)
60
   */
61
  now(): void;
62

63
  /**
64
   * Build final SQL string with parameter substitution
65
   * @param params - Array of parameters for substitution
66
   * @returns Generated SQL WHERE clause string
67
   */
68
  build(params: Array<SqlParameter> | null): string;
69

70
  /**
71
   * Validate column references against entity class
72
   * @param classRef - Entity class to validate columns against
73
   */
74
  checkColumns(classRef: any): void;
75
}
76

77
/** Type for SQL parameters */
78
type SqlParameter = string | number;
79
```
80

81
**Usage Examples:**
82

83
```typescript
84
import { SqlCondition, load } from "hibernatets";
85

86
// Simple equality condition
87
const activeUsers = await load(User,
88
  new SqlCondition().column("active").equals(true)
89
);
90

91
// Greater than condition
92
const adultUsers = await load(User,
93
  new SqlCondition().column("age").greater().param(18)
94
);
95

96
// Less than condition with date
97
const recentPosts = await load(Post,
98
  new SqlCondition()
99
    .column("createdAt")
100
    .greater()
101
    .param(new Date("2023-01-01"))
102
);
103

104
// Using predefined ALL condition
105
const allUsers = await load(User, SqlCondition.ALL);
106

107
// Current timestamp comparison
108
const condition = new SqlCondition()
109
  .column("lastLoginAt")
110
  .greater();
111
condition.now();
112
const currentActiveUsers = await load(User, condition);
113
```
114

115
### Complex Conditions
116

117
Building complex SQL conditions with AND logic and nested expressions.
118

119
```typescript { .api }
120
/**
121
 * Add AND condition with nested logic
122
 * @param cb - Callback function that receives a new SqlCondition instance
123
 * @returns SqlCondition instance for chaining
124
 */
125
and(cb: (condition: SqlCondition) => SqlCondition): SqlCondition;
126
```
127

128
**Usage Examples:**
129

130
```typescript
131
import { SqlCondition, load } from "hibernatets";
132

133
// AND condition with multiple criteria
134
const activeAdultUsers = await load(User,
135
  new SqlCondition()
136
    .column("active")
137
    .equals(true)
138
    .and(c => c.column("age").greater().param(18))
139
);
140

141
// Complex nested conditions
142
const eligibleUsers = await load(User,
143
  new SqlCondition()
144
    .column("verified")
145
    .equals(true)
146
    .and(c => c
147
      .column("createdAt")
148
      .smaller()
149
      .param(new Date("2023-12-31"))
150
    )
151
    .and(c => c
152
      .column("loginCount")
153
      .greater()
154
      .param(5)
155
    )
156
);
157

158
// Multiple AND conditions
159
const premiumActiveUsers = await load(User,
160
  new SqlCondition()
161
    .column("subscriptionType")
162
    .equals("premium")
163
    .and(c => c.column("active").equals(true))
164
    .and(c => c.column("paymentStatus").equals("current"))
165
    .and(c => c
166
      .column("lastPaymentDate")
167
      .greater()
168
      .param(new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
169
    )
170
);
171
```
172

173
### Parameter Handling
174

175
Safe parameter binding and value handling in SQL conditions.
176

177
**Usage Examples:**
178

179
```typescript
180
import { SqlCondition, load } from "hibernatets";
181

182
// String parameters
183
const usersByName = await load(User,
184
  new SqlCondition().column("name").equals("Alice")
185
);
186

187
// Numeric parameters
188
const userById = await load(User,
189
  new SqlCondition().column("id").equals(123)
190
);
191

192
// Date parameters
193
const recentUsers = await load(User,
194
  new SqlCondition()
195
    .column("registrationDate")
196
    .greater()
197
    .param(new Date("2023-01-01"))
198
);
199

200
// Dynamic parameters
201
function findUsersByAgeRange(minAge: number, maxAge: number) {
202
  return load(User,
203
    new SqlCondition()
204
      .column("age")
205
      .greater()
206
      .param(minAge)
207
      .and(c => c.column("age").smaller().param(maxAge))
208
  );
209
}
210

211
// Boolean parameters
212
const verifiedUsers = await load(User,
213
  new SqlCondition().column("isVerified").equals(true)
214
);
215
```
216

217
### Column Validation
218

219
Validate column references against entity definitions to catch errors early.
220

221
**Usage Examples:**
222

223
```typescript
224
import { SqlCondition } from "hibernatets";
225

226
// Validate columns before using in queries
227
const condition = new SqlCondition()
228
  .column("name")
229
  .equals("Alice")
230
  .and(c => c.column("age").greater().param(21));
231

232
// Check if columns exist in User entity
233
condition.checkColumns(User);
234

235
// This will throw an error if columns don't exist
236
try {
237
  const invalidCondition = new SqlCondition()
238
    .column("nonExistentColumn")
239
    .equals("value");
240

241
  invalidCondition.checkColumns(User);
242
} catch (error) {
243
  console.error("Invalid column reference:", error.message);
244
}
245
```
246

247
### Advanced Query Patterns
248

249
Complex querying patterns and best practices for SQL condition building.
250

251
```typescript
252
import { SqlCondition, load } from "hibernatets";
253

254
// Search functionality
255
async function searchUsers(searchTerm: string) {
256
  return await load(User,
257
    new SqlCondition()
258
      .column("name")
259
      .equals(`%${searchTerm}%`)
260
      .and(c => c.column("active").equals(true))
261
  );
262
}
263

264
// Date range queries
265
async function getUsersInDateRange(startDate: Date, endDate: Date) {
266
  return await load(User,
267
    new SqlCondition()
268
      .column("createdAt")
269
      .greater()
270
      .param(startDate)
271
      .and(c => c
272
        .column("createdAt")
273
        .smaller()
274
        .param(endDate)
275
      )
276
  );
277
}
278

279
// Status-based filtering
280
async function getUsersByStatus(statuses: string[]) {
281
  // For multiple values, you might need to build OR conditions
282
  // This example shows one approach using individual AND conditions
283
  let condition = new SqlCondition().column("status").equals(statuses[0]);
284

285
  // Note: This creates AND logic, for OR logic you'd need database-specific SQL
286
  for (let i = 1; i < statuses.length; i++) {
287
    condition = condition.and(c => c.column("status").equals(statuses[i]));
288
  }
289

290
  return await load(User, condition);
291
}
292

293
// Conditional query building
294
function buildUserFilter(options: {
295
  active?: boolean;
296
  minAge?: number;
297
  maxAge?: number;
298
  verified?: boolean;
299
  searchTerm?: string;
300
}) {
301
  let condition = SqlCondition.ALL;
302

303
  if (options.active !== undefined) {
304
    condition = new SqlCondition()
305
      .column("active")
306
      .equals(options.active);
307
  }
308

309
  if (options.minAge !== undefined) {
310
    condition = condition.and(c => c
311
      .column("age")
312
      .greater()
313
      .param(options.minAge!)
314
    );
315
  }
316

317
  if (options.maxAge !== undefined) {
318
    condition = condition.and(c => c
319
      .column("age")
320
      .smaller()
321
      .param(options.maxAge!)
322
    );
323
  }
324

325
  if (options.verified !== undefined) {
326
    condition = condition.and(c => c
327
      .column("verified")
328
      .equals(options.verified!)
329
    );
330
  }
331

332
  if (options.searchTerm) {
333
    condition = condition.and(c => c
334
      .column("name")
335
      .equals(`%${options.searchTerm}%`)
336
    );
337
  }
338

339
  return condition;
340
}
341

342
// Usage of conditional filter
343
const filteredUsers = await load(User, buildUserFilter({
344
  active: true,
345
  minAge: 18,
346
  verified: true,
347
  searchTerm: "John"
348
}));
349
```
350

351
### Integration with Deep Loading
352

353
Using SQL conditions with relationship deep loading for complex data retrieval.
354

355
```typescript
356
import { SqlCondition, load } from "hibernatets";
357

358
// Deep loading with filtered relationships
359
const usersWithRecentPosts = await load(User, SqlCondition.ALL, [], {
360
  deep: {
361
    posts: new SqlCondition()
362
      .column("published")
363
      .equals(true)
364
      .and(c => c
365
        .column("createdAt")
366
        .greater()
367
        .param(new Date(Date.now() - 7 * 24 * 60 * 60 * 1000))
368
      )
369
  }
370
});
371

372
// Multiple relationship filters
373
const activeUsersWithData = await load(User,
374
  new SqlCondition().column("active").equals(true),
375
  [],
376
  {
377
    deep: {
378
      posts: new SqlCondition()
379
        .column("status")
380
        .equals("published"),
381
      profile: new SqlCondition()
382
        .column("visibility")
383
        .equals("public"),
384
      comments: new SqlCondition()
385
        .column("approved")
386
        .equals(true)
387
        .and(c => c
388
          .column("createdAt")
389
          .greater()
390
          .param(new Date(Date.now() - 30 * 24 * 60 * 60 * 1000))
391
        )
392
    }
393
  }
394
);
395
```
396

397
### SQL Generation and Debugging
398

399
Understanding how SQL conditions are built and debugging query generation.
400

401
```typescript
402
import { SqlCondition } from "hibernatets";
403

404
// Build and inspect generated SQL
405
const condition = new SqlCondition()
406
  .column("active")
407
  .equals(true)
408
  .and(c => c.column("age").greater().param(21));
409

410
// Generate SQL string (usually done internally)
411
const sql = condition.build([true, 21]);
412
console.log("Generated SQL:", sql);
413
// Output: "active = ? AND age > ?"
414

415
// Complex condition SQL generation
416
const complexCondition = new SqlCondition()
417
  .column("status")
418
  .equals("active")
419
  .and(c => {
420
    c.column("lastLogin").greater();
421
    c.now();
422
    return c;
423
  })
424
  .and(c => c
425
    .column("credits")
426
    .greater()
427
    .param(100)
428
  );
429

430
// This would generate something like:
431
// "status = ? AND lastLogin > NOW() AND credits > ?"
432
```