0
# Find Options & Operators
1

2
Type-safe find options system with powerful query operators for building complex where conditions without raw SQL. This system provides a declarative way to construct database queries using TypeScript objects.
3

4
## Capabilities
5

6
### Find Options Interfaces
7

8
Core interfaces for configuring database queries with type safety.
9

10
```typescript { .api }
11
/**
12
 * Options for finding multiple entities
13
 */
14
interface FindManyOptions<Entity = any> {
15
  /** Columns to select */
16
  select?: FindOptionsSelect<Entity>;
17
  /** WHERE conditions */
18
  where?: FindOptionsWhere<Entity>[] | FindOptionsWhere<Entity>;
19
  /** Relations to load */
20
  relations?: FindOptionsRelations<Entity>;
21
  /** Ordering configuration */
22
  order?: FindOptionsOrder<Entity>;
23
  /** Number of entities to skip */
24
  skip?: number;
25
  /** Maximum number of entities to return */
26
  take?: number;
27
  /** Enable/disable caching */
28
  cache?: boolean | number | { id: any; milliseconds: number };
29
  /** Row locking configuration */
30
  lock?: { mode: "optimistic"; version: number | Date } |
31
         { mode: "pessimistic_read" | "pessimistic_write" | "dirty_read" |
32
                 "pessimistic_partial_write" | "pessimistic_write_or_fail" | "for_no_key_update" };
33
  /** Include soft-deleted entities */
34
  withDeleted?: boolean;
35
  /** Load relation IDs instead of relations */
36
  loadRelationIds?: boolean | FindOptionsRelationIds;
37
  /** Relation loading strategy */
38
  relationLoadStrategy?: "join" | "query";
39
  /** Comment for the query */
40
  comment?: string;
41
}
42

43
/**
44
 * Options for finding a single entity
45
 */
46
interface FindOneOptions<Entity = any> extends Omit<FindManyOptions<Entity>, "skip" | "take"> {}
47

48
/**
49
 * WHERE clause conditions with operator support
50
 */
51
type FindOptionsWhere<Entity> = {
52
  [P in keyof Entity]?: P extends "toString" ? unknown :
53
    Entity[P] extends never ? never :
54
    Entity[P] extends string ? FindOperator<string> | Entity[P] :
55
    Entity[P] extends number ? FindOperator<number> | Entity[P] :
56
    Entity[P] extends boolean ? FindOperator<boolean> | Entity[P] :
57
    Entity[P] extends Buffer ? FindOperator<Entity[P]> | Entity[P] :
58
    FindOperator<Entity[P]> | Entity[P] | FindOptionsWhere<Entity[P]>;
59
};
60

61
/**
62
 * SELECT clause configuration
63
 */
64
type FindOptionsSelect<Entity> = {
65
  [P in keyof Entity]?: P extends "toString" ? false :
66
    Entity[P] extends object ? FindOptionsSelect<Entity[P]> | boolean :
67
    boolean;
68
};
69

70
/**
71
 * Relations loading configuration
72
 */
73
type FindOptionsRelations<Entity> = {
74
  [P in keyof Entity]?: P extends "toString" ? false :
75
    Entity[P] extends object ? FindOptionsRelations<Entity[P]> | boolean :
76
    boolean;
77
};
78

79
/**
80
 * ORDER BY clause configuration
81
 */
82
type FindOptionsOrder<Entity> = {
83
  [P in keyof Entity]?: P extends "toString" ? never :
84
    Entity[P] extends object ? FindOptionsOrder<Entity[P]> :
85
    "ASC" | "DESC" | 1 | -1 | {
86
      direction?: "asc" | "desc" | "ASC" | "DESC";
87
      nulls?: "first" | "last" | "FIRST" | "LAST"
88
    };
89
};
90
```
91

92
**Find Options Examples:**
93

94
```typescript
95
import { FindManyOptions, FindOneOptions } from "typeorm";
96

97
// Basic find with select and where
98
const users = await userRepository.find({
99
  select: {
100
    id: true,
101
    name: true,
102
    email: true,
103
    profile: {
104
      bio: true
105
    }
106
  },
107
  where: {
108
    active: true,
109
    age: MoreThan(18)
110
  },
111
  relations: {
112
    profile: true,
113
    posts: true
114
  },
115
  order: {
116
    createdAt: "DESC"
117
  },
118
  take: 10,
119
  skip: 20
120
});
121

122
// Complex WHERE conditions with multiple options
123
const complexQuery: FindManyOptions<User> = {
124
  where: [
125
    { name: Like("%john%"), active: true },
126
    { email: Like("%@company.com"), status: In(["premium", "pro"]) }
127
  ],
128
  order: {
129
    name: "ASC",
130
    createdAt: { direction: "DESC", nulls: "last" }
131
  },
132
  cache: {
133
    id: "users_active",
134
    milliseconds: 60000
135
  }
136
};
137
```
138

139
### Query Operators
140

141
Powerful operators for building complex WHERE conditions with type safety.
142

