0
# Query Building
1

2
Advanced query building with chainable methods, joins, aggregates, and complex WHERE conditions.
3

4
## Capabilities
5

6
### QueryBuilderBase Class
7

8
Base class providing core query building functionality and transaction support.
9

10
```javascript { .api }
11
/**
12
 * Base QueryBuilder class providing core functionality
13
 */
14
class QueryBuilderBase {
15
  /**
16
   * Modify the query using a function or named modifier
17
   * @param func - Modifier function or name
18
   * @param args - Additional arguments for the modifier
19
   * @returns QueryBuilderBase instance
20
   */
21
  modify(func: string | Function, ...args: any[]): QueryBuilderBase;
22
  
23
  /**
24
   * Set transaction context for the query
25
   * @param trx - Knex transaction object or null
26
   * @returns QueryBuilderBase instance
27
   */
28
  transacting(trx: Transaction | null): QueryBuilderBase;
29
  
30
  /**
31
   * Clone the query builder
32
   * @returns New QueryBuilderBase instance
33
   */
34
  clone(): QueryBuilderBase;
35
  
36
  /**
37
   * Get the Knex query builder instance
38
   * @returns Knex QueryBuilder
39
   */
40
  knex(): Knex;
41
  
42
  /**
43
   * Convert to Knex query
44
   * @returns Knex QueryBuilder
45
   */
46
  toKnexQuery(): Knex.QueryBuilder;
47
  
48
  /**
49
   * Set query context
50
   * @param context - Context object
51
   * @returns QueryBuilderBase instance
52
   */
53
  context(context: object): QueryBuilderBase;
54
  
55
  /**
56
   * Clear query context
57
   * @returns QueryBuilderBase instance
58
   */
59
  clearContext(): QueryBuilderBase;
60
  
61
  /**
62
   * Enable query debugging
63
   * @returns QueryBuilderBase instance
64
   */
65
  debug(): QueryBuilderBase;
66
}
67
```
68

69
### QueryBuilderOperation Class
70

71
Base class for query operations that modify the query builder.
72

73
```javascript { .api }
74
/**
75
 * Base class for all query builder operations
76
 */
77
class QueryBuilderOperation {
78
  /**
79
   * Operation name
80
   */
81
  readonly name: string;
82
  
83
  /**
84
   * Operation constructor
85
   * @param name - Operation name
86
   * @param opt - Operation options
87
   */
88
  constructor(name: string, opt?: any);
89
  
90
  /**
91
   * Check if operation is a subclass of another operation
92
   * @param OperationClass - Operation class to check against
93
   * @returns boolean
94
   */
95
  is(OperationClass: Function): boolean;
96
  
97
  /**
98
   * Called when operation is added to query
99
   * @param builder - Query builder instance
100
   * @param args - Operation arguments
101
   * @returns QueryBuilderOperation instance
102
   */
103
  onAdd(builder: QueryBuilderBase, args: any[]): QueryBuilderOperation;
104
  
105
  /**
106
   * Called before build phase
107
   * @param builder - Query builder instance
108
   * @returns QueryBuilderOperation instance or Promise
109
   */
110
  onBefore1(builder: QueryBuilderBase): QueryBuilderOperation | Promise<QueryBuilderOperation>;
111
  
112
  /**
113
   * Called during build phase
114
   * @param builder - Query builder instance
115
   * @returns QueryBuilderOperation instance or Promise
116
   */
117
  onBuild(builder: QueryBuilderBase): QueryBuilderOperation | Promise<QueryBuilderOperation>;
118
  
119
  /**
120
   * Check if operation can be skipped
121
   * @param builder - Query builder instance
122
   * @returns boolean
123
   */
124
  hasOnBuild(builder: QueryBuilderBase): boolean;
125
  
126
  /**
127
   * Clone the operation
128
   * @param props - Properties to override
129
   * @returns New QueryBuilderOperation instance
130
   */
131
  clone(props?: object): QueryBuilderOperation;
132
}
133
```
134

135
### QueryBuilder Class
136

137
Main query builder class providing chainable query methods for database operations.
138

