directus

1
import { useEnv } from '@directus/env';
2
import type { Item, Query, SchemaOverview } from '@directus/types';
3
import { toArray } from '@directus/utils';
4
import type { Knex } from 'knex';
5
import { clone, cloneDeep, isNil, merge, pick, uniq } from 'lodash-es';
6
import { PayloadService } from '../services/payload.js';
7
import type { AST, FieldNode, FunctionFieldNode, M2ONode, NestedCollectionNode } from '../types/ast.js';
8
import { applyFunctionToColumnName } from '../utils/apply-function-to-column-name.js';
9
import applyQuery, { applyLimit, applySort, generateAlias, type ColumnSortRecord } from '../utils/apply-query.js';
10
import { getCollectionFromAlias } from '../utils/get-collection-from-alias.js';
11
import type { AliasMap } from '../utils/get-column-path.js';
12
import { getColumn } from '../utils/get-column.js';
13
import { parseFilterKey } from '../utils/parse-filter-key.js';
14
import { getHelpers } from './helpers/index.js';
15
import getDatabase from './index.js';
16

17
type RunASTOptions = {
18
	/**
19
	 * Query override for the current level
20
	 */
21
	query?: AST['query'];
22

23
	/**
24
	 * Knex instance
25
	 */
26
	knex?: Knex;
27

28
	/**
29
	 * Whether or not the current execution is a nested dataset in another AST
30
	 */
31
	nested?: boolean;
32

33
	/**
34
	 * Whether or not to strip out non-requested required fields automatically (eg IDs / FKs)
35
	 */
36
	stripNonRequested?: boolean;
37
};
38

39
/**
40
 * Execute a given AST using Knex. Returns array of items based on requested AST.
41
 */
42
export default async function runAST(
43
	originalAST: AST | NestedCollectionNode,
44
	schema: SchemaOverview,
45
	options?: RunASTOptions,
46
): Promise<null | Item | Item[]> {
47
	const ast = cloneDeep(originalAST);
48

49
	const knex = options?.knex || getDatabase();
50

51
	if (ast.type === 'a2o') {
52
		const results: { [collection: string]: null | Item | Item[] } = {};
53

54
		for (const collection of ast.names) {
55
			results[collection] = await run(collection, ast.children[collection]!, ast.query[collection]!);
56
		}
57

58
		return results;
59
	} else {
60
		return await run(ast.name, ast.children, options?.query || ast.query);
61
	}
62

63
	async function run(
64
		collection: string,
65
		children: (NestedCollectionNode | FieldNode | FunctionFieldNode)[],
66
		query: Query,
67
	) {
68
		const env = useEnv();
69

70
		// Retrieve the database columns to select in the current AST
71
		const { fieldNodes, primaryKeyField, nestedCollectionNodes } = await parseCurrentLevel(
72
			schema,
73
			collection,
74
			children,
75
			query,
76
		);
77

78
		// The actual knex query builder instance. This is a promise that resolves with the raw items from the db
79
		const dbQuery = await getDBQuery(schema, knex, collection, fieldNodes, query);
80

81
		const rawItems: Item | Item[] = await dbQuery;
82

83
		if (!rawItems) return null;
84

85
		// Run the items through the special transforms
86
		const payloadService = new PayloadService(collection, { knex, schema });
87
		let items: null | Item | Item[] = await payloadService.processValues('read', rawItems, query.alias ?? {});
88

89
		if (!items || (Array.isArray(items) && items.length === 0)) return items;
90

91
		// Apply the `_in` filters to the nested collection batches
92
		const nestedNodes = applyParentFilters(schema, nestedCollectionNodes, items);
93

94
		for (const nestedNode of nestedNodes) {
95
			let nestedItems: Item[] | null = [];
96

97
			if (nestedNode.type === 'o2m') {
98
				let hasMore = true;
99

100
				let batchCount = 0;
101

102
				while (hasMore) {
103
					const node = merge({}, nestedNode, {
104
						query: {
105
							limit: env['RELATIONAL_BATCH_SIZE'],
106
							offset: batchCount * (env['RELATIONAL_BATCH_SIZE'] as number),
107
							page: null,
108
						},
109
					});
110

111
					nestedItems = (await runAST(node, schema, { knex, nested: true })) as Item[] | null;
112

113
					if (nestedItems) {
114
						items = mergeWithParentItems(schema, nestedItems, items!, nestedNode)!;
115
					}
116

117
					if (!nestedItems || nestedItems.length < (env['RELATIONAL_BATCH_SIZE'] as number)) {
118
						hasMore = false;
119
					}
120

121
					batchCount++;
122
				}
123
			} else {
124
				const node = merge({}, nestedNode, {
125
					query: { limit: -1 },
126
				});
127

128
				nestedItems = (await runAST(node, schema, { knex, nested: true })) as Item[] | null;
129

130
				if (nestedItems) {
131
					// Merge all fetched nested records with the parent items
132
					items = mergeWithParentItems(schema, nestedItems, items!, nestedNode)!;
133
				}
134
			}
135
		}
136

137
		// During the fetching of data, we have to inject a couple of required fields for the child nesting
138
		// to work (primary / foreign keys) even if they're not explicitly requested. After all fetching
139
		// and nesting is done, we parse through the output structure, and filter out all non-requested
140
		// fields
141
		if (options?.nested !== true && options?.stripNonRequested !== false) {
142
			items = removeTemporaryFields(schema, items, originalAST, primaryKeyField);
143
		}
144

145
		return items;
146
	}
147
}
148

