0
# Query Building
1

2
This document covers comprehensive query construction with method chaining, joins, subqueries, and advanced SQL features for building complex database queries.
3

4
## Query Class
5

6
The Query class provides a fluent API for building SQL statements through method chaining.
7

8
```javascript { .api }
9
class Query {
10
  // Core query building
11
  select(...columns: any[]): Query;
12
  from(...tables: any[]): Query;
13
  where(...conditions: any[]): Query;
14
  and(condition: any): Query;
15
  or(condition: any): Query;
16
  
17
  // Joins
18
  join(table: Table): JoinQuery;
19
  leftJoin(table: Table): JoinQuery;
20
  
21
  // Ordering and grouping
22
  order(...criteria: any[]): Query;
23
  group(...columns: any[]): Query;
24
  having(...conditions: any[]): Query;
25
  
26
  // Limiting results
27
  limit(count: number): Query;
28
  offset(count: number): Query;
29
  
30
  // Query modifiers
31
  distinct(): Query;
32
  distinctOn(...columns: any[]): Query;
33
  
34
  // Data modification
35
  insert(data: object | object[]): Query;
36
  replace(data: object | object[]): Query;
37
  update(data: object): Query;
38
  delete(conditions?: any): Query;
39
  
40
  // Advanced features
41
  returning(...columns: any[]): Query;
42
  onDuplicate(action: object): Query;
43
  onConflict(action: ConflictAction): Query;
44
  forUpdate(): Query;
45
  forShare(): Query;
46
  
47
  // Output methods
48
  toQuery(): QueryResult;
49
  toNamedQuery(name: string, dialect?: string): NamedQueryResult;
50
  
51
  // Subquery operations (for subqueries only)
52
  star(): Column;
53
  exists(): BinaryExpression;
54
  notExists(): BinaryExpression;
55
  
56
  // Utility
57
  parameter(value: any): Query;
58
}
59

60
interface QueryResult {
61
  text: string;
62
  values: any[];
63
}
64

65
interface NamedQueryResult extends QueryResult {
66
  name: string;
67
}
68
```
69

70
## SELECT Queries
71

72
### Basic SELECT
73

74
```javascript
75
// Select all columns
76
const allColumnsQuery = user.select().toQuery();
77
console.log(allColumnsQuery.text); // SELECT "user".* FROM "user"
78

79
// Select specific columns
80
const specificQuery = user.select(user.id, user.name).toQuery();
81
console.log(specificQuery.text); // SELECT "user"."id", "user"."name" FROM "user"
82

83
// Select with aliases
84
const aliasQuery = user.select(
85
  user.id,
86
  user.name.as('full_name'),
87
  user.email.as('contact_email')
88
).toQuery();
89
```
90

91
### SELECT with Expressions
92

93
```javascript
94
// Select with computed columns
95
const computedQuery = user.select(
96
  user.id,
97
  user.name,
98
  sql.functions.UPPER(user.name).as('upper_name'),
99
  sql.functions.COUNT(user.id).as('user_count')
100
).toQuery();
101

102
// Select with constants
103
const constantQuery = user.select(
104
  user.id,
105
  user.name,
106
  sql.constant('active').as('status')
107
).toQuery();
108
```
109

110
## WHERE Clauses
111

112
### Basic Conditions
113

114
```javascript { .api }
115
// Single condition
116
query.where(condition: any): Query;
117

118
// Multiple conditions (AND)
119
query.where(condition1, condition2, ...): Query;
120

121
// Explicit AND/OR
122
query.and(condition: any): Query;
123
query.or(condition: any): Query;
124
```
125

126
Usage examples:
127

128
```javascript
129
// Simple WHERE
130
const simpleWhere = user
131
  .select()
132
  .where(user.age.gt(18))
133
  .toQuery();
134

135
// Multiple conditions with AND
136
const multipleAnd = user
137
  .select()
138
  .where(
139
    user.age.gt(18),
140
    user.active.equals(true),
141
    user.email.isNotNull()
142
  )
143
  .toQuery();
144

145
// Complex conditions with AND/OR
146
const complexWhere = user
147
  .select()
148
  .where(user.age.gt(18))
149
  .and(user.active.equals(true))
150
  .or(user.role.equals('admin'))
151
  .toQuery();
152

153
// Grouped conditions
154
const groupedWhere = user
155
  .select()
156
  .where(
157
    user.name.equals('John').and(user.age.gt(25))
158
  )
159
  .or(
160
    user.name.equals('Jane').and(user.age.gt(30))
161
  )
162
  .toQuery();
163
```
164

