directus

1
import { NUMERIC_TYPES } from '@directus/constants';
2
import { InvalidQueryError } from '@directus/errors';
3
import type {
4
	Aggregate,
5
	ClientFilterOperator,
6
	FieldFunction,
7
	FieldOverview,
8
	Filter,
9
	NumericType,
10
	Query,
11
	Relation,
12
	SchemaOverview,
13
	Type,
14
} from '@directus/types';
15
import { getFilterOperatorsForType, getFunctionsForType, getOutputTypeForFunction, isIn } from '@directus/utils';
16
import type { Knex } from 'knex';
17
import { clone, isPlainObject } from 'lodash-es';
18
import { customAlphabet } from 'nanoid/non-secure';
19
import { getHelpers } from '../database/helpers/index.js';
20
import type { AliasMap } from './get-column-path.js';
21
import { getColumnPath } from './get-column-path.js';
22
import { getColumn } from './get-column.js';
23
import { getRelationInfo } from './get-relation-info.js';
24
import { isValidUuid } from './is-valid-uuid.js';
25
import { parseFilterKey } from './parse-filter-key.js';
26
import { parseNumericString } from './parse-numeric-string.js';
27

28
export const generateAlias = customAlphabet('abcdefghijklmnopqrstuvwxyz', 5);
29

30
/**
31
 * Apply the Query to a given Knex query builder instance
32
 */
33
export default function applyQuery(
34
	knex: Knex,
35
	collection: string,
36
	dbQuery: Knex.QueryBuilder,
37
	query: Query,
38
	schema: SchemaOverview,
39
	options?: { aliasMap?: AliasMap; isInnerQuery?: boolean; hasMultiRelationalSort?: boolean | undefined },
40
) {
41
	const aliasMap: AliasMap = options?.aliasMap ?? Object.create(null);
42
	let hasJoins = false;
43
	let hasMultiRelationalFilter = false;
44

45
	applyLimit(knex, dbQuery, query.limit);
46

47
	if (query.offset) {
48
		applyOffset(knex, dbQuery, query.offset);
49
	}
50

51
	if (query.page && query.limit && query.limit !== -1) {
52
		applyOffset(knex, dbQuery, query.limit * (query.page - 1));
53
	}
54

55
	if (query.sort && !options?.isInnerQuery && !options?.hasMultiRelationalSort) {
56
		const sortResult = applySort(knex, schema, dbQuery, query, collection, aliasMap);
57

58
		if (!hasJoins) {
59
			hasJoins = sortResult.hasJoins;
60
		}
61
	}
62

63
	if (query.search) {
64
		applySearch(knex, schema, dbQuery, query.search, collection);
65
	}
66

67
	if (query.group) {
68
		dbQuery.groupBy(query.group.map((column) => getColumn(knex, collection, column, false, schema)));
69
	}
70

71
	if (query.filter) {
72
		const filterResult = applyFilter(knex, schema, dbQuery, query.filter, collection, aliasMap);
73

74
		if (!hasJoins) {
75
			hasJoins = filterResult.hasJoins;
76
		}
77

78
		hasMultiRelationalFilter = filterResult.hasMultiRelationalFilter;
79
	}
80

81
	if (query.aggregate) {
82
		applyAggregate(schema, dbQuery, query.aggregate, collection, hasJoins);
83
	}
84

85
	return { query: dbQuery, hasJoins, hasMultiRelationalFilter };
86
}
87

88
/**
89
 * Apply a given filter object to the Knex QueryBuilder instance.
90
 *
91
 * Relational nested filters, like the following example:
92
 *
93
 * ```json
94
 * // Fetch pages that have articles written by Rijk
95
 *
96
 * {
97
 *   "articles": {
98
 *     "author": {
99
 *       "name": {
100
 *         "_eq": "Rijk"
101
 *       }
102
 *     }
103
 *   }
104
 * }
105
 * ```
106
 *
107
 * are handled by joining the nested tables, and using a where statement on the top level on the
108
 * nested field through the join. This allows us to filter the top level items based on nested data.
109
 * The where on the root is done with a subquery to prevent duplicates, any nested joins are done
110
 * with aliases to prevent naming conflicts.
111
 *
112
 * The output SQL for the above would look something like:
113
 *
114
 * ```sql
115
 * SELECT *
116
 * FROM pages
117
 * WHERE
118
 *   pages.id in (
119
 *     SELECT articles.page_id AS page_id
120
 *     FROM articles
121
 *     LEFT JOIN authors AS xviqp ON articles.author = xviqp.id
122
 *     WHERE xviqp.name = 'Rijk'
123
 *   )
124
 * ```
125
 */