143
```typescript { .api }
144
/**
145
 * Equality operator (can be omitted for simple equality)
146
 * @param value - Value to compare
147
 * @returns FindOperator for equality comparison
148
 */
149
function Equal(value: any): FindOperator<any>;
150

151
/**
152
 * Negation operator
153
 * @param value - Value or operator to negate
154
 * @returns FindOperator for negation
155
 */
156
function Not(value: any | FindOperator<any>): FindOperator<any>;
157

158
/**
159
 * Less than operator
160
 * @param value - Value to compare against
161
 * @returns FindOperator for less than comparison
162
 */
163
function LessThan(value: any): FindOperator<any>;
164

165
/**
166
 * Less than or equal operator
167
 * @param value - Value to compare against
168
 * @returns FindOperator for less than or equal comparison
169
 */
170
function LessThanOrEqual(value: any): FindOperator<any>;
171

172
/**
173
 * Greater than operator
174
 * @param value - Value to compare against
175
 * @returns FindOperator for greater than comparison
176
 */
177
function MoreThan(value: any): FindOperator<any>;
178

179
/**
180
 * Greater than or equal operator
181
 * @param value - Value to compare against
182
 * @returns FindOperator for greater than or equal comparison
183
 */
184
function MoreThanOrEqual(value: any): FindOperator<any>;
185

186
/**
187
 * LIKE pattern matching operator
188
 * @param pattern - SQL LIKE pattern with % and _ wildcards
189
 * @returns FindOperator for LIKE comparison
190
 */
191
function Like(pattern: string): FindOperator<string>;
192

193
/**
194
 * Case-insensitive LIKE operator (PostgreSQL)
195
 * @param pattern - SQL LIKE pattern with % and _ wildcards
196
 * @returns FindOperator for case-insensitive LIKE
197
 */
198
function ILike(pattern: string): FindOperator<string>;
199

200
/**
201
 * IN operator for multiple values
202
 * @param values - Array of values to match
203
 * @returns FindOperator for IN comparison
204
 */
205
function In(values: any[]): FindOperator<any>;
206

207
/**
208
 * BETWEEN operator for range queries
209
 * @param from - Start of range
210
 * @param to - End of range
211
 * @returns FindOperator for BETWEEN comparison
212
 */
213
function Between(from: any, to: any): FindOperator<any>;
214

215
/**
216
 * IS NULL operator
217
 * @returns FindOperator for NULL check
218
 */
219
function IsNull(): FindOperator<any>;
220

221
/**
222
 * IS NOT NULL operator
223
 * @returns FindOperator for NOT NULL check
224
 */
225
function IsNotNull(): FindOperator<any>;
226

227
/**
228
 * ANY operator for array/subquery matching
229
 * @param values - Array of values or subquery
230
 * @returns FindOperator for ANY comparison
231
 */
232
function Any(values: any[] | SelectQueryBuilder<any>): FindOperator<any>;
233

234
/**
235
 * Raw SQL operator for custom conditions
236
 * @param condition - Raw SQL condition
237
 * @param parameters - Parameters for the condition
238
 * @returns FindOperator for raw SQL
239
 */
240
function Raw(condition: (columnAlias: string) => string, parameters?: ObjectLiteral): FindOperator<any>;
241
```
242

243
### Advanced Operators
244

245
Specialized operators for specific database types and advanced queries.
246

247
```typescript { .api }
248
/**
249
 * Array contains operator (PostgreSQL)
250
 * @param values - Array that should be contained
251
 * @returns FindOperator for array contains
252
 */
253
function ArrayContains(values: any[]): FindOperator<any>;
254

255
/**
256
 * Array contained by operator (PostgreSQL)
257
 * @param values - Array that should contain the column value
258
 * @returns FindOperator for array contained by
259
 */
260
function ArrayContainedBy(values: any[]): FindOperator<any>;
261

262
/**
263
 * Array overlap operator (PostgreSQL)
264
 * @param values - Array to check for overlap
265
 * @returns FindOperator for array overlap
266
 */
267
function ArrayOverlap(values: any[]): FindOperator<any>;
268

269
/**
270
 * JSON contains operator (PostgreSQL, MySQL)
271
 * @param value - JSON value to check for containment
272
 * @returns FindOperator for JSON contains
273
 */
274
function JsonContains(value: any): FindOperator<any>;
275

276
/**
277
 * Logical AND operator for combining conditions
278
 * @param conditions - Array of conditions to combine with AND
279
 * @returns FindOperator for AND logic
280
 */
281
function And(...conditions: FindOperator<any>[]): FindOperator<any>;
282

283
/**
284
 * Logical OR operator for combining conditions
285
 * @param conditions - Array of conditions to combine with OR
286
 * @returns FindOperator for OR logic
287
 */
288
function Or(...conditions: FindOperator<any>[]): FindOperator<any>;
289
```
290

291
**Operator Examples:**
292