165
## JOINs
166

167
### Basic Joins
168

169
```javascript { .api }
170
interface JoinQuery {
171
  on(condition: any): Query;
172
  using(...columns: string[]): Query;
173
}
174

175
// Join types
176
table.join(other: Table): JoinQuery;        // INNER JOIN
177
table.leftJoin(other: Table): JoinQuery;    // LEFT JOIN
178
table.rightJoin(other: Table): JoinQuery;   // RIGHT JOIN (dialect-dependent)
179
table.fullJoin(other: Table): JoinQuery;    // FULL JOIN (dialect-dependent)
180
```
181

182
Usage examples:
183

184
```javascript
185
// Inner join
186
const innerJoin = user
187
  .select(user.name, post.title)
188
  .from(user.join(post).on(user.id.equals(post.userId)))
189
  .toQuery();
190

191
// Left join with multiple conditions
192
const leftJoin = user
193
  .select(user.name, post.title)
194
  .from(
195
    user.leftJoin(post).on(
196
      user.id.equals(post.userId).and(post.published.equals(true))
197
    )
198
  )
199
  .toQuery();
200

201
// Multiple joins
202
const multiJoin = user
203
  .select(user.name, post.title, category.name.as('category'))
204
  .from(
205
    user
206
      .join(post).on(user.id.equals(post.userId))
207
      .join(category).on(post.categoryId.equals(category.id))
208
  )
209
  .toQuery();
210

211
// Self join with aliases
212
const friends = user.as('friends');
213
const selfJoin = user
214
  .select(user.name, friends.name.as('friend_name'))
215
  .from(
216
    user
217
      .join(friendship).on(user.id.equals(friendship.userId))
218
      .join(friends).on(friendship.friendId.equals(friends.id))
219
  )
220
  .toQuery();
221
```
222

223
### Auto-Join
224

225
```javascript
226
// Automatic join based on foreign key relationships
227
const autoJoin = user.joinTo(post);
228
```
229

230
## ORDER BY and GROUP BY
231

232
### ORDER BY
233

234
```javascript { .api }
235
query.order(...criteria: any[]): Query;
236

237
// Order criteria can be:
238
// - Column with .asc or .desc
239
// - Column (defaults to ASC)
240
// - Raw SQL string
241
```
242

243
Usage examples:
244

245
```javascript
246
// Single column ordering
247
const singleOrder = user
248
  .select()
249
  .order(user.name.asc)
250
  .toQuery();
251

252
// Multiple column ordering
253
const multiOrder = user
254
  .select()
255
  .order(user.name.asc, user.age.desc, user.id)
256
  .toQuery();
257

258
// Ordering with expressions
259
const expressionOrder = user
260
  .select()
261
  .order(sql.functions.LOWER(user.name).asc)
262
  .toQuery();
263
```
264

265
### GROUP BY and HAVING
266

267
```javascript { .api }
268
query.group(...columns: any[]): Query;
269
query.having(...conditions: any[]): Query;
270
```
271

272
Usage examples:
273

274
```javascript
275
// Group by with aggregation
276
const groupQuery = user
277
  .select(user.department, sql.functions.COUNT(user.id).as('user_count'))
278
  .group(user.department)
279
  .toQuery();
280

281
// Group by with HAVING
282
const havingQuery = user
283
  .select(user.department, sql.functions.COUNT(user.id).as('user_count'))
284
  .group(user.department)
285
  .having(sql.functions.COUNT(user.id).gt(5))
286
  .toQuery();
287

288
// Multiple grouping columns
289
const multiGroupQuery = post
290
  .select(
291
    post.userId,
292
    post.category,
293
    sql.functions.COUNT(post.id).as('post_count')
294
  )
295
  .group(post.userId, post.category)
296
  .toQuery();
297
```
298