139
```javascript { .api }
140
/**
141
 * Main QueryBuilder class for constructing database queries
142
 */
143
class QueryBuilder {
144
  // Selection methods
145
  select(...columns: string[]): QueryBuilder;
146
  distinct(...columns: string[]): QueryBuilder;
147
  
148
  // FROM clause
149
  from(table: string | QueryBuilder): QueryBuilder;
150
  
151
  // WHERE conditions
152
  where(column: string, operator: string, value: any): QueryBuilder;
153
  where(column: string, value: any): QueryBuilder;
154
  where(object: object): QueryBuilder;
155
  where(callback: (builder: QueryBuilder) => void): QueryBuilder;
156
  
157
  orWhere(column: string, operator: string, value: any): QueryBuilder;
158
  whereNot(column: string, operator: string, value: any): QueryBuilder;
159
  whereIn(column: string, values: any[]): QueryBuilder;
160
  whereNotIn(column: string, values: any[]): QueryBuilder;
161
  whereNull(column: string): QueryBuilder;
162
  whereNotNull(column: string): QueryBuilder;
163
  whereBetween(column: string, range: [any, any]): QueryBuilder;
164
  whereExists(callback: (builder: QueryBuilder) => void): QueryBuilder;
165
  
166
  // Raw WHERE
167
  whereRaw(sql: string, ...bindings: any[]): QueryBuilder;
168
  orWhereRaw(sql: string, ...bindings: any[]): QueryBuilder;
169
  
170
  // JSON operations
171
  whereJsonObject(column: string, value: any): QueryBuilder;
172
  whereJsonPath(column: string, path: string, operator: string, value: any): QueryBuilder;
173
  whereJsonSupersetOf(column: string, value: any): QueryBuilder;
174
  whereJsonSubsetOf(column: string, value: any): QueryBuilder;
175
  
176
  // JOINS
177
  join(table: string, leftCol: string, rightCol: string): QueryBuilder;
178
  join(table: string, callback: (builder: JoinClause) => void): QueryBuilder;
179
  leftJoin(table: string, leftCol: string, rightCol: string): QueryBuilder;
180
  rightJoin(table: string, leftCol: string, rightCol: string): QueryBuilder;
181
  innerJoin(table: string, leftCol: string, rightCol: string): QueryBuilder;
182
  fullOuterJoin(table: string, leftCol: string, rightCol: string): QueryBuilder;
183
  
184
  // Relation joins
185
  joinRelated(expression: string, options?: JoinRelatedOptions): QueryBuilder;
186
  leftJoinRelated(expression: string, options?: JoinRelatedOptions): QueryBuilder;
187
  
188
  // ORDER BY
189
  orderBy(column: string, direction?: 'asc' | 'desc'): QueryBuilder;
190
  orderBy(columns: OrderByDescriptor[]): QueryBuilder;
191
  orderByRaw(sql: string, ...bindings: any[]): QueryBuilder;
192
  
193
  // GROUP BY
194
  groupBy(...columns: string[]): QueryBuilder;
195
  groupByRaw(sql: string, ...bindings: any[]): QueryBuilder;
196
  
197
  // HAVING
198
  having(column: string, operator: string, value: any): QueryBuilder;
199
  havingRaw(sql: string, ...bindings: any[]): QueryBuilder;
200
  
201
  // Aggregates
202
  count(column?: string): QueryBuilder;
203
  sum(column: string): QueryBuilder;
204
  avg(column: string): QueryBuilder;
205
  min(column: string): QueryBuilder;
206
  max(column: string): QueryBuilder;
207
  
208
  // Pagination
209
  limit(count: number): QueryBuilder;
210
  offset(count: number): QueryBuilder;
211
  page(page: number, pageSize: number): QueryBuilder;
212
  range(start?: number, end?: number): QueryBuilder;
213
  
214
  // CRUD operations
215
  insert(data: object | object[]): QueryBuilder;
216
  insertAndFetch(data: object | object[]): QueryBuilder;
217
  update(data: object): QueryBuilder;
218
  updateAndFetch(data: object): QueryBuilder;
219
  patch(data: object): QueryBuilder;
220
  patchAndFetch(data: object): QueryBuilder;
221
  delete(): QueryBuilder;
222
  del(): QueryBuilder;
223
  
224
  // Upsert operations
225
  onConflict(column?: string | string[]): QueryBuilder;
226
  merge(data?: object | string[]): QueryBuilder;
227
  ignore(): QueryBuilder;
228
  
229
  // Relations
230
  withGraphFetched(expression: string, options?: GraphOptions): QueryBuilder;
231
  withGraphJoined(expression: string, options?: GraphOptions): QueryBuilder;
232
  
233
  // Finders
234
  findById(id: any): QueryBuilder;
235
  findByIds(ids: any[]): QueryBuilder;
236
  findOne(...args: any[]): QueryBuilder;
237
  
238
  // Query execution
239
  execute(): Promise<any>;
240
  then(onFulfilled?: Function, onRejected?: Function): Promise<any>;
241
  
242
  // Query introspection
243
  isFind(): boolean;
244
  isInsert(): boolean;
245
  isUpdate(): boolean;
246
  isDelete(): boolean;
247
  
248
  // Query modification
249
  modify(modifier: string | Function, ...args: any[]): QueryBuilder;
250
  modifiers(modifiers: object): QueryBuilder;
251
  
252
  // Context
253
  context(context: object): QueryBuilder;
254
  clearContext(): QueryBuilder;
255
  
256
  // Utilities
257
  clone(): QueryBuilder;
258
  debug(): QueryBuilder;
259
  timeout(ms: number): QueryBuilder;
260
  returning(columns: string | string[]): QueryBuilder;
261
  
262
  // Knex integration
263
  toKnexQuery(): Knex.QueryBuilder;
264
  knex(): Knex;
265
}
266
```
267