126

127
type AddJoinProps = {
128
	path: string[];
129
	collection: string;
130
	aliasMap: AliasMap;
131
	relations: Relation[];
132
	rootQuery: Knex.QueryBuilder;
133
	schema: SchemaOverview;
134
	knex: Knex;
135
};
136

137
function addJoin({ path, collection, aliasMap, rootQuery, schema, relations, knex }: AddJoinProps) {
138
	let hasMultiRelational = false;
139
	let isJoinAdded = false;
140

141
	path = clone(path);
142
	followRelation(path);
143

144
	return { hasMultiRelational, isJoinAdded };
145

146
	function followRelation(pathParts: string[], parentCollection: string = collection, parentFields?: string) {
147
		/**
148
		 * For A2M fields, the path can contain an optional collection scope <field>:<scope>
149
		 */
150
		const pathRoot = pathParts[0]!.split(':')[0]!;
151

152
		const { relation, relationType } = getRelationInfo(relations, parentCollection, pathRoot);
153

154
		if (!relation) {
155
			return;
156
		}
157

158
		const existingAlias = parentFields
159
			? aliasMap[`${parentFields}.${pathParts[0]}`]?.alias
160
			: aliasMap[pathParts[0]!]?.alias;
161

162
		if (!existingAlias) {
163
			const alias = generateAlias();
164
			const aliasKey = parentFields ? `${parentFields}.${pathParts[0]}` : pathParts[0]!;
165
			const aliasedParentCollection = aliasMap[parentFields ?? '']?.alias || parentCollection;
166

167
			aliasMap[aliasKey] = { alias, collection: '' };
168

169
			if (relationType === 'm2o') {
170
				rootQuery.leftJoin(
171
					{ [alias]: relation.related_collection! },
172
					`${aliasedParentCollection}.${relation.field}`,
173
					`${alias}.${schema.collections[relation.related_collection!]!.primary}`,
174
				);
175

176
				aliasMap[aliasKey]!.collection = relation.related_collection!;
177

178
				isJoinAdded = true;
179
			} else if (relationType === 'a2o') {
180
				const pathScope = pathParts[0]!.split(':')[1];
181

182
				if (!pathScope) {
183
					throw new InvalidQueryError({
184
						reason: `You have to provide a collection scope when sorting or filtering on a many-to-any item`,
185
					});
186
				}
187

188
				rootQuery.leftJoin({ [alias]: pathScope }, (joinClause) => {
189
					joinClause
190
						.onVal(`${aliasedParentCollection}.${relation.meta!.one_collection_field!}`, '=', pathScope)
191
						.andOn(
192
							`${aliasedParentCollection}.${relation.field}`,
193
							'=',
194
							knex.raw(
195
								getHelpers(knex).schema.castA2oPrimaryKey(),
196
								`${alias}.${schema.collections[pathScope]!.primary}`,
197
							),
198
						);
199
				});
200

201
				aliasMap[aliasKey]!.collection = pathScope;
202

203
				isJoinAdded = true;
204
			} else if (relationType === 'o2a') {
205
				rootQuery.leftJoin({ [alias]: relation.collection }, (joinClause) => {
206
					joinClause
207
						.onVal(`${alias}.${relation.meta!.one_collection_field!}`, '=', parentCollection)
208
						.andOn(
209
							`${alias}.${relation.field}`,
210
							'=',
211
							knex.raw(
212
								getHelpers(knex).schema.castA2oPrimaryKey(),
213
								`${aliasedParentCollection}.${schema.collections[parentCollection]!.primary}`,
214
							),
215
						);
216
				});
217

218
				aliasMap[aliasKey]!.collection = relation.collection;
219

220
				hasMultiRelational = true;
221
				isJoinAdded = true;
222
			} else if (relationType === 'o2m') {
223
				rootQuery.leftJoin(
224
					{ [alias]: relation.collection },
225
					`${aliasedParentCollection}.${schema.collections[relation.related_collection!]!.primary}`,
226
					`${alias}.${relation.field}`,
227
				);
228

229
				aliasMap[aliasKey]!.collection = relation.collection;
230

231
				hasMultiRelational = true;
232
				isJoinAdded = true;
233
			}
234
		}
235

236
		let parent: string;
237

238
		if (relationType === 'm2o') {
239
			parent = relation.related_collection!;
240
		} else if (relationType === 'a2o') {
241
			const pathScope = pathParts[0]!.split(':')[1];
242

243
			if (!pathScope) {
244
				throw new InvalidQueryError({
245
					reason: `You have to provide a collection scope when sorting or filtering on a many-to-any item`,
246
				});
247
			}
248

249
			parent = pathScope;
250
		} else {
251
			parent = relation.collection;
252
		}
253

254
		if (pathParts.length > 1) {
255
			followRelation(pathParts.slice(1), parent, `${parentFields ? parentFields + '.' : ''}${pathParts[0]}`);
256
		}
257
	}
258
}
259