299
## DISTINCT and LIMIT/OFFSET
300

301
### DISTINCT
302

303
```javascript { .api }
304
query.distinct(): Query;
305
query.distinctOn(...columns: any[]): Query;  // PostgreSQL specific
306
```
307

308
Usage examples:
309

310
```javascript
311
// Distinct results
312
const distinctQuery = user
313
  .select(user.department)
314
  .distinct()
315
  .toQuery();
316

317
// Distinct on specific columns (PostgreSQL)
318
const distinctOnQuery = user
319
  .select(user.id, user.name, user.email)
320
  .distinctOn(user.email)
321
  .order(user.email, user.id)
322
  .toQuery();
323
```
324

325
### LIMIT and OFFSET
326

327
```javascript { .api }
328
query.limit(count: number): Query;
329
query.offset(count: number): Query;
330
```
331

332
Usage examples:
333

334
```javascript
335
// Pagination
336
const paginatedQuery = user
337
  .select()
338
  .order(user.id)
339
  .limit(20)
340
  .offset(40)  // Skip first 40 records
341
  .toQuery();
342

343
// Top N records
344
const topQuery = user
345
  .select()
346
  .order(user.created_at.desc)
347
  .limit(10)
348
  .toQuery();
349
```
350

351
## Subqueries
352

353
### Creating Subqueries
354

355
```javascript { .api }
356
table.subQuery(alias?: string): SubQuery;
357

358
interface SubQuery {
359
  select(...columns: any[]): SubQuery;
360
  where(...conditions: any[]): SubQuery;
361
  from(...tables: any[]): SubQuery;
362
  group(...columns: any[]): SubQuery;
363
  order(...criteria: any[]): SubQuery;
364
  limit(count: number): SubQuery;
365
  offset(count: number): SubQuery;
366
  exists(): BinaryExpression;
367
  notExists(): BinaryExpression;
368
  star(): Column;
369
}
370
```
371

372
Usage examples:
373

374
```javascript
375
// Subquery in WHERE clause
376
const activeUsers = user.subQuery('active_users')
377
  .select(user.id)
378
  .where(user.active.equals(true));
379

380
const postsWithActiveUsers = post
381
  .select()
382
  .where(post.userId.in(activeUsers))
383
  .toQuery();
384

385
// EXISTS subquery
386
const usersWithPosts = user
387
  .select()
388
  .where(
389
    post.subQuery()
390
      .select(post.id)
391
      .where(post.userId.equals(user.id))
392
      .exists()
393
  )
394
  .toQuery();
395

396
// Subquery in FROM clause
397
const avgAgeByDept = user.subQuery('dept_avg')
398
  .select(
399
    user.department,
400
    sql.functions.AVG(user.age).as('avg_age')
401
  )
402
  .group(user.department);
403

404
const aboveAvgUsers = user
405
  .select(user.name, user.age, user.department)
406
  .from(
407
    user.join(avgAgeByDept).on(user.department.equals(avgAgeByDept.department))
408
  )
409
  .where(user.age.gt(avgAgeByDept.avg_age))
410
  .toQuery();
411
```
412

413
## Advanced Query Features
414

415
### RETURNING Clause
416

417
```javascript { .api }
418
query.returning(...columns: any[]): Query;
419
```
420

421
Usage (PostgreSQL specific):
422

423
```javascript
424
// INSERT with RETURNING
425
const insertReturning = user
426
  .insert({ name: 'John', email: 'john@example.com' })
427
  .returning(user.id, user.created_at)
428
  .toQuery();
429

430
// UPDATE with RETURNING
431
const updateReturning = user
432
  .update({ name: 'John Smith' })
433
  .where(user.id.equals(1))
434
  .returning(user.id, user.name, user.updated_at)
435
  .toQuery();
436
```
437

438
### ON DUPLICATE KEY UPDATE
439

440
```javascript { .api }
441
query.onDuplicate(action: object): Query;  // MySQL specific
442
```
443

444
Usage:
445

446
```javascript
447
const onDuplicateQuery = user
448
  .insert({ id: 1, name: 'John', email: 'john@example.com' })
449
  .onDuplicate({ name: 'John Updated' })
450
  .toQuery();
451
```
452