268
**Usage Examples:**
269

270
```javascript
271
const { Model } = require('objection');
272

273
// Basic SELECT query
274
const people = await Person.query()
275
  .select('firstName', 'lastName', 'age')
276
  .where('age', '>', 18)
277
  .orderBy('lastName');
278

279
// Complex WHERE conditions
280
const results = await Person.query()
281
  .where('age', '>=', 18)
282
  .where('active', true)
283
  .orWhere(builder => {
284
    builder.where('vip', true).where('age', '>=', 16);
285
  })
286
  .whereIn('category', ['premium', 'gold'])
287
  .whereNotNull('email');
288

289
// JOIN queries
290
const peopleWithPets = await Person.query()
291
  .join('pets', 'persons.id', 'pets.ownerId')
292
  .select('persons.*', 'pets.name as petName')
293
  .where('pets.species', 'dog');
294

295
// Aggregation
296
const stats = await Person.query()
297
  .groupBy('department')
298
  .select('department')
299
  .count('id as personCount')
300
  .avg('age as averageAge')
301
  .having('personCount', '>', 5);
302
```
303

304
### Selection Methods
305

306
Methods for specifying which columns to select.
307

308
```javascript { .api }
309
/**
310
 * Select specific columns
311
 * @param columns - Column names to select
312
 * @returns QueryBuilder instance
313
 */
314
select(...columns: string[]): QueryBuilder;
315

316
/**
317
 * Select distinct values
318
 * @param columns - Column names for distinct selection
319
 * @returns QueryBuilder instance
320
 */
321
distinct(...columns: string[]): QueryBuilder;
322

323
/**
324
 * Add columns to existing selection
325
 * @param columns - Additional column names
326
 * @returns QueryBuilder instance
327
 */
328
columns(...columns: string[]): QueryBuilder;
329
```
330

331
### WHERE Conditions
332

333
Methods for adding WHERE conditions to queries.
334

335
```javascript { .api }
336
/**
337
 * Add WHERE condition
338
 * @param column - Column name or object with conditions
339
 * @param operator - Comparison operator
340
 * @param value - Value to compare against
341
 * @returns QueryBuilder instance
342
 */
343
where(column: string, operator: string, value: any): QueryBuilder;
344
where(column: string, value: any): QueryBuilder;
345
where(conditions: object): QueryBuilder;
346

347
/**
348
 * Add OR WHERE condition
349
 */
350
orWhere(column: string, operator: string, value: any): QueryBuilder;
351

352
/**
353
 * Add WHERE NOT condition
354
 */
355
whereNot(column: string, operator: string, value: any): QueryBuilder;
356

357
/**
358
 * Add WHERE IN condition
359
 */
360
whereIn(column: string, values: any[]): QueryBuilder;
361

362
/**
363
 * Add WHERE NULL condition
364
 */
365
whereNull(column: string): QueryBuilder;
366

367
/**
368
 * Add WHERE BETWEEN condition
369
 */
370
whereBetween(column: string, range: [any, any]): QueryBuilder;
371

372
/**
373
 * Add raw WHERE condition
374
 */
375
whereRaw(sql: string, ...bindings: any[]): QueryBuilder;
376
```
377