260
export type ColumnSortRecord = { order: 'asc' | 'desc'; column: string };
261

262
export function applySort(
263
	knex: Knex,
264
	schema: SchemaOverview,
265
	rootQuery: Knex.QueryBuilder,
266
	query: Query,
267
	collection: string,
268
	aliasMap: AliasMap,
269
	returnRecords = false,
270
) {
271
	const rootSort = query.sort!;
272
	const aggregate = query?.aggregate;
273
	const relations: Relation[] = schema.relations;
274
	let hasJoins = false;
275
	let hasMultiRelationalSort = false;
276

277
	const sortRecords = rootSort.map((sortField) => {
278
		const column: string[] = sortField.split('.');
279
		let order: 'asc' | 'desc' = 'asc';
280

281
		if (sortField.startsWith('-')) {
282
			order = 'desc';
283
		}
284

285
		if (column[0]!.startsWith('-')) {
286
			column[0] = column[0]!.substring(1);
287
		}
288

289
		// Is the column name one of the aggregate functions used in the query if there is any?
290
		if (Object.keys(aggregate ?? {}).includes(column[0]!)) {
291
			// If so, return the column name without the order prefix
292
			const operation = column[0]!;
293

294
			// Get the field for the aggregate function
295
			const field = column[1]!;
296

297
			// If the operation is countAll there is no field.
298
			if (operation === 'countAll') {
299
				return {
300
					order,
301
					column: 'countAll',
302
				};
303
			}
304

305
			// If the operation is a root count there is no field.
306
			if (operation === 'count' && (field === '*' || !field)) {
307
				return {
308
					order,
309
					column: 'count',
310
				};
311
			}
312

313
			// Return the column name with the operation and field name
314
			return {
315
				order,
316
				column: returnRecords ? column[0] : `${operation}->${field}`,
317
			};
318
		}
319

320
		if (column.length === 1) {
321
			const pathRoot = column[0]!.split(':')[0]!;
322
			const { relation, relationType } = getRelationInfo(relations, collection, pathRoot);
323

324
			if (!relation || ['m2o', 'a2o'].includes(relationType ?? '')) {
325
				return {
326
					order,
327
					column: returnRecords ? column[0] : (getColumn(knex, collection, column[0]!, false, schema) as any),
328
				};
329
			}
330
		}
331

332
		const { hasMultiRelational, isJoinAdded } = addJoin({
333
			path: column,
334
			collection,
335
			aliasMap,
336
			rootQuery,
337
			schema,
338
			relations,
339
			knex,
340
		});
341

342
		const { columnPath } = getColumnPath({
343
			path: column,
344
			collection,
345
			aliasMap,
346
			relations,
347
			schema,
348
		});
349

350
		const [alias, field] = columnPath.split('.');
351

352
		if (!hasJoins) {
353
			hasJoins = isJoinAdded;
354
		}
355

356
		if (!hasMultiRelationalSort) {
357
			hasMultiRelationalSort = hasMultiRelational;
358
		}
359

360
		return {
361
			order,
362
			column: returnRecords ? columnPath : (getColumn(knex, alias!, field!, false, schema) as any),
363
		};
364
	});
365

366
	if (returnRecords) return { sortRecords, hasJoins, hasMultiRelationalSort };
367

368
	// Clears the order if any, eg: from MSSQL offset
369
	rootQuery.clear('order');
370

371
	rootQuery.orderBy(sortRecords);
372

373
	return { hasJoins, hasMultiRelationalSort };
374
}
375

376
export function applyLimit(knex: Knex, rootQuery: Knex.QueryBuilder, limit: any) {
377
	if (typeof limit === 'number') {
378
		getHelpers(knex).schema.applyLimit(rootQuery, limit);
379
	}
380
}
381

