@peerbit/indexer-sqlite3 - npm Package Compare versions

Comparing version 1.0.2 to 1.0.3

dist/src/engine.js

 import { getSchema } from "@dao-xyz/borsh";
 import * as types from "@peerbit/indexer-interface";
 import { v4 as uuid } from "uuid";
-import { buildJoin, convertCountRequestToQuery, convertDeleteRequestToQuery, convertSearchRequestToQuery, 
+import { MissingFieldError, buildJoin, convertCountRequestToQuery, convertDeleteRequestToQuery, convertSearchRequestToQuery, 
 /* getTableName, */
-convertSumRequestToQuery, escapeColumnName, getInlineTableFieldName, getSQLTable, getTablePrefixedField, insert, resolveInstanceFromValue, resolveTable, selectAllFields, selectChildren, } from "./schema.js";
+convertSumRequestToQuery, escapeColumnName, generateSelectQuery, getInlineTableFieldName, getSQLTable, getTablePrefixedField, insert, resolveInstanceFromValue, resolveTable, selectAllFieldsFromTable, selectChildren, } from "./schema.js";
 const escapePathToSQLName = (path) => {
@@ -68,3 +68,3 @@ return path.map((x) => x.replace(/[^a-zA-Z0-9]/g, "_"));
         }
-        this.primaryKeyString = getInlineTableFieldName(this.primaryKeyArr.slice(0, this.primaryKeyArr.length - 1), this.primaryKeyArr[this.primaryKeyArr.length - 1]);
+        this.primaryKeyString = getInlineTableFieldName(this.primaryKeyArr);
         return this;
@@ -85,8 +85,5 @@ }
         this._cursor = new Map();