378
### JOIN Operations
379

380
Methods for joining tables.
381

382
```javascript { .api }
383
/**
384
 * Add INNER JOIN
385
 * @param table - Table to join
386
 * @param leftCol - Left column for join condition
387
 * @param rightCol - Right column for join condition
388
 * @returns QueryBuilder instance
389
 */
390
join(table: string, leftCol: string, rightCol: string): QueryBuilder;
391

392
/**
393
 * Add LEFT JOIN
394
 */
395
leftJoin(table: string, leftCol: string, rightCol: string): QueryBuilder;
396

397
/**
398
 * Add RIGHT JOIN
399
 */
400
rightJoin(table: string, leftCol: string, rightCol: string): QueryBuilder;
401

402
/**
403
 * Join related models through relationship definitions
404
 * @param expression - Relation expression string
405
 * @param options - Join options
406
 * @returns QueryBuilder instance
407
 */
408
joinRelated(expression: string, options?: JoinRelatedOptions): QueryBuilder;
409
```
410

411
### CRUD Operations
412

413
Methods for inserting, updating, and deleting data.
414

415
```javascript { .api }
416
/**
417
 * Insert new records
418
 * @param data - Data to insert (object or array of objects)
419
 * @returns QueryBuilder instance
420
 */
421
insert(data: object | object[]): QueryBuilder;
422

423
/**
424
 * Insert and return the inserted records
425
 * @param data - Data to insert
426
 * @returns QueryBuilder instance
427
 */
428
insertAndFetch(data: object | object[]): QueryBuilder;
429

430
/**
431
 * Update existing records
432
 * @param data - Data to update
433
 * @returns QueryBuilder instance
434
 */
435
update(data: object): QueryBuilder;
436

437
/**
438
 * Update and return the updated records
439
 * @param data - Data to update
440
 * @returns QueryBuilder instance
441
 */
442
updateAndFetch(data: object): QueryBuilder;
443

444
/**
445
 * Patch (partial update) existing records
446
 * @param data - Data to patch
447
 * @returns QueryBuilder instance
448
 */
449
patch(data: object): QueryBuilder;
450

451
/**
452
 * Delete records
453
 * @returns QueryBuilder instance
454
 */
455
delete(): QueryBuilder;
456
```
457

458
### Aggregation Methods
459

460
Methods for aggregate functions.
461

462
```javascript { .api }
463
/**
464
 * Count records
465
 * @param column - Column to count (default: '*')
466
 * @returns QueryBuilder instance
467
 */
468
count(column?: string): QueryBuilder;
469

470
/**
471
 * Sum column values
472
 * @param column - Column to sum
473
 * @returns QueryBuilder instance
474
 */
475
sum(column: string): QueryBuilder;
476

477
/**
478
 * Average column values
479
 * @param column - Column to average
480
 * @returns QueryBuilder instance
481
 */
482
avg(column: string): QueryBuilder;
483

484
/**
485
 * Minimum column value
486
 * @param column - Column to find minimum
487
 * @returns QueryBuilder instance
488
 */
489
min(column: string): QueryBuilder;
490

491
/**
492
 * Maximum column value
493
 * @param column - Column to find maximum
494
 * @returns QueryBuilder instance
495
 */
496
max(column: string): QueryBuilder;
497
```
498

499
## Types
500

501
```typescript { .api }
502
interface JoinRelatedOptions {
503
  alias?: string | boolean;
504
  aliases?: Record<string, string>;
505
}
506

507
interface OrderByDescriptor {
508
  column: string;
509
  order?: 'asc' | 'desc';
510
}
511

512
interface GraphOptions {
513
  minimize?: boolean;
514
  separator?: string;
515
  aliases?: Record<string, string>;
516
  joinOperation?: string;
517
  maxBatchSize?: number;
518
}
519

520
interface JoinClause {
521
  on(leftCol: string, rightCol: string): JoinClause;
522
  onIn(leftCol: string, values: any[]): JoinClause;
523
  onNull(column: string): JoinClause;
524
  onNotNull(column: string): JoinClause;
525
  using(columns: string[]): JoinClause;
526
}
527
```