453
### ON CONFLICT (UPSERT)
454

455
```javascript { .api }
456
query.onConflict(action: ConflictAction): Query;  // PostgreSQL specific
457

458
interface ConflictAction {
459
  target?: string | string[];
460
  action?: 'NOTHING' | object;
461
  where?: any;
462
}
463
```
464

465
Usage:
466

467
```javascript
468
// ON CONFLICT DO NOTHING
469
const conflictNothing = user
470
  .insert({ name: 'John', email: 'john@example.com' })
471
  .onConflict({ target: 'email', action: 'NOTHING' })
472
  .toQuery();
473

474
// ON CONFLICT DO UPDATE
475
const conflictUpdate = user
476
  .insert({ name: 'John', email: 'john@example.com' })
477
  .onConflict({
478
    target: 'email',
479
    action: { name: 'John Updated' }
480
  })
481
  .toQuery();
482
```
483

484
### Row Locking
485

486
```javascript { .api }
487
query.forUpdate(): Query;
488
query.forShare(): Query;  // PostgreSQL specific
489
```
490

491
Usage:
492

493
```javascript
494
// SELECT FOR UPDATE
495
const forUpdateQuery = user
496
  .select()
497
  .where(user.id.equals(1))
498
  .forUpdate()
499
  .toQuery();
500

501
// SELECT FOR SHARE
502
const forShareQuery = user
503
  .select()
504
  .where(user.id.equals(1))
505
  .forShare()
506
  .toQuery();
507
```
508

509
## Query Execution and Output
510

511
### Query Result
512

513
```javascript { .api }
514
interface QueryResult {
515
  text: string;    // SQL query string with parameter placeholders
516
  values: any[];   // Parameter values array
517
}
518

519
interface NamedQueryResult extends QueryResult {
520
  name: string;    // Query name for identification
521
}
522
```
523

524
Usage:
525

526
```javascript
527
// Get parameterized query
528
const query = user
529
  .select()
530
  .where(user.name.equals('John'))
531
  .toQuery();
532

533
console.log(query.text);   // SELECT "user".* FROM "user" WHERE ("user"."name" = $1)
534
console.log(query.values); // ['John']
535

536
// Named query
537
const namedQuery = user
538
  .select()
539
  .toNamedQuery('user.findAll');
540

541
console.log(namedQuery.name); // 'user.findAll'
542
```
543

544
### Parameters
545

546
```javascript { .api }
547
query.parameter(value: any): Query;
548
```
549

550
Usage:
551

552
```javascript
553
// Explicit parameter
554
const paramQuery = user
555
  .select()
556
  .where(user.created_at.gt(sql.parameter(new Date())))
557
  .toQuery();
558
```
559

560
## Query Composition and Reusability
561

562
### Reusable Query Fragments
563

564
```javascript
565
// Define reusable conditions
566
const activeCondition = user.active.equals(true);
567
const adultCondition = user.age.gte(18);
568

569
// Compose queries
570
const activeAdults = user
571
  .select()
572
  .where(activeCondition.and(adultCondition))
573
  .toQuery();
574

575
// Reusable joins
576
const userPostJoin = user.join(post).on(user.id.equals(post.userId));
577

578
const userPostsQuery = user
579
  .select(user.name, post.title)
580
  .from(userPostJoin)
581
  .toQuery();
582
```
583

584
### Query Templates
585

586
```javascript
587
// Create query builder function
588
function buildUserQuery(filters = {}) {
589
  let query = user.select();
590
  
591
  if (filters.active !== undefined) {
592
    query = query.where(user.active.equals(filters.active));
593
  }
594
  
595
  if (filters.minAge) {
596
    query = query.where(user.age.gte(filters.minAge));
597
  }
598
  
599
  if (filters.department) {
600
    query = query.where(user.department.equals(filters.department));
601
  }
602
  
603
  return query;
604
}
605

606
// Use template
607
const adminUsers = buildUserQuery({ 
608
  active: true, 
609
  minAge: 21, 
610
  department: 'admin' 
611
}).toQuery();
612
```