382
export function applyOffset(knex: Knex, rootQuery: Knex.QueryBuilder, offset: any) {
383
	if (typeof offset === 'number') {
384
		getHelpers(knex).schema.applyOffset(rootQuery, offset);
385
	}
386
}
387

388
export function applyFilter(
389
	knex: Knex,
390
	schema: SchemaOverview,
391
	rootQuery: Knex.QueryBuilder,
392
	rootFilter: Filter,
393
	collection: string,
394
	aliasMap: AliasMap,
395
) {
396
	const helpers = getHelpers(knex);
397
	const relations: Relation[] = schema.relations;
398
	let hasJoins = false;
399
	let hasMultiRelationalFilter = false;
400

401
	addJoins(rootQuery, rootFilter, collection);
402
	addWhereClauses(knex, rootQuery, rootFilter, collection);
403

404
	return { query: rootQuery, hasJoins, hasMultiRelationalFilter };
405

406
	function addJoins(dbQuery: Knex.QueryBuilder, filter: Filter, collection: string) {
407
		for (const [key, value] of Object.entries(filter)) {
408
			if (key === '_or' || key === '_and') {
409
				// If the _or array contains an empty object (full permissions), we should short-circuit and ignore all other
410
				// permission checks, as {} already matches full permissions.
411
				if (key === '_or' && value.some((subFilter: Record<string, any>) => Object.keys(subFilter).length === 0)) {
412
					continue;
413
				}
414

415
				value.forEach((subFilter: Record<string, any>) => {
416
					addJoins(dbQuery, subFilter, collection);
417
				});
418

419
				continue;
420
			}
421

422
			const filterPath = getFilterPath(key, value);
423

424
			if (
425
				filterPath.length > 1 ||
426
				(!(key.includes('(') && key.includes(')')) && schema.collections[collection]?.fields[key]?.type === 'alias')
427
			) {
428
				const { hasMultiRelational, isJoinAdded } = addJoin({
429
					path: filterPath,
430
					collection,
431
					knex,
432
					schema,
433
					relations,
434
					rootQuery,
435
					aliasMap,
436
				});
437

438
				if (!hasJoins) {
439
					hasJoins = isJoinAdded;
440
				}
441

442
				if (!hasMultiRelationalFilter) {
443
					hasMultiRelationalFilter = hasMultiRelational;
444
				}
445
			}
446
		}
447
	}
448

449
	function addWhereClauses(
450
		knex: Knex,
451
		dbQuery: Knex.QueryBuilder,
452
		filter: Filter,
453
		collection: string,
454
		logical: 'and' | 'or' = 'and',
455
	) {
456
		for (const [key, value] of Object.entries(filter)) {
457
			if (key === '_or' || key === '_and') {
458
				// If the _or array contains an empty object (full permissions), we should short-circuit and ignore all other
459
				// permission checks, as {} already matches full permissions.
460
				if (key === '_or' && value.some((subFilter: Record<string, any>) => Object.keys(subFilter).length === 0)) {
461
					continue;
462
				}
463

464
				/** @NOTE this callback function isn't called until Knex runs the query */
465
				dbQuery[logical].where((subQuery) => {
466
					value.forEach((subFilter: Record<string, any>) => {
467
						addWhereClauses(knex, subQuery, subFilter, collection, key === '_and' ? 'and' : 'or');
468
					});
469
				});
470

471
				continue;
472
			}
473

474
			const filterPath = getFilterPath(key, value);
475

476
			/**
477
			 * For A2M fields, the path can contain an optional collection scope <field>:<scope>
478
			 */
479
			const pathRoot = filterPath[0]!.split(':')[0]!;
480

481
			const { relation, relationType } = getRelationInfo(relations, collection, pathRoot);
482

483
			const operation = getOperation(key, value);
484

485
			if (!operation) continue;
486

487
			const { operator: filterOperator, value: filterValue } = operation;
488

489
			if (
490
				filterPath.length > 1 ||
491
				(!(key.includes('(') && key.includes(')')) && schema.collections[collection]?.fields[key]?.type === 'alias')
492
			) {
493
				if (!relation) continue;
494

495
				if (relationType === 'o2m' || relationType === 'o2a') {
496
					let pkField: Knex.Raw<any> | string = `${collection}.${
497
						schema.collections[relation!.related_collection!]!.primary
498
					}`;
499

500
					if (relationType === 'o2a') {
501
						pkField = knex.raw(getHelpers(knex).schema.castA2oPrimaryKey(), [pkField]);
502
					}
503

504
					const subQueryBuilder = (filter: Filter) => (subQueryKnex: Knex.QueryBuilder<any, unknown[]>) => {
505
						const field = relation!.field;
506
						const collection = relation!.collection;
507
						const column = `${collection}.${field}`;
508

509
						subQueryKnex
510
							.select({ [field]: column })
511
							.from(collection)
512
							.whereNotNull(column);
513

514
						applyQuery(knex, relation!.collection, subQueryKnex, { filter }, schema);
515
					};
516

517
					const childKey = Object.keys(value)?.[0];
518

519
					if (childKey === '_none') {
520
						dbQuery[logical].whereNotIn(pkField as string, subQueryBuilder(Object.values(value)[0] as Filter));
521
						continue;
522
					} else if (childKey === '_some') {
523
						dbQuery[logical].whereIn(pkField as string, subQueryBuilder(Object.values(value)[0] as Filter));
524
						continue;
525
					}
526
				}
527

528
				if (filterPath.includes('_none') || filterPath.includes('_some')) {
529
					throw new InvalidQueryError({
530
						reason: `"${
531
							filterPath.includes('_none') ? '_none' : '_some'
532
						}" can only be used with top level relational alias field`,
533
					});
534
				}
535

536
				const { columnPath, targetCollection, addNestedPkField } = getColumnPath({
537
					path: filterPath,
538
					collection,
539
					relations,
540
					aliasMap,
541
					schema,
542
				});
543

544
				if (addNestedPkField) {
545
					filterPath.push(addNestedPkField);
546
				}
547

548
				if (!columnPath) continue;
549

550
				const { type, special } = getFilterType(
551
					schema.collections[targetCollection]!.fields,
552
					filterPath.at(-1)!,
553
					targetCollection,
554
				)!;
555

556
				validateFilterOperator(type, filterOperator, special);
557

558
				applyFilterToQuery(columnPath, filterOperator, filterValue, logical, targetCollection);
559
			} else {
560
				const { type, special } = getFilterType(schema.collections[collection]!.fields, filterPath[0]!, collection)!;
561

562
				validateFilterOperator(type, filterOperator, special);
563

564
				const aliasedCollection = aliasMap['']?.alias || collection;
565

566
				applyFilterToQuery(`${aliasedCollection}.${filterPath[0]}`, filterOperator, filterValue, logical, collection);
567
			}
568
		}
569

570
		function getFilterType(fields: Record<string, FieldOverview>, key: string, collection = 'unknown') {
571
			const { fieldName, functionName } = parseFilterKey(key);
572

573
			const field = fields[fieldName];
574

575
			if (!field) {
576
				throw new InvalidQueryError({ reason: `Invalid filter key "${key}" on "${collection}"` });
577
			}
578

579
			const { type } = field;
580

581
			if (functionName) {
582
				const availableFunctions: string[] = getFunctionsForType(type);
583

584
				if (!availableFunctions.includes(functionName)) {
585
					throw new InvalidQueryError({ reason: `Invalid filter key "${key}" on "${collection}"` });
586
				}
587

588
				const functionType = getOutputTypeForFunction(functionName as FieldFunction);
589

590
				return { type: functionType };
591
			}
592

593
			return { type, special: field.special };
594
		}
595

596
		function validateFilterOperator(type: Type, filterOperator: string, special?: string[]) {
597
			if (filterOperator.startsWith('_')) {
598
				filterOperator = filterOperator.slice(1);
599
			}
600

601
			if (!getFilterOperatorsForType(type).includes(filterOperator as ClientFilterOperator)) {
602
				throw new InvalidQueryError({
603
					reason: `"${type}" field type does not contain the "_${filterOperator}" filter operator`,
604
				});
605
			}
606

607
			if (
608
				special?.includes('conceal') &&
609
				!getFilterOperatorsForType('hash').includes(filterOperator as ClientFilterOperator)
610
			) {
611
				throw new InvalidQueryError({
612
					reason: `Field with "conceal" special does not allow the "_${filterOperator}" filter operator`,
613
				});
614
			}
615
		}
616

617
		function applyFilterToQuery(
618
			key: string,
619
			operator: string,
620
			compareValue: any,
621
			logical: 'and' | 'or' = 'and',
622
			originalCollectionName?: string,
623
		) {
624
			const [table, column] = key.split('.');
625

626
			// Is processed through Knex.Raw, so should be safe to string-inject into these where queries
627
			const selectionRaw = getColumn(knex, table!, column!, false, schema, { originalCollectionName }) as any;
628

629
			// Knex supports "raw" in the columnName parameter, but isn't typed as such. Too bad..
630
			// See https://github.com/knex/knex/issues/4518 @TODO remove as any once knex is updated
631

632
			// These operators don't rely on a value, and can thus be used without one (eg `?filter[field][_null]`)
633
			if ((operator === '_null' && compareValue !== false) || (operator === '_nnull' && compareValue === false)) {
634
				dbQuery[logical].whereNull(selectionRaw);
635
			}
636

637
			if ((operator === '_nnull' && compareValue !== false) || (operator === '_null' && compareValue === false)) {
638
				dbQuery[logical].whereNotNull(selectionRaw);
639
			}
640

641
			if ((operator === '_empty' && compareValue !== false) || (operator === '_nempty' && compareValue === false)) {
642
				dbQuery[logical].andWhere((query) => {
643
					query.whereNull(key).orWhere(key, '=', '');
644
				});
645
			}
646

647
			if ((operator === '_nempty' && compareValue !== false) || (operator === '_empty' && compareValue === false)) {
648
				dbQuery[logical].andWhere((query) => {
649
					query.whereNotNull(key).andWhere(key, '!=', '');
650
				});
651
			}
652

653
			// The following fields however, require a value to be run. If no value is passed, we
654
			// ignore them. This allows easier use in GraphQL, where you wouldn't be able to
655
			// conditionally build out your filter structure (#4471)
656
			if (compareValue === undefined) return;
657

658
			if (Array.isArray(compareValue)) {
659
				// Tip: when using a `[Type]` type in GraphQL, but don't provide the variable, it'll be
660
				// reported as [undefined].
661
				// We need to remove any undefined values, as they are useless
662
				compareValue = compareValue.filter((val) => val !== undefined);
663
			}
664

665
			// Cast filter value (compareValue) based on function used
666
			if (column!.includes('(') && column!.includes(')')) {
667
				const functionName = column!.split('(')[0] as FieldFunction;
668
				const type = getOutputTypeForFunction(functionName);
669

670
				if (['integer', 'float', 'decimal'].includes(type)) {
671
					compareValue = Array.isArray(compareValue) ? compareValue.map(Number) : Number(compareValue);
672
				}
673
			}
674

675
			// Cast filter value (compareValue) based on type of field being filtered against
676
			const [collection, field] = key.split('.');
677
			const mappedCollection = (originalCollectionName || collection)!;
678

679
			if (mappedCollection! in schema.collections && field! in schema.collections[mappedCollection]!.fields) {
680
				const type = schema.collections[mappedCollection]!.fields[field!]!.type;
681

682
				if (['date', 'dateTime', 'time', 'timestamp'].includes(type)) {
683
					if (Array.isArray(compareValue)) {
684
						compareValue = compareValue.map((val) => helpers.date.parse(val));
685
					} else {
686
						compareValue = helpers.date.parse(compareValue);
687
					}
688
				}
689

690
				if (['integer', 'float', 'decimal'].includes(type)) {
691
					if (Array.isArray(compareValue)) {
692
						compareValue = compareValue.map((val) => Number(val));
693
					} else {
694
						compareValue = Number(compareValue);
695
					}
696
				}
697
			}
698

699
			if (operator === '_eq') {
700
				dbQuery[logical].where(selectionRaw, '=', compareValue);
701
			}
702

703
			if (operator === '_neq') {
704
				dbQuery[logical].whereNot(selectionRaw, compareValue);
705
			}
706

707
			if (operator === '_ieq') {
708
				dbQuery[logical].whereRaw(`LOWER(??) = ?`, [selectionRaw, `${compareValue.toLowerCase()}`]);
709
			}
710

711
			if (operator === '_nieq') {
712
				dbQuery[logical].whereRaw(`LOWER(??) <> ?`, [selectionRaw, `${compareValue.toLowerCase()}`]);
713
			}
714

715
			if (operator === '_contains') {
716
				dbQuery[logical].where(selectionRaw, 'like', `%${compareValue}%`);
717
			}
718

719
			if (operator === '_ncontains') {
720
				dbQuery[logical].whereNot(selectionRaw, 'like', `%${compareValue}%`);
721
			}
722

723
			if (operator === '_icontains') {
724
				dbQuery[logical].whereRaw(`LOWER(??) LIKE ?`, [selectionRaw, `%${compareValue.toLowerCase()}%`]);
725
			}
726

727
			if (operator === '_nicontains') {
728
				dbQuery[logical].whereRaw(`LOWER(??) NOT LIKE ?`, [selectionRaw, `%${compareValue.toLowerCase()}%`]);
729
			}
730

731
			if (operator === '_starts_with') {
732
				dbQuery[logical].where(key, 'like', `${compareValue}%`);
733
			}
734

735
			if (operator === '_nstarts_with') {
736
				dbQuery[logical].whereNot(key, 'like', `${compareValue}%`);
737
			}
738

739
			if (operator === '_istarts_with') {
740
				dbQuery[logical].whereRaw(`LOWER(??) LIKE ?`, [selectionRaw, `${compareValue.toLowerCase()}%`]);
741
			}
742

743
			if (operator === '_nistarts_with') {
744
				dbQuery[logical].whereRaw(`LOWER(??) NOT LIKE ?`, [selectionRaw, `${compareValue.toLowerCase()}%`]);
745
			}
746

747
			if (operator === '_ends_with') {
748
				dbQuery[logical].where(key, 'like', `%${compareValue}`);
749
			}
750

751
			if (operator === '_nends_with') {
752
				dbQuery[logical].whereNot(key, 'like', `%${compareValue}`);
753
			}
754

755
			if (operator === '_iends_with') {
756
				dbQuery[logical].whereRaw(`LOWER(??) LIKE ?`, [selectionRaw, `%${compareValue.toLowerCase()}`]);
757
			}
758

759
			if (operator === '_niends_with') {
760
				dbQuery[logical].whereRaw(`LOWER(??) NOT LIKE ?`, [selectionRaw, `%${compareValue.toLowerCase()}`]);
761
			}
762

763
			if (operator === '_gt') {
764
				dbQuery[logical].where(selectionRaw, '>', compareValue);
765
			}
766

767
			if (operator === '_gte') {
768
				dbQuery[logical].where(selectionRaw, '>=', compareValue);
769
			}
770

771
			if (operator === '_lt') {
772
				dbQuery[logical].where(selectionRaw, '<', compareValue);
773
			}
774

775
			if (operator === '_lte') {
776
				dbQuery[logical].where(selectionRaw, '<=', compareValue);
777
			}
778

779
			if (operator === '_in') {
780
				let value = compareValue;
781
				if (typeof value === 'string') value = value.split(',');
782

783
				dbQuery[logical].whereIn(selectionRaw, value as string[]);
784
			}
785

786
			if (operator === '_nin') {
787
				let value = compareValue;
788
				if (typeof value === 'string') value = value.split(',');
789

790
				dbQuery[logical].whereNotIn(selectionRaw, value as string[]);
791
			}
792

793
			if (operator === '_between') {
794
				let value = compareValue;
795
				if (typeof value === 'string') value = value.split(',');
796

797
				if (value.length !== 2) return;
798

799
				dbQuery[logical].whereBetween(selectionRaw, value);
800
			}
801

802
			if (operator === '_nbetween') {
803
				let value = compareValue;
804
				if (typeof value === 'string') value = value.split(',');
805

806
				if (value.length !== 2) return;
807

808
				dbQuery[logical].whereNotBetween(selectionRaw, value);
809
			}
810

811
			if (operator == '_intersects') {
812
				dbQuery[logical].whereRaw(helpers.st.intersects(key, compareValue));
813
			}
814

815
			if (operator == '_nintersects') {
816
				dbQuery[logical].whereRaw(helpers.st.nintersects(key, compareValue));
817
			}
818

819
			if (operator == '_intersects_bbox') {
820
				dbQuery[logical].whereRaw(helpers.st.intersects_bbox(key, compareValue));
821
			}
822

823
			if (operator == '_nintersects_bbox') {
824
				dbQuery[logical].whereRaw(helpers.st.nintersects_bbox(key, compareValue));
825
			}
826
		}
827
	}
828
}
829