293
```typescript
294
import {
295
  Equal, Not, LessThan, MoreThan, Like, In, Between, IsNull,
296
  Raw, ArrayContains, JsonContains
297
} from "typeorm";
298

299
// Basic operators
300
const users = await userRepository.find({
301
  where: {
302
    age: MoreThan(18),
303
    name: Like("John%"),
304
    status: In(["active", "premium"]),
305
    createdAt: Between(startDate, endDate),
306
    deletedAt: IsNull(),
307
    email: Not(Like("%temp%"))
308
  }
309
});
310

311
// PostgreSQL array operators
312
const postsWithTags = await postRepository.find({
313
  where: {
314
    tags: ArrayContains(["typescript", "tutorial"])
315
  }
316
});
317

318
// JSON operators
319
const usersWithMetadata = await userRepository.find({
320
  where: {
321
    metadata: JsonContains({ premium: true })
322
  }
323
});
324

325
// Raw SQL conditions
326
const customQuery = await userRepository.find({
327
  where: {
328
    name: Raw(alias => `UPPER(${alias}) = UPPER(:name)`, { name: "john" })
329
  }
330
});
331

332
// Complex combinations
333
const complexWhere = await userRepository.find({
334
  where: {
335
    age: And(MoreThan(18), LessThan(65)),
336
    email: Or(Like("%@company.com"), Like("%@enterprise.com"))
337
  }
338
});
339
```
340

341
### Find Options Utilities
342

343
Helper types and interfaces for advanced query configuration.
344

345
```typescript { .api }
346
/**
347
 * Configuration for loading relation IDs
348
 */
349
interface FindOptionsRelationIds {
350
  /** Relations to load IDs for */
351
  relations?: string[];
352
  /** Disable mixing with relation objects */
353
  disableMixedMap?: boolean;
354
}
355

356
/**
357
 * Tree-specific find options
358
 */
359
interface FindTreeOptions {
360
  /** Relations to load in tree entities */
361
  relations?: string[];
362
  /** Maximum tree depth to load */
363
  depth?: number;
364
}
365

366
/**
367
 * Base class for find operators
368
 */
369
abstract class FindOperator<T> {
370
  /** Operator type */
371
  readonly type: FindOperatorType;
372
  /** Operator value */
373
  readonly value: any;
374
  /** Whether to use parameter */
375
  readonly useParameter: boolean;
376
  /** Multiple values flag */
377
  readonly multipleParameters: boolean;
378
  /** SQL generator function */
379
  readonly sql: (columnName: string) => string;
380
  /** Parameter transformation */
381
  readonly parameters: ObjectLiteral;
382
}
383

384
/**
385
 * Supported find operator types
386
 */
387
type FindOperatorType =
388
  | "not" | "lessThan" | "lessThanOrEqual" | "moreThan" | "moreThanOrEqual"
389
  | "equal" | "between" | "in" | "any" | "isNull" | "isNotNull"
390
  | "like" | "ilike" | "raw" | "arrayContains" | "arrayContainedBy"
391
  | "arrayOverlap" | "jsonContains" | "and" | "or";
392
```
393

394
## Usage Patterns
395

396
### Dynamic Query Building
397

398
Build queries dynamically based on conditions:
399

400
```typescript
401
function buildUserQuery(filters: {
402
  name?: string;
403
  minAge?: number;
404
  maxAge?: number;
405
  statuses?: string[];
406
  includeInactive?: boolean;
407
}) {
408
  const where: FindOptionsWhere<User> = {};
409

410
  if (filters.name) {
411
    where.name = Like(`%${filters.name}%`);
412
  }
413

414
  if (filters.minAge !== undefined) {
415
    where.age = MoreThanOrEqual(filters.minAge);
416
  }
417

418
  if (filters.maxAge !== undefined) {
419
    where.age = where.age
420
      ? And(where.age as FindOperator<number>, LessThanOrEqual(filters.maxAge))
421
      : LessThanOrEqual(filters.maxAge);
422
  }
423

424
  if (filters.statuses?.length) {
425
    where.status = In(filters.statuses);
426
  }
427

428
  if (!filters.includeInactive) {
429
    where.active = true;
430
  }
431

432
  return { where };
433
}
434

435
// Usage
436
const query = buildUserQuery({
437
  name: "john",
438
  minAge: 18,
439
  statuses: ["active", "premium"]
440
});
441
const users = await userRepository.find(query);
442
```
443

444
### Pagination and Sorting
445

446
Implement pagination with flexible sorting:
447

448
```typescript
449
interface PaginationOptions<T> {
450
  page: number;
451
  limit: number;
452
  sortBy?: keyof T;
453
  sortOrder?: "ASC" | "DESC";
454
}
455

456
async function paginateUsers(options: PaginationOptions<User>) {
457
  const findOptions: FindManyOptions<User> = {
458
    skip: (options.page - 1) * options.limit,
459
    take: options.limit,
460
  };
461

462
  if (options.sortBy) {
463
    findOptions.order = {
464
      [options.sortBy]: options.sortOrder || "ASC"
465
    } as any;
466
  }
467

468
  const [users, total] = await userRepository.findAndCount(findOptions);
469

470
  return {
471
    data: users,
472
    total,
473
    page: options.page,
474
    pages: Math.ceil(total / options.limit)
475
  };
476
}
477
```