149
async function parseCurrentLevel(
150
	schema: SchemaOverview,
151
	collection: string,
152
	children: (NestedCollectionNode | FieldNode | FunctionFieldNode)[],
153
	query: Query,
154
) {
155
	const primaryKeyField = schema.collections[collection]!.primary;
156
	const columnsInCollection = Object.keys(schema.collections[collection]!.fields);
157

158
	const columnsToSelectInternal: string[] = [];
159
	const nestedCollectionNodes: NestedCollectionNode[] = [];
160

161
	for (const child of children) {
162
		if (child.type === 'field' || child.type === 'functionField') {
163
			const { fieldName } = parseFilterKey(child.name);
164

165
			if (columnsInCollection.includes(fieldName)) {
166
				columnsToSelectInternal.push(child.fieldKey);
167
			}
168

169
			continue;
170
		}
171

172
		if (!child.relation) continue;
173

174
		if (child.type === 'm2o') {
175
			columnsToSelectInternal.push(child.relation.field);
176
		}
177

178
		if (child.type === 'a2o') {
179
			columnsToSelectInternal.push(child.relation.field);
180
			columnsToSelectInternal.push(child.relation.meta!.one_collection_field!);
181
		}
182

183
		nestedCollectionNodes.push(child);
184
	}
185

186
	const isAggregate = (query.group || (query.aggregate && Object.keys(query.aggregate).length > 0)) ?? false;
187

188
	/** Always fetch primary key in case there's a nested relation that needs it. Aggregate payloads
189
	 * can't have nested relational fields
190
	 */
191
	if (isAggregate === false && columnsToSelectInternal.includes(primaryKeyField) === false) {
192
		columnsToSelectInternal.push(primaryKeyField);
193
	}
194

195
	/** Make sure select list has unique values */
196
	const columnsToSelect = [...new Set(columnsToSelectInternal)];
197

198
	const fieldNodes = columnsToSelect.map(
199
		(column: string) =>
200
			children.find(
201
				(childNode) =>
202
					(childNode.type === 'field' || childNode.type === 'functionField') && childNode.fieldKey === column,
203
			) ?? {
204
				type: 'field',
205
				name: column,
206
				fieldKey: column,
207
			},
208
	) as FieldNode[];
209

210
	return { fieldNodes, nestedCollectionNodes, primaryKeyField };
211
}
212

213
function getColumnPreprocessor(knex: Knex, schema: SchemaOverview, table: string) {
214
	const helpers = getHelpers(knex);
215

216
	return function (fieldNode: FieldNode | FunctionFieldNode | M2ONode): Knex.Raw<string> {
217
		let alias = undefined;
218

219
		if (fieldNode.name !== fieldNode.fieldKey) {
220
			alias = fieldNode.fieldKey;
221
		}
222

223
		let field;
224

225
		if (fieldNode.type === 'field' || fieldNode.type === 'functionField') {
226
			const { fieldName } = parseFilterKey(fieldNode.name);
227
			field = schema.collections[table]!.fields[fieldName];
228
		} else {
229
			field = schema.collections[fieldNode.relation.collection]!.fields[fieldNode.relation.field];
230
		}
231

232
		if (field?.type?.startsWith('geometry')) {
233
			return helpers.st.asText(table, field.field);
234
		}
235

236
		if (fieldNode.type === 'functionField') {
237
			return getColumn(knex, table, fieldNode.name, alias, schema, { query: fieldNode.query });
238
		}
239

240
		return getColumn(knex, table, fieldNode.name, alias, schema);
241
	};
242
}
243