-        const tables = getSQLTable(this.properties.schema, this.scopeString ? [this.scopeString] : [], getInlineTableFieldName(this.primaryKeyArr.slice(0, -1), this.primaryKeyArr[this.primaryKeyArr.length - 1]), // TODO fix this, should be array
+        const tables = getSQLTable(this.properties.schema, this.scopeString ? [this.scopeString] : [], getInlineTableFieldName(this.primaryKeyArr), // TODO fix this, should be array
         false, undefined, false);
         this._rootTables = tables.filter((x) => x.parent == null);
-        if (this._rootTables.length > 1) {
-            throw new Error("Multiple root tables not supported (yet)");
-        }
         const allTables = tables;
@@ -169,4 +166,4 @@ for (const table of allTables) {
         for (const table of this._rootTables) {
-            const { join: joinMap, query } = selectAllFields(table, options?.shape);
-            const sql = `${query} ${buildJoin(joinMap, true)} where ${this.primaryKeyString} = ? `;
+            const { join: joinMap, selects } = selectAllFieldsFromTable(table, options?.shape);
+            const sql = `${generateSelectQuery(table, selects)} ${buildJoin(joinMap, true)} where ${this.primaryKeyString} = ? `;
             const stmt = await this.properties.db.prepare(sql);
@@ -231,3 +228,3 @@ const rows = await stmt.get([id.key]);
             let results = await Promise.all(allResults.map(async (row) => {
-                let selectedTable = this._rootTables.find((table) => row[getTablePrefixedField(table, this.primaryKeyString)] != null);
+                let selectedTable = this._rootTables.find((table /* row["table_name"] === table.name, */) => row[getTablePrefixedField(table, this.primaryKeyString)] != null);
                 const value = await resolveInstanceFromValue(row, this.tables, selectedTable, this.resolveDependencies.bind(this), true, options?.shape);
@@ -296,11 +293,26 @@ return {
         let ret = [];
+        let once = false;
+        let lastError = undefined;
         for (const table of this._rootTables) {
-            const stmt = await this.properties.db.prepare(convertDeleteRequestToQuery(query, this.tables, table));
-            const results = await stmt.all([]);
-            await stmt.finalize?.();
-            // TODO types
-            for (const result of results) {
-                ret.push(types.toId(result[table.primary]));
+            try {
+                const stmt = await this.properties.db.prepare(convertDeleteRequestToQuery(query, this.tables, table));
+                const results = await stmt.all([]);
+                await stmt.finalize?.();
+                // TODO types
+                for (const result of results) {
+                    ret.push(types.toId(result[table.primary]));
+                }
+                once = true;
             }
+            catch (error) {
+                if (error instanceof MissingFieldError) {
+                    lastError = error;
+                    continue;
+                }
+                throw error;
+            }
         }
+        if (!once) {
+            throw lastError;
+        }
         return ret;
@@ -310,13 +322,33 @@ }
         let ret = undefined;
+        let once = false;
+        let lastError = undefined;
+        let inlinedName = getInlineTableFieldName(query.key);
         for (const table of this._rootTables) {
-            const stmt = await this.properties.db.prepare(convertSumRequestToQuery(query, this.tables, table));
-            const result = await stmt.get();
-            await stmt.finalize?.();
-            if (ret == null) {
-                ret = result.sum;
+            try {
+                if (table.fields.find((x) => x.name === inlinedName) == null) {
+                    lastError = new MissingFieldError("Missing field: " + query.key.join("."));
+                    continue;
+                }
+                const stmt = await this.properties.db.prepare(convertSumRequestToQuery(query, this.tables, table));
+                const result = await stmt.get();
+                await stmt.finalize?.();
+                if (ret == null) {
+                    ret = result.sum;
+                }
+                else {
+                    ret += result.sum;
+                }
+                once = true;
             }
-            else {
-                ret += result.sum;
+            catch (error) {
+                if (error instanceof MissingFieldError) {
+                    lastError = error;
+                    continue;
+                }
+                throw error;
             }
         }
+        if (!once) {
+            throw lastError;
+        }
         return ret != null ? ret : 0;
@@ -326,8 +358,23 @@ }
         let ret = 0;
+        let once = false;
+        let lastError = undefined;
         for (const table of this._rootTables) {
-            const stmt = await this.properties.db.prepare(convertCountRequestToQuery(request, this.tables, table));
-            const result = await stmt.get();
-            await stmt.finalize?.();
-            ret += Number(result.count);
+            try {
+                const stmt = await this.properties.db.prepare(convertCountRequestToQuery(request, this.tables, table));
+                const result = await stmt.get();
+                await stmt.finalize?.();
+                ret += Number(result.count);
+                once = true;
+            }
+            catch (error) {
+                if (error instanceof MissingFieldError) {
+                    lastError = error;
+                    continue;
+                }
+                throw error;
+            }
         }
+        if (!once) {
+            throw lastError;
+        }
         return ret;
@@ -334,0 +381,0 @@ }

dist/src/schema.d.ts

@@ -7,2 +7,5 @@ import { type AbstractType, type Constructor, type Field, type FieldType } from "@dao-xyz/borsh";
 export declare const escapeColumnName: (name: string) => string;
+export declare class MissingFieldError extends Error {
+    constructor(message: string);
+}
 export declare const convertFromSQLType: (value: boolean | bigint | string | number | Uint8Array, type?: FieldType) => any;
@@ -53,6 +56,20 @@ export declare const toSQLType: (type: FieldType, isOptional?: boolean) => string;
 export declare const getTableNameFromPrefixedField: (prefixedField: string) => string;
-export declare const getInlineTableFieldName: (path: string[] | undefined, key: string) => string;
+export declare const getInlineTableFieldName: (path: string[] | undefined, key?: string) => string;
 export declare const selectChildren: (childrenTable: Table) => string;
-export declare const selectAllFields: (table: Table, shape: types.Shape | undefined) => {
-    query: string;
+export declare const generateSelectQuery: (table: Table, selects: {
+    from: string;
+    as: string;
+}[]) => string;
+export declare const selectAllFieldsFromTables: (tables: Table[], shape: types.Shape | undefined) => {
+    selects: {
+        from: string;
+        as: string;
+    }[];
+    joins: Map<string, JoinTable>;
+}[];
+export declare const selectAllFieldsFromTable: (table: Table, shape: types.Shape | undefined) => {
+    selects: {
+        from: string;
+        as: string;
+    }[];
     join: Map<string, JoinTable>;
@@ -59,0 +76,0 @@ };

dist/src/schema.js

@@ -42,2 +42,8 @@ var __decorate = (this && this.__decorate) || function (decorators, target, key, desc) {
 export const escapeColumnName = (name) => `"${name}"`;
+export class MissingFieldError extends Error {
+    constructor(message) {
+        super(message);
+        this.name = "MissingFieldError";
+    }
+}
 export const convertFromSQLType = (value, type) => {
@@ -566,3 +572,14 @@ if (type === "bool") {
 export const getTableNameFromPrefixedField = (prefixedField) => prefixedField.split("#")[0];
-export const getInlineTableFieldName = (path, key) => (path && path.length > 0 ? `${path.join("_")}__${key}` : key);
+export const getInlineTableFieldName = (path, key) => {
+    if (key) {
+        return path && path.length > 0 ? `${path.join("_")}__${key}` : key;
+    }
+    else {
+        // last element in the path is the key, the rest is the path
+        // join key with __ , rest with _
+        return path.length > 2
+            ? `${path.slice(0, -1).join("_")}__${path[path.length - 1]}`
+            : path.join("__");
+    }
+};
 const matchFieldInShape = (shape, path, field) => {
@@ -591,3 +608,39 @@ if (!shape) {
 export const selectChildren = (childrenTable) => "select * from " + childrenTable.name + " where " + PARENT_TABLE_ID + " = ?";
-export const selectAllFields = (table, shape) => {
+export const generateSelectQuery = (table, selects) => {
+    return `SELECT ${selects.map((x) => `${x.from} as ${x.as}`).join(", ")} FROM ${table.name}`;
+};
+export const selectAllFieldsFromTables = (tables, shape) => {
+    const selectsPerTable = [];
+    for (const table of tables) {
+        const { selects, join: joinFromSelect } = selectAllFieldsFromTable(table, shape);
+        selectsPerTable.push({ selects, joins: joinFromSelect });
+    }
+    // pad with empty selects to make sure all selects have the same length
+    /* const maxSelects = Math.max(...selectsPerTable.map(x => x.selects.length)); */
+    let newSelects = [];
+    for (const [i, selects] of selectsPerTable.entries()) {
+        const newSelect = [];
+        for (const [j, selectsOther] of selectsPerTable.entries()) {
+            if (i !== j) {
+                for (const select of selectsOther.selects) {
+                    newSelect.push({ from: "NULL", as: select.as });
+                }
+            }
+            else {
+                selects.selects.forEach((select) => newSelect.push(select));
+            }
+        }
+        newSelects.push(newSelect);
+        /* let pad = 0;
+        while (select.selects.length < maxSelects) {
+            select.selects.push({ from: "NULL", as: `'pad#${++pad}'` });
+        } */
+    }
+    // also return table name
+    for (const [i, selects] of selectsPerTable.entries()) {
+        selects.selects = newSelects[i];
+    }
+    return selectsPerTable;
+};
+export const selectAllFieldsFromTable = (table, shape) => {
     let stack = [{ table, shape }];
@@ -602,4 +655,6 @@ let join = new Map();
                     matchFieldInShape(tableAndShape.shape, [], field)) {
-                    const value = `${tableAndShape.table.name}.${escapeColumnName(field.name)} as '${getTablePrefixedField(tableAndShape.table, field.name)}'`;
-                    fieldResolvers.push(value);
+                    fieldResolvers.push({
+                        from: `${tableAndShape.table.name}.${escapeColumnName(field.name)}`,
+                        as: `'${getTablePrefixedField(tableAndShape.table, field.name)}'`,
+                    });
                 }
@@ -633,3 +688,3 @@ }
     return {
-        query: `SELECT ${fieldResolvers.join(", ")} FROM ${table.name}`,
+        selects: fieldResolvers, //  `SELECT ${fieldResolvers.join(", ")} FROM ${table.name}`,
         join,
@@ -771,3 +826,3 @@ };
 export const convertSumRequestToQuery = (request, tables, table) => {
-    return `SELECT SUM(${table.name}.${request.key.join(".")}) as sum FROM ${table.name} ${convertRequestToQuery(request, tables, table).query}`;
+    return `SELECT SUM(${table.name}.${getInlineTableFieldName(request.key)}) as sum FROM ${table.name} ${convertRequestToQuery(request, tables, table).query}`;
 };
@@ -779,10 +834,32 @@ export const convertCountRequestToQuery = (request, tables, table) => {
     let unionBuilder = "";
-    let orderByClause = undefined;
-    for (const table of rootTables) {
-        const { query: selectQuery, join: joinFromSelect } = selectAllFields(table, shape);
-        const { orderBy, query } = convertRequestToQuery(request, tables, table, joinFromSelect);
-        unionBuilder += `${unionBuilder.length > 0 ? " UNION ALL " : ""} ${selectQuery} ${query}`;
-        orderByClause = orderBy?.length > 0 ? orderBy : orderByClause;
+    let orderByClause = "";
+    let matchedOnce = false;
+    let lastError = undefined;
+    const selectsPerTable = selectAllFieldsFromTables(rootTables, shape);
+    for (const [i, table] of rootTables.entries()) {
+        const { selects, joins: joinFromSelect } = selectsPerTable[i];
+        const selectQuery = generateSelectQuery(table, selects);
+        try {
+            const { orderBy, query } = convertRequestToQuery(request, tables, table, joinFromSelect);
+            unionBuilder += `${unionBuilder.length > 0 ? " UNION ALL " : ""} ${selectQuery} ${query}`;
+            orderByClause =
+                orderBy?.length > 0
+                    ? orderByClause.length > 0
+                        ? orderByClause + ", " + orderBy
+                        : orderBy
+                    : orderByClause;
+            matchedOnce = true;
+        }
+        catch (error) {
+            if (error instanceof MissingFieldError) {
+                lastError = error;
+                continue;
+            }
+            throw error;
+        }
     }
-    return `${unionBuilder} ${orderByClause ? orderByClause : ""} limit ? offset ?`;
+    if (!matchedOnce) {
+        throw lastError;
+    }
+    return `${unionBuilder} ${orderByClause ? "ORDER BY " + orderByClause : ""} limit ? offset ?`;
 };
@@ -804,5 +881,3 @@ const convertRequestToQuery = (request, tables, table, extraJoin, path = []) => {
         if (request.sort.length > 0) {
-            if (request.sort.length > 0) {
-                orderByBuilder = "ORDER BY ";
-            }
+            orderByBuilder = "";
             let once = false;
@@ -928,3 +1003,3 @@ for (const sort of request.sort) {
     if (searchSelf) {
-        const inlineName = getInlineTableFieldName(path.slice(0, -1), path[path.length - 1]);
+        const inlineName = getInlineTableFieldName(path);
         let field = table.fields.find((x) => x.name === inlineName);
@@ -949,3 +1024,3 @@ if (field) {
                 // second arg is needed because of polymorphic fields we might end up here intentially to check what tables to query
-                throw new Error(`Property with key "${key}" is not found in the schema ${JSON.stringify(schema.fields.map((x) => x.key))}`);
+                throw new MissingFieldError(`Property with key "${key}" is not found in the schema ${JSON.stringify(schema.fields.map((x) => x.key))}`);
             }
@@ -989,2 +1064,5 @@ for (const child of currentTable.children) {
     let foreignTables = currentTables.filter((x) => x.table.fields.find((x) => x.key === path[path.length - 1]));
+    if (foreignTables.length === 0) {
+        throw new MissingFieldError("Failed to find field to join");
+    }
     let tableToQuery = foreignTables[foreignTables.length - 1].table;
@@ -997,3 +1075,3 @@ let queryKeyPath = [path[path.length - 1]];
     let queryKey = queryKeyPath.length > 0
-        ? getInlineTableFieldName(queryKeyPath.slice(0, -1), queryKeyPath[queryKeyPath.length - 1])
+        ? getInlineTableFieldName(queryKeyPath)
         : FOREIGN_VALUE_PROPERTY;
@@ -1004,3 +1082,3 @@ return { queryKey, foreignTables };
     // if field id represented as foreign table, do join and compare
-    const inlinedName = getInlineTableFieldName(query.key.slice(0, query.key.length - 1), query.key[query.key.length - 1]);
+    const inlinedName = getInlineTableFieldName(query.key);
     const tableField = table.fields.find((x) => x.name === inlinedName); /* stringArraysEquals(query.key, [...table.parentPath, x.name]) )*/
@@ -1007,0 +1085,0 @@ const isForeign = !tableField; // table.fields.find(x => x.name === query.key[query.key.length - 1])

package.json

 {
 	"name": "@peerbit/indexer-sqlite3",
-	"version": "1.0.2",
+	"version": "1.0.3",
 	"description": "SQLite index for document store",
@@ -78,4 +78,4 @@ "type": "module",
 		"@types/better-sqlite3": "^7.6.11",
-		"@peerbit/indexer-tests": "^1.0.0"
+		"@peerbit/indexer-tests": "^1.0.1"
 	}
 }

src/engine.ts

@@ -15,2 +15,3 @@ import { type AbstractType, type Constructor, getSchema } from "@dao-xyz/borsh";
 import {
+	MissingFieldError,
 	type Table,
@@ -24,2 +25,3 @@ buildJoin,
 	escapeColumnName,
+	generateSelectQuery,
 	getInlineTableFieldName,
@@ -31,3 +33,3 @@ getSQLTable,
 	resolveTable,
-	selectAllFields,
+	selectAllFieldsFromTable,
 	selectChildren,
@@ -130,6 +132,3 @@ } from "./schema.js";
 
-		this.primaryKeyString = getInlineTableFieldName(
-			this.primaryKeyArr.slice(0, this.primaryKeyArr.length - 1),
-			this.primaryKeyArr[this.primaryKeyArr.length - 1],
-		);
+		this.primaryKeyString = getInlineTableFieldName(this.primaryKeyArr);
 
@@ -159,6 +158,3 @@ return this;
 			this.scopeString ? [this.scopeString] : [],
-			getInlineTableFieldName(
-				this.primaryKeyArr.slice(0, -1),
-				this.primaryKeyArr[this.primaryKeyArr.length - 1],
-			), // TODO fix this, should be array
+			getInlineTableFieldName(this.primaryKeyArr), // TODO fix this, should be array
 			false,
@@ -172,6 +168,2 @@ undefined,
 
-		if (this._rootTables.length > 1) {
-			throw new Error("Multiple root tables not supported (yet)");
-		}
-
 		const allTables = tables;
@@ -293,4 +285,7 @@
 		for (const table of this._rootTables) {
-			const { join: joinMap, query } = selectAllFields(table, options?.shape);
-			const sql = `${query} ${buildJoin(joinMap, true)} where ${this.primaryKeyString} = ? `;
+			const { join: joinMap, selects } = selectAllFieldsFromTable(
+				table,
+				options?.shape,
+			);
+			const sql = `${generateSelectQuery(table, selects)} ${buildJoin(joinMap, true)} where ${this.primaryKeyString} = ? `;
 			const stmt = await this.properties.db.prepare(sql);
@@ -400,5 +395,6 @@ const rows = await stmt.get([id.key]);
 					let selectedTable = this._rootTables.find(
-						(table) =>
+						(table /* row["table_name"] === table.name, */) =>
 							row[getTablePrefixedField(table, this.primaryKeyString)] != null,
 					)!;
+
 					const value = await resolveInstanceFromValue<T>(
@@ -439,2 +435,3 @@ row,
 		};
+
 		const iterator = {
@@ -495,13 +492,30 @@ kept: 0,
 		let ret: types.IdKey[] = [];
+		let once = false;
+		let lastError: Error | undefined = undefined;
 		for (const table of this._rootTables) {
-			const stmt = await this.properties.db.prepare(
-				convertDeleteRequestToQuery(query, this.tables, table),
-			);
-			const results: any[] = await stmt.all([]);
-			await stmt.finalize?.();
-			// TODO types
-			for (const result of results) {
-				ret.push(types.toId(result[table.primary as string]));
+			try {
+				const stmt = await this.properties.db.prepare(
+					convertDeleteRequestToQuery(query, this.tables, table),
+				);
+				const results: any[] = await stmt.all([]);
+				await stmt.finalize?.();
+				// TODO types
+				for (const result of results) {
+					ret.push(types.toId(result[table.primary as string]));
+				}
+				once = true;
+			} catch (error) {
+				if (error instanceof MissingFieldError) {
+					lastError = error;
+					continue;
+				}
+
+				throw error;
 			}
 		}
+
+		if (!once) {
+			throw lastError;
+		}
+
 		return ret;
@@ -512,14 +526,39 @@ }
 		let ret: number | bigint | undefined = undefined;
+		let once = false;
+		let lastError: Error | undefined = undefined;
+
+		let inlinedName = getInlineTableFieldName(query.key);
 		for (const table of this._rootTables) {
-			const stmt = await this.properties.db.prepare(
-				convertSumRequestToQuery(query, this.tables, table),
-			);
-			const result = await stmt.get();
-			await stmt.finalize?.();
-			if (ret == null) {
-				(ret as any) = result.sum as number;
-			} else {
-				(ret as any) += result.sum as number;
+			try {
+				if (table.fields.find((x) => x.name === inlinedName) == null) {
+					lastError = new MissingFieldError(
+						"Missing field: " + query.key.join("."),
+					);
+					continue;
+				}
+
+				const stmt = await this.properties.db.prepare(
+					convertSumRequestToQuery(query, this.tables, table),
+				);
+				const result = await stmt.get();
+				await stmt.finalize?.();
+				if (ret == null) {
+					(ret as any) = result.sum as number;
+				} else {
+					(ret as any) += result.sum as number;
+				}
+				once = true;
+			} catch (error) {
+				if (error instanceof MissingFieldError) {
+					lastError = error;
+					continue;
+				}
+				throw error;
 			}
 		}
+
+		if (!once) {
+			throw lastError;
+		}
+
 		return ret != null ? ret : 0;
@@ -530,10 +569,26 @@ }
 		let ret: number = 0;
+		let once = false;
+		let lastError: Error | undefined = undefined;
 		for (const table of this._rootTables) {
-			const stmt = await this.properties.db.prepare(
-				convertCountRequestToQuery(request, this.tables, table),
-			);
-			const result = await stmt.get();
-			await stmt.finalize?.();
-			ret += Number(result.count);
+			try {
+				const stmt = await this.properties.db.prepare(
+					convertCountRequestToQuery(request, this.tables, table),
+				);
+				const result = await stmt.get();
+				await stmt.finalize?.();
+				ret += Number(result.count);
+				once = true;
+			} catch (error) {
+				if (error instanceof MissingFieldError) {
+					lastError = error;
+					continue;
+				}
+
+				throw error;
+			}
 		}
+
+		if (!once) {
+			throw lastError;
+		}
 		return ret;
@@ -540,0 +595,0 @@ }

src/schema.ts

@@ -75,2 +75,9 @@ import {
 
+export class MissingFieldError extends Error {
+	constructor(message: string) {
+		super(message);
+		this.name = "MissingFieldError";
+	}
+}
+
 export const convertFromSQLType = (
@@ -818,7 +825,19 @@ value: boolean | bigint | string | number | Uint8Array,
 	prefixedField.split("#")[0];
+
 export const getInlineTableFieldName = (
 	path: string[] | undefined,
-	key: string,
-) => (path && path.length > 0 ? `${path.join("_")}__${key}` : key);
+	key?: string,
+) => {
+	if (key) {
+		return path && path.length > 0 ? `${path.join("_")}__${key}` : key;
+	} else {
+		// last element in the path is the key, the rest is the path
+		// join key with __ , rest with _
 
+		return path!.length > 2
+			? `${path!.slice(0, -1).join("_")}__${path![path!.length - 1]}`
+			: path!.join("__");
+	}
+};
+
 const matchFieldInShape = (
@@ -856,9 +875,69 @@ shape: types.Shape | undefined,
 
-export const selectAllFields = (
+export const generateSelectQuery = (
 	table: Table,
+	selects: { from: string; as: string }[],
+) => {
+	return `SELECT ${selects.map((x) => `${x.from} as ${x.as}`).join(", ")} FROM ${table.name}`;
+};
+
+export const selectAllFieldsFromTables = (
+	tables: Table[],
 	shape: types.Shape | undefined,
 ) => {
+	const selectsPerTable: {
+		selects: {
+			from: string;
+			as: string;
+		}[];
+		joins: Map<string, JoinTable>;
+	}[] = [];
+
+	for (const table of tables) {
+		const { selects, join: joinFromSelect } = selectAllFieldsFromTable(
+			table,
+			shape,
+		);
+		selectsPerTable.push({ selects, joins: joinFromSelect });
+	}
+
+	// pad with empty selects to make sure all selects have the same length
+	/* const maxSelects = Math.max(...selectsPerTable.map(x => x.selects.length)); */
+
+	let newSelects: {
+		from: string;
+		as: string;
+	}[][] = [];
+	for (const [i, selects] of selectsPerTable.entries()) {
+		const newSelect = [];
+		for (const [j, selectsOther] of selectsPerTable.entries()) {
+			if (i !== j) {
+				for (const select of selectsOther.selects) {
+					newSelect.push({ from: "NULL", as: select.as });
+				}
+			} else {
+				selects.selects.forEach((select) => newSelect.push(select));
+			}
+		}
+		newSelects.push(newSelect);
+
+		/* let pad = 0;
+		while (select.selects.length < maxSelects) {
+			select.selects.push({ from: "NULL", as: `'pad#${++pad}'` });
+		} */
+	}
+	// also return table name
+	for (const [i, selects] of selectsPerTable.entries()) {
+		selects.selects = newSelects[i];
+	}
+
+	return selectsPerTable;
+};
+
+export const selectAllFieldsFromTable = (
+	table: Table,
+	shape: types.Shape | undefined,
+) => {
 	let stack: { table: Table; shape?: types.Shape }[] = [{ table, shape }];
 	let join: Map<string, JoinTable> = new Map();
-	const fieldResolvers: string[] = [];
+	const fieldResolvers: { from: string; as: string }[] = [];
 	for (const tableAndShape of stack) {
@@ -872,4 +951,6 @@ if (!tableAndShape.table.inline) {
 				) {
-					const value = `${tableAndShape.table.name}.${escapeColumnName(field.name)} as '${getTablePrefixedField(tableAndShape.table, field.name)}'`;
-					fieldResolvers.push(value);
+					fieldResolvers.push({
+						from: `${tableAndShape.table.name}.${escapeColumnName(field.name)}`,
+						as: `'${getTablePrefixedField(tableAndShape.table, field.name)}'`,
+					});
 				}
@@ -910,3 +991,3 @@ }
 	return {
-		query: `SELECT ${fieldResolvers.join(", ")} FROM ${table.name}`,
+		selects: fieldResolvers, //  `SELECT ${fieldResolvers.join(", ")} FROM ${table.name}`,
 		join,
@@ -1128,3 +1209,3 @@ };
 ) => {
-	return `SELECT SUM(${table.name}.${request.key.join(".")}) as sum FROM ${table.name} ${convertRequestToQuery(request, tables, table).query}`;
+	return `SELECT SUM(${table.name}.${getInlineTableFieldName(request.key)}) as sum FROM ${table.name} ${convertRequestToQuery(request, tables, table).query}`;
 };
@@ -1147,19 +1228,41 @@
 	let unionBuilder = "";
-	let orderByClause: string | undefined = undefined;
-	for (const table of rootTables) {
-		const { query: selectQuery, join: joinFromSelect } = selectAllFields(
-			table,
-			shape,
-		);
-		const { orderBy, query } = convertRequestToQuery(
-			request,
-			tables,
-			table,
-			joinFromSelect,
-		);
-		unionBuilder += `${unionBuilder.length > 0 ? " UNION ALL " : ""} ${selectQuery} ${query}`;
-		orderByClause = orderBy?.length > 0 ? orderBy : orderByClause;
+	let orderByClause: string = "";
+
+	let matchedOnce = false;
+	let lastError: Error | undefined = undefined;
+
+	const selectsPerTable = selectAllFieldsFromTables(rootTables, shape);
+
+	for (const [i, table] of rootTables.entries()) {
+		const { selects, joins: joinFromSelect } = selectsPerTable[i];
+		const selectQuery = generateSelectQuery(table, selects);
+		try {
+			const { orderBy, query } = convertRequestToQuery(
+				request,
+				tables,
+				table,
+				joinFromSelect,
+			);
+			unionBuilder += `${unionBuilder.length > 0 ? " UNION ALL " : ""} ${selectQuery} ${query}`;
+			orderByClause =
+				orderBy?.length > 0
+					? orderByClause.length > 0
+						? orderByClause + ", " + orderBy
+						: orderBy
+					: orderByClause;
+			matchedOnce = true;
+		} catch (error) {
+			if (error instanceof MissingFieldError) {
+				lastError = error;
+				continue;
+			}
+			throw error;
+		}
 	}
 
-	return `${unionBuilder} ${orderByClause ? orderByClause : ""} limit ? offset ?`;
+	if (!matchedOnce) {
+		throw lastError;
+	}
+
+	return `${unionBuilder} ${orderByClause ? "ORDER BY " + orderByClause : ""} limit ? offset ?`;
 };
@@ -1207,5 +1310,3 @@
 		if (request.sort.length > 0) {
-			if (request.sort.length > 0) {
-				orderByBuilder = "ORDER BY ";
-			}
+			orderByBuilder = "";
 			let once = false;
@@ -1414,6 +1515,3 @@ for (const sort of request.sort) {
 	if (searchSelf) {
-		const inlineName = getInlineTableFieldName(
-			path.slice(0, -1),
-			path[path.length - 1],
-		);
+		const inlineName = getInlineTableFieldName(path);
 		let field = table.fields.find((x) => x.name === inlineName);
@@ -1440,3 +1538,3 @@ if (field) {
 				// second arg is needed because of polymorphic fields we might end up here intentially to check what tables to query
-				throw new Error(
+				throw new MissingFieldError(
 					`Property with key "${key}" is not found in the schema ${JSON.stringify(schema.fields.map((x) => x.key))}`,
@@ -1495,2 +1593,5 @@ );
 	);
+	if (foreignTables.length === 0) {
+		throw new MissingFieldError("Failed to find field to join");
+	}
 	let tableToQuery: Table | undefined =
@@ -1508,6 +1609,3 @@ foreignTables[foreignTables.length - 1].table;
 		queryKeyPath.length > 0
-			? getInlineTableFieldName(
-					queryKeyPath.slice(0, -1),
-					queryKeyPath[queryKeyPath.length - 1],
-				)
+			? getInlineTableFieldName(queryKeyPath)
 			: FOREIGN_VALUE_PROPERTY;
@@ -1526,6 +1624,3 @@ return { queryKey, foreignTables };
 	// if field id represented as foreign table, do join and compare
-	const inlinedName = getInlineTableFieldName(
-		query.key.slice(0, query.key.length - 1),
-		query.key[query.key.length - 1],
-	);
+	const inlinedName = getInlineTableFieldName(query.key);
 	const tableField = table.fields.find(
@@ -1532,0 +1627,0 @@ (x) => x.name === inlinedName,

No alert changes

Improved metrics

Total package byte prevSize

6493551

increased by0.21%

Lines of code

102690

increased by0.36%

No dependency changes