830
export async function applySearch(
831
	knex: Knex,
832
	schema: SchemaOverview,
833
	dbQuery: Knex.QueryBuilder,
834
	searchQuery: string,
835
	collection: string,
836
): Promise<void> {
837
	const { number: numberHelper } = getHelpers(knex);
838
	const fields = Object.entries(schema.collections[collection]!.fields);
839

840
	dbQuery.andWhere(function () {
841
		let needsFallbackCondition = true;
842

843
		fields.forEach(([name, field]) => {
844
			if (['text', 'string'].includes(field.type)) {
845
				this.orWhereRaw(`LOWER(??) LIKE ?`, [`${collection}.${name}`, `%${searchQuery.toLowerCase()}%`]);
846
				needsFallbackCondition = false;
847
			} else if (isNumericField(field)) {
848
				const number = parseNumericString(searchQuery);
849

850
				if (number === null) {
851
					return; // unable to parse
852
				}
853

854
				if (numberHelper.isNumberValid(number, field)) {
855
					numberHelper.addSearchCondition(this, collection, name, number);
856
					needsFallbackCondition = false;
857
				}
858
			} else if (field.type === 'uuid' && isValidUuid(searchQuery)) {
859
				this.orWhere({ [`${collection}.${name}`]: searchQuery });
860
				needsFallbackCondition = false;
861
			}
862
		});
863

864
		if (needsFallbackCondition) {
865
			this.orWhereRaw('1 = 0');
866
		}
867
	});
868
}
869