244
async function getDBQuery(
245
	schema: SchemaOverview,
246
	knex: Knex,
247
	table: string,
248
	fieldNodes: (FieldNode | FunctionFieldNode)[],
249
	query: Query,
250
): Promise<Knex.QueryBuilder> {
251
	const env = useEnv();
252
	const preProcess = getColumnPreprocessor(knex, schema, table);
253
	const queryCopy = clone(query);
254
	const helpers = getHelpers(knex);
255

256
	queryCopy.limit = typeof queryCopy.limit === 'number' ? queryCopy.limit : Number(env['QUERY_LIMIT_DEFAULT']);
257

258
	// Queries with aggregates and groupBy will not have duplicate result
259
	if (queryCopy.aggregate || queryCopy.group) {
260
		const flatQuery = knex.select(fieldNodes.map(preProcess)).from(table);
261
		return await applyQuery(knex, table, flatQuery, queryCopy, schema).query;
262
	}
263

264
	const primaryKey = schema.collections[table]!.primary;
265
	const aliasMap: AliasMap = Object.create(null);
266
	let dbQuery = knex.from(table);
267
	let sortRecords: ColumnSortRecord[] | undefined;
268
	const innerQuerySortRecords: { alias: string; order: 'asc' | 'desc' }[] = [];
269
	let hasMultiRelationalSort: boolean | undefined;
270

271
	if (queryCopy.sort) {
272
		const sortResult = applySort(knex, schema, dbQuery, queryCopy, table, aliasMap, true);
273

274
		if (sortResult) {
275
			sortRecords = sortResult.sortRecords;
276
			hasMultiRelationalSort = sortResult.hasMultiRelationalSort;
277
		}
278
	}
279

280
	const { hasMultiRelationalFilter } = applyQuery(knex, table, dbQuery, queryCopy, schema, {
281
		aliasMap,
282
		isInnerQuery: true,
283
		hasMultiRelationalSort,
284
	});
285

286
	const needsInnerQuery = hasMultiRelationalSort || hasMultiRelationalFilter;
287

288
	if (needsInnerQuery) {
289
		dbQuery.select(`${table}.${primaryKey}`).distinct();
290
	} else {
291
		dbQuery.select(fieldNodes.map(preProcess));
292
	}
293

294
	if (sortRecords) {
295
		// Clears the order if any, eg: from MSSQL offset
296
		dbQuery.clear('order');
297

298
		if (needsInnerQuery) {
299
			let orderByString = '';
300
			const orderByFields: Knex.Raw[] = [];
301

302
			sortRecords.map((sortRecord) => {
303
				if (orderByString.length !== 0) {
304
					orderByString += ', ';
305
				}
306

307
				const sortAlias = `sort_${generateAlias()}`;
308

309
				if (sortRecord.column.includes('.')) {
310
					const [alias, field] = sortRecord.column.split('.');
311
					const originalCollectionName = getCollectionFromAlias(alias!, aliasMap);
312
					dbQuery.select(getColumn(knex, alias!, field!, sortAlias, schema, { originalCollectionName }));
313

314
					orderByString += `?? ${sortRecord.order}`;
315
					orderByFields.push(getColumn(knex, alias!, field!, false, schema, { originalCollectionName }));
316
				} else {
317
					dbQuery.select(getColumn(knex, table, sortRecord.column, sortAlias, schema));
318

319
					orderByString += `?? ${sortRecord.order}`;
320
					orderByFields.push(getColumn(knex, table, sortRecord.column, false, schema));
321
				}
322

323
				innerQuerySortRecords.push({ alias: sortAlias, order: sortRecord.order });
324
			});
325

326
			dbQuery.orderByRaw(orderByString, orderByFields);
327

328
			if (hasMultiRelationalSort) {
329
				dbQuery = helpers.schema.applyMultiRelationalSort(
330
					knex,
331
					dbQuery,
332
					table,
333
					primaryKey,
334
					orderByString,
335
					orderByFields,
336
				);
337
			}
338
		} else {
339
			sortRecords.map((sortRecord) => {
340
				if (sortRecord.column.includes('.')) {
341
					const [alias, field] = sortRecord.column.split('.');
342

343
					sortRecord.column = getColumn(knex, alias!, field!, false, schema, {
344
						originalCollectionName: getCollectionFromAlias(alias!, aliasMap),
345
					}) as any;
346
				} else {
347
					sortRecord.column = getColumn(knex, table, sortRecord.column, false, schema) as any;
348
				}
349
			});
350

351
			dbQuery.orderBy(sortRecords);
352
		}
353
	}
354

355
	if (!needsInnerQuery) return dbQuery;
356

357
	const wrapperQuery = knex
358
		.select(fieldNodes.map(preProcess))
359
		.from(table)
360
		.innerJoin(knex.raw('??', dbQuery.as('inner')), `${table}.${primaryKey}`, `inner.${primaryKey}`);
361

362
	if (sortRecords && needsInnerQuery) {
363
		innerQuerySortRecords.map((innerQuerySortRecord) => {
364
			wrapperQuery.orderBy(`inner.${innerQuerySortRecord.alias}`, innerQuerySortRecord.order);
365
		});
366

367
		if (hasMultiRelationalSort) {
368
			wrapperQuery.where('inner.directus_row_number', '=', 1);
369
			applyLimit(knex, wrapperQuery, queryCopy.limit);
370
		}
371
	}
372

373
	return wrapperQuery;
374
}
375

