@saltcorn/db-common - npm Package Compare versions

Comparing version 0.7.1 to 0.7.2-beta.0

dist/internal.d.ts

@@ -23,2 +23,7 @@ /**
 export declare const sqlsanitizeAllowDots: (nm: string | symbol) => string;
+declare type PlaceHolderStack = {
+    push: (x: Value) => string;
+    is_sqlite: boolean;
+    getValues: () => Value[];
+};
 export declare type Value = string | number | boolean | Date | Value[];
@@ -52,2 +57,11 @@ export declare type Where = {
 };
+export declare const subSelectWhere: (phs: PlaceHolderStack) => (k: string, v: {
+    inSelect: {
+        where: Where;
+        field: string;
+        table: string;
+        through?: string;
+        valField?: string;
+    };
+}) => string;
 declare type WhereAndVals = {
@@ -122,3 +136,4 @@ where: string;
 };
+export declare const prefixFieldsInWhere: (inputWhere: any, tablePrefix: string) => Where;
 export {};
 //# sourceMappingURL=internal.d.ts.map

dist/internal.js

@@ -7,3 +7,3 @@ "use strict";
 Object.defineProperty(exports, "__esModule", { value: true });
-exports.mkSelectOptions = exports.orderByIsObject = exports.mkWhere = exports.sqlsanitizeAllowDots = exports.sqlsanitize = void 0;
+exports.prefixFieldsInWhere = exports.mkSelectOptions = exports.orderByIsObject = exports.mkWhere = exports.subSelectWhere = exports.sqlsanitizeAllowDots = exports.sqlsanitize = void 0;
 //https://stackoverflow.com/questions/15300704/regex-with-my-jquery-function-for-sql-variable-name-validation
@@ -93,2 +93,4 @@ /**
         .join(" || ' ' || ");
+    const prefixMatch = !v.searchTerm?.includes(" ");
+    const searchTerm = prefixMatch ? `${v.searchTerm}:*` : v.searchTerm;
     if (flds === "")
@@ -99,18 +101,23 @@ flds = "''";
     else
-        return `to_tsvector('english', ${flds}) @@ plainto_tsquery('english', ${phs.push(v.searchTerm)})`;
+        return `to_tsvector('english', ${flds}) @@ ${prefixMatch ? "" : `plain`}to_tsquery('english', ${phs.push(searchTerm)})`;
 };
-/**
- *
- * @param {boolean} is_sqlite
- * @param {string} i
- * @returns {function}
- */
 const subSelectWhere = (phs) => (k, v) => {
-    const whereObj = v.inSelect.where;
-    const wheres = whereObj ? Object.entries(whereObj) : [];
-    const where = whereObj && wheres.length > 0
-        ? "where " + wheres.map(whereClause(phs)).join(" and ")
-        : "";
-    return `${quote((0, exports.sqlsanitizeAllowDots)(k))} in (select ${v.inSelect.field} from ${v.inSelect.table} ${where})`;
+    if (v.inSelect.through && v.inSelect.valField) {
+        const whereObj = (0, exports.prefixFieldsInWhere)(v.inSelect.where, "ss2");
+        const wheres = whereObj ? Object.entries(whereObj) : [];
+        const where = whereObj && wheres.length > 0
+            ? "where " + wheres.map(whereClause(phs)).join(" and ")
+            : "";
+        return `${quote((0, exports.sqlsanitizeAllowDots)(k))} in (select ss1."${v.inSelect.valField}" from ${v.inSelect.table} ss1 join ${v.inSelect.through} ss2 on ss2.id = ss1."${v.inSelect.field}" ${where})`;
+    }
+    else {
+        const whereObj = v.inSelect.where;
+        const wheres = whereObj ? Object.entries(whereObj) : [];
+        const where = whereObj && wheres.length > 0
+            ? "where " + wheres.map(whereClause(phs)).join(" and ")
+            : "";
+        return `${quote((0, exports.sqlsanitizeAllowDots)(k))} in (select "${v.inSelect.field}" from ${v.inSelect.table} ${where})`;
+    }
 };
+exports.subSelectWhere = subSelectWhere;
 /**
@@ -187,3 +194,3 @@ * @param {string} s
                                             : typeof (v || {}).inSelect !== "undefined"
-                                                ? subSelectWhere(phs)(k, v)
+                                                ? (0, exports.subSelectWhere)(phs)(k, v)
                                                 : typeof (v || {}).json !== "undefined"
@@ -263,2 +270,23 @@ ? phs.is_sqlite
 exports.mkSelectOptions = mkSelectOptions;
+const prefixFieldsInWhere = (inputWhere, tablePrefix) => {
+    if (!inputWhere)
+        return {};
+    const whereObj = {};
+    Object.keys(inputWhere).forEach((k) => {
+        if (k === "_fts")
+            whereObj[k] = { table: tablePrefix, ...inputWhere[k] };
+        else if (k === "not") {
+            whereObj.not = (0, exports.prefixFieldsInWhere)(inputWhere[k], tablePrefix);
+        }
+        else if (k === "or") {
+            whereObj.or = Array.isArray(inputWhere[k])
+                ? inputWhere[k].map((w) => (0, exports.prefixFieldsInWhere)(w, tablePrefix))
+                : (0, exports.prefixFieldsInWhere)(inputWhere[k], tablePrefix);
+        }
+        else
+            whereObj[`${tablePrefix}."${k}"`] = inputWhere[k];
+    });
+    return whereObj;
+};
+exports.prefixFieldsInWhere = prefixFieldsInWhere;
 //# sourceMappingURL=internal.js.map

dist/internal.test.js

@@ -142,8 +142,17 @@ "use strict";
         expect(mkWhere({
-            _fts: { fields: [fld("name"), fld("description")], searchTerm: "foo" },
+            _fts: {
+                fields: [fld("name"), fld("description")],
+                searchTerm: "foo bar",
+            },
         })).toStrictEqual({
-            values: ["foo"],
+            values: ["foo bar"],
             where: `where to_tsvector('english', coalesce("name",'') || ' ' || coalesce("description",'')) @@ plainto_tsquery('english', $1)`,
         });
         expect(mkWhere({
+            _fts: { fields: [fld("name"), fld("description")], searchTerm: "foo" },
+        })).toStrictEqual({
+            values: ["foo:*"],
+            where: `where to_tsvector('english', coalesce("name",'') || ' ' || coalesce("description",'')) @@ to_tsquery('english', $1)`,
+        });
+        expect(mkWhere({
             _fts: {
@@ -163,3 +172,3 @@ fields: [fld("name"), fld("description")],
             values: [7],
-            where: 'where "id" in (select bar from foo where "baz"=$1)',
+            where: 'where "id" in (select "bar" from foo where "baz"=$1)',
         });
@@ -172,3 +181,3 @@ expect(mkWhere({
             values: [45, 7, "Alice"],
-            where: `where "age"=$1 and "id" in (select bar from foo where "baz"=$2) and "name"=$3`,
+            where: `where "age"=$1 and "id" in (select "bar" from foo where "baz"=$2) and "name"=$3`,
         });
@@ -175,0 +184,0 @@ });

package.json

 {
   "name": "@saltcorn/db-common",
-  "version": "0.7.1",
+  "version": "0.7.2-beta.0",
   "description": "Db common structures for Saltcorn, open-source no-code platform",
@@ -5,0 +5,0 @@ "homepage": "https://saltcorn.com",

No alert changes

Improved metrics

Total package byte prevSize

81166

increased by5.66%

Lines of code

894

increased by6.18%

No dependency changes