870
export function applyAggregate(
871
	schema: SchemaOverview,
872
	dbQuery: Knex.QueryBuilder,
873
	aggregate: Aggregate,
874
	collection: string,
875
	hasJoins: boolean,
876
): void {
877
	for (const [operation, fields] of Object.entries(aggregate)) {
878
		if (!fields) continue;
879

880
		for (const field of fields) {
881
			if (operation === 'avg') {
882
				dbQuery.avg(`${collection}.${field}`, { as: `avg->${field}` });
883
			}
884

885
			if (operation === 'avgDistinct') {
886
				dbQuery.avgDistinct(`${collection}.${field}`, { as: `avgDistinct->${field}` });
887
			}
888

889
			if (operation === 'countAll') {
890
				dbQuery.count('*', { as: 'countAll' });
891
			}
892

893
			if (operation === 'count') {
894
				if (field === '*') {
895
					dbQuery.count('*', { as: 'count' });
896
				} else {
897
					dbQuery.count(`${collection}.${field}`, { as: `count->${field}` });
898
				}
899
			}
900

901
			if (operation === 'countDistinct') {
902
				if (!hasJoins && schema.collections[collection]?.primary === field) {
903
					// Optimize to count as primary keys are unique
904
					dbQuery.count(`${collection}.${field}`, { as: `countDistinct->${field}` });
905
				} else {
906
					dbQuery.countDistinct(`${collection}.${field}`, { as: `countDistinct->${field}` });
907
				}
908
			}
909

910
			if (operation === 'sum') {
911
				dbQuery.sum(`${collection}.${field}`, { as: `sum->${field}` });
912
			}
913

914
			if (operation === 'sumDistinct') {
915
				dbQuery.sumDistinct(`${collection}.${field}`, { as: `sumDistinct->${field}` });
916
			}
917

918
			if (operation === 'min') {
919
				dbQuery.min(`${collection}.${field}`, { as: `min->${field}` });
920
			}
921

922
			if (operation === 'max') {
923
				dbQuery.max(`${collection}.${field}`, { as: `max->${field}` });
924
			}
925
		}
926
	}
927
}
928

929
function getFilterPath(key: string, value: Record<string, any>) {
930
	const path = [key];
931
	const childKey = Object.keys(value)[0];
932

933
	if (!childKey || (childKey.startsWith('_') === true && !['_none', '_some'].includes(childKey))) {
934
		return path;
935
	}
936

937
	if (isPlainObject(value)) {
938
		path.push(...getFilterPath(childKey, Object.values(value)[0]));
939
	}
940

941
	return path;
942
}
943

944
function getOperation(key: string, value: Record<string, any>): { operator: string; value: any } | null {
945
	if (key.startsWith('_') && !['_and', '_or', '_none', '_some'].includes(key)) {
946
		return { operator: key, value };
947
	} else if (!isPlainObject(value)) {
948
		return { operator: '_eq', value };
949
	}
950

951
	const childKey = Object.keys(value)[0];
952

953
	if (childKey) {
954
		return getOperation(childKey, Object.values(value)[0]);
955
	}
956

957
	return null;
958
}
959

960
function isNumericField(field: FieldOverview): field is FieldOverview & { type: NumericType } {
961
	return isIn(field.type, NUMERIC_TYPES);
962
}
963