376
function applyParentFilters(
377
	schema: SchemaOverview,
378
	nestedCollectionNodes: NestedCollectionNode[],
379
	parentItem: Item | Item[],
380
) {
381
	const parentItems = toArray(parentItem);
382

383
	for (const nestedNode of nestedCollectionNodes) {
384
		if (!nestedNode.relation) continue;
385

386
		if (nestedNode.type === 'm2o') {
387
			const foreignField = schema.collections[nestedNode.relation.related_collection!]!.primary;
388
			const foreignIds = uniq(parentItems.map((res) => res[nestedNode.relation.field])).filter((id) => !isNil(id));
389

390
			merge(nestedNode, { query: { filter: { [foreignField]: { _in: foreignIds } } } });
391
		} else if (nestedNode.type === 'o2m') {
392
			const relatedM2OisFetched = !!nestedNode.children.find((child) => {
393
				return child.type === 'field' && child.name === nestedNode.relation.field;
394
			});
395

396
			if (relatedM2OisFetched === false) {
397
				nestedNode.children.push({
398
					type: 'field',
399
					name: nestedNode.relation.field,
400
					fieldKey: nestedNode.relation.field,
401
				});
402
			}
403

404
			if (nestedNode.relation.meta?.sort_field) {
405
				nestedNode.children.push({
406
					type: 'field',
407
					name: nestedNode.relation.meta.sort_field,
408
					fieldKey: nestedNode.relation.meta.sort_field,
409
				});
410
			}
411

412
			const foreignField = nestedNode.relation.field;
413
			const foreignIds = uniq(parentItems.map((res) => res[nestedNode.parentKey])).filter((id) => !isNil(id));
414

415
			merge(nestedNode, { query: { filter: { [foreignField]: { _in: foreignIds } } } });
416
		} else if (nestedNode.type === 'a2o') {
417
			const keysPerCollection: { [collection: string]: (string | number)[] } = {};
418

419
			for (const parentItem of parentItems) {
420
				const collection = parentItem[nestedNode.relation.meta!.one_collection_field!];
421
				if (!keysPerCollection[collection]) keysPerCollection[collection] = [];
422
				keysPerCollection[collection]!.push(parentItem[nestedNode.relation.field]);
423
			}
424

425
			for (const relatedCollection of nestedNode.names) {
426
				const foreignField = nestedNode.relatedKey[relatedCollection]!;
427
				const foreignIds = uniq(keysPerCollection[relatedCollection]);
428

429
				merge(nestedNode, {
430
					query: { [relatedCollection]: { filter: { [foreignField]: { _in: foreignIds } }, limit: foreignIds.length } },
431
				});
432
			}
433
		}
434
	}
435

436
	return nestedCollectionNodes;
437
}
438

439
function mergeWithParentItems(
440
	schema: SchemaOverview,
441
	nestedItem: Item | Item[],
442
	parentItem: Item | Item[],
443
	nestedNode: NestedCollectionNode,
444
) {
445
	const env = useEnv();
446
	const nestedItems = toArray(nestedItem);
447
	const parentItems = clone(toArray(parentItem));
448

449
	if (nestedNode.type === 'm2o') {
450
		for (const parentItem of parentItems) {
451
			const itemChild = nestedItems.find((nestedItem) => {
452
				return (
453
					nestedItem[schema.collections[nestedNode.relation.related_collection!]!.primary] ==
454
					parentItem[nestedNode.relation.field]
455
				);
456
			});
457

458
			parentItem[nestedNode.fieldKey] = itemChild || null;
459
		}
460
	} else if (nestedNode.type === 'o2m') {
461
		for (const parentItem of parentItems) {
462
			if (!parentItem[nestedNode.fieldKey]) parentItem[nestedNode.fieldKey] = [] as Item[];
463

464
			const itemChildren = nestedItems.filter((nestedItem) => {
465
				if (nestedItem === null) return false;
466
				if (Array.isArray(nestedItem[nestedNode.relation.field])) return true;
467

468
				return (
469
					nestedItem[nestedNode.relation.field] ==
470
						parentItem[schema.collections[nestedNode.relation.related_collection!]!.primary] ||
471
					nestedItem[nestedNode.relation.field]?.[
472
						schema.collections[nestedNode.relation.related_collection!]!.primary
473
					] == parentItem[schema.collections[nestedNode.relation.related_collection!]!.primary]
474
				);
475
			});
476

477
			parentItem[nestedNode.fieldKey].push(...itemChildren);
478

479
			const limit = nestedNode.query.limit ?? Number(env['QUERY_LIMIT_DEFAULT']);
480

481
			if (nestedNode.query.page && nestedNode.query.page > 1) {
482
				parentItem[nestedNode.fieldKey] = parentItem[nestedNode.fieldKey].slice(limit * (nestedNode.query.page - 1));
483
			}
484

485
			if (nestedNode.query.offset && nestedNode.query.offset >= 0) {
486
				parentItem[nestedNode.fieldKey] = parentItem[nestedNode.fieldKey].slice(nestedNode.query.offset);
487
			}
488

489
			if (limit !== -1) {
490
				parentItem[nestedNode.fieldKey] = parentItem[nestedNode.fieldKey].slice(0, limit);
491
			}
492

493
			parentItem[nestedNode.fieldKey] = parentItem[nestedNode.fieldKey].sort((a: Item, b: Item) => {
494
				// This is pre-filled in get-ast-from-query
495
				const sortField = nestedNode.query.sort![0]!;
496
				let column = sortField;
497
				let order: 'asc' | 'desc' = 'asc';
498

499
				if (sortField.startsWith('-')) {
500
					column = sortField.substring(1);
501
					order = 'desc';
502
				}
503

504
				if (a[column] === b[column]) return 0;
505
				if (a[column] === null) return 1;
506
				if (b[column] === null) return -1;
507

508
				if (order === 'asc') {
509
					return a[column] < b[column] ? -1 : 1;
510
				} else {
511
					return a[column] < b[column] ? 1 : -1;
512
				}
513
			});
514
		}
515
	} else if (nestedNode.type === 'a2o') {
516
		for (const parentItem of parentItems) {
517
			if (!nestedNode.relation.meta?.one_collection_field) {
518
				parentItem[nestedNode.fieldKey] = null;
519
				continue;
520
			}
521

522
			const relatedCollection = parentItem[nestedNode.relation.meta.one_collection_field];
523

524
			if (!(nestedItem as Record<string, any[]>)[relatedCollection]) {
525
				parentItem[nestedNode.fieldKey] = null;
526
				continue;
527
			}
528

529
			const itemChild = (nestedItem as Record<string, any[]>)[relatedCollection]!.find((nestedItem) => {
530
				return nestedItem[nestedNode.relatedKey[relatedCollection]!] == parentItem[nestedNode.fieldKey];
531
			});
532

533
			parentItem[nestedNode.fieldKey] = itemChild || null;
534
		}
535
	}
536

537
	return Array.isArray(parentItem) ? parentItems : parentItems[0];
538
}
539

540
function removeTemporaryFields(
541
	schema: SchemaOverview,
542
	rawItem: Item | Item[],
543
	ast: AST | NestedCollectionNode,
544
	primaryKeyField: string,
545
	parentItem?: Item,
546
): null | Item | Item[] {
547
	const rawItems = cloneDeep(toArray(rawItem));
548
	const items: Item[] = [];
549

550
	if (ast.type === 'a2o') {
551
		const fields: Record<string, string[]> = {};
552
		const nestedCollectionNodes: Record<string, NestedCollectionNode[]> = {};
553

554
		for (const relatedCollection of ast.names) {
555
			if (!fields[relatedCollection]) fields[relatedCollection] = [];
556
			if (!nestedCollectionNodes[relatedCollection]) nestedCollectionNodes[relatedCollection] = [];
557

558
			for (const child of ast.children[relatedCollection]!) {
559
				if (child.type === 'field' || child.type === 'functionField') {
560
					fields[relatedCollection]!.push(child.name);
561
				} else {
562
					fields[relatedCollection]!.push(child.fieldKey);
563
					nestedCollectionNodes[relatedCollection]!.push(child);
564
				}
565
			}
566
		}
567

568
		for (const rawItem of rawItems) {
569
			const relatedCollection: string = parentItem?.[ast.relation.meta!.one_collection_field!];
570

571
			if (rawItem === null || rawItem === undefined) return rawItem;
572

573
			let item = rawItem;
574

575
			for (const nestedNode of nestedCollectionNodes[relatedCollection]!) {
576
				item[nestedNode.fieldKey] = removeTemporaryFields(
577
					schema,
578
					item[nestedNode.fieldKey],
579
					nestedNode,
580
					schema.collections[nestedNode.relation.collection]!.primary,
581
					item,
582
				);
583
			}
584

585
			const fieldsWithFunctionsApplied = fields[relatedCollection]!.map((field) => applyFunctionToColumnName(field));
586

587
			item =
588
				fields[relatedCollection]!.length > 0 ? pick(rawItem, fieldsWithFunctionsApplied) : rawItem[primaryKeyField];
589

590
			items.push(item);
591
		}
592
	} else {
593
		const fields: string[] = [];
594
		const nestedCollectionNodes: NestedCollectionNode[] = [];
595

596
		for (const child of ast.children) {
597
			fields.push(child.fieldKey);
598

599
			if (child.type !== 'field' && child.type !== 'functionField') {
600
				nestedCollectionNodes.push(child);
601
			}
602
		}
603

604
		// Make sure any requested aggregate fields are included
605
		if (ast.query?.aggregate) {
606
			for (const [operation, aggregateFields] of Object.entries(ast.query.aggregate)) {
607
				if (!fields) continue;
608

609
				if (operation === 'count' && aggregateFields.includes('*')) fields.push('count');
610

611
				fields.push(...aggregateFields.map((field) => `${operation}.${field}`));
612
			}
613
		}
614

615
		for (const rawItem of rawItems) {
616
			if (rawItem === null || rawItem === undefined) return rawItem;
617

618
			let item = rawItem;
619

620
			for (const nestedNode of nestedCollectionNodes) {
621
				item[nestedNode.fieldKey] = removeTemporaryFields(
622
					schema,
623
					item[nestedNode.fieldKey],
624
					nestedNode,
625
					nestedNode.type === 'm2o'
626
						? schema.collections[nestedNode.relation.related_collection!]!.primary
627
						: schema.collections[nestedNode.relation.collection]!.primary,
628
					item,
629
				);
630
			}
631

632
			const fieldsWithFunctionsApplied = fields.map((field) => applyFunctionToColumnName(field));
633

634
			item = fields.length > 0 ? pick(rawItem, fieldsWithFunctionsApplied) : rawItem[primaryKeyField];
635

636
			items.push(item);
637
		}
638
	}
639

640
	return Array.isArray(rawItem) ? items : items[0]!;
641
}
642