@forty-boy/sql - npm Package Compare versions

Comparing version 1.0.3 to 1.0.4

lib/examples/example.js

@@ -120,24 +120,51 @@ "use strict";
     */
-    // userTable
+    // productTable
     //   .join({
     //     joinType: 'INNER JOIN',
-    //     tableName: productTable.tableName,
-    //     columnsToSelect: ['id', 'name'],
-    //     columnsOn: { createdBy: 'id' },
+    //     columnsToSelect: [
+    //       { column: 'name' },
+    //       { column: 'price' },
+    //       { column: 'fullName', as: 'userName', table: userTable.tableName },
+    //       { column: 'dateOfBirth', table: userTable.tableName },
+    //     ],
+    //     columnsOn: [
+    //       {
+    //         from: { column: 'id', table: userTable.tableName },
+    //         to: { column: 'createdBy', table: productTable.tableName },
+    //       },
+    //     ],
     //   })
     //   .subscribe((res) => console.log(res));
-    // userTable
+    // productTable
     //   .join({
     //     joinType: 'LEFT JOIN',
-    //     tableName: productTable.tableName,
-    //     columnsToSelect: ['id', 'name'],
-    //     columnsOn: { createdBy: 'id' },
+    //     columnsToSelect: [
+    //       { column: 'name' },
+    //       { column: 'price' },
+    //       { column: 'fullName', as: 'userName', table: userTable.tableName },
+    //       { column: 'dateOfBirth', table: userTable.tableName },
+    //     ],
+    //     columnsOn: [
+    //       {
+    //         from: { column: 'id', table: userTable.tableName },
+    //         to: { column: 'createdBy', table: productTable.tableName },
+    //       },
+    //     ],
     //   })
     //   .subscribe((res) => console.log(res));
-    // userTable
+    // productTable
     //   .join({
     //     joinType: 'RIGHT JOIN',
-    //     tableName: productTable.tableName,
-    //     columnsToSelect: ['id', 'name'],
-    //     columnsOn: { createdBy: 'id' },
+    //     columnsToSelect: [
+    //       { column: 'name' },
+    //       { column: 'price' },
+    //       { column: 'fullName', as: 'userName', table: userTable.tableName },
+    //       { column: 'dateOfBirth', table: userTable.tableName },
+    //     ],
+    //     columnsOn: [
+    //       {
+    //         from: { column: 'id', table: userTable.tableName },
+    //         to: { column: 'createdBy', table: productTable.tableName },
+    //       },
+    //     ],
     //   })
@@ -144,0 +171,0 @@ //   .subscribe((res) => console.log(res));

lib/index.js

 "use strict";
-const example_1 = require("./examples/example");
 const sql_service_1 = require("./services/sql.service");
 const table_1 = require("./table");
-(0, example_1.RunExample)();
 module.exports = { Table: table_1.Table, SqlService: sql_service_1.SqlService };
 //# sourceMappingURL=index.js.map

lib/models/sql.model.d.ts

@@ -21,2 +21,19 @@ export declare type SqlStringType = 'CHAR' | 'VARCHAR' | 'BINARY' | 'VARBINARY' | 'TINYBLOB' | 'TINYTEXT' | 'TEXT' | 'BLOB' | 'MEDIUMTEXT' | 'MEDIUMBLOB' | 'LONGTEXT' | 'LONGBLOB' | 'ENUM' | 'SET';
 }
+export interface ColumnToSelect {
+    column: string;
+    as?: string;
+    table?: string;
+}
+export interface ColumnOn {
+    from: OnTableColumn;
+    to: OnTableColumn;
+}
+export interface OnTableColumn {
+    column: string;
+    table: string;
+}
+export interface JoinOrderBy {
+    column: string;
+    table: string;
+}
 export interface SqlWhereQuery {
@@ -41,5 +58,6 @@ columns?: Array<string>;
     joinType: SqlJoinType;
-    tableName: string;
-    columnsToSelect: Array<string>;
-    columnsOn: any;
+    columnsToSelect: Array<ColumnToSelect>;
+    columnsOn: Array<ColumnOn>;
+    orderBy?: Array<JoinOrderBy>;
+    asc?: boolean;
 }
@@ -46,0 +64,0 @@ export interface SqlUnionQuery {

lib/services/sql.service.d.ts

 import mysql from 'mysql2';
-import { TableColumn, SqlWhereQuery, SqlUpdateQuery, SqlJoinQuery, SqlUnionQuery } from '../models/sql.model';
+import { TableColumn, SqlWhereQuery, SqlUpdateQuery, SqlUnionQuery, SqlJoinQuery } from '../models/sql.model';
 export declare class SqlService {
@@ -14,2 +14,3 @@ private _tableName;
     createJoinQuery(sqlQuery: SqlJoinQuery): string;
+    private _sequelizeJoin;
     createUnionQuery(sqlQuery: SqlUnionQuery): string;
@@ -19,5 +20,4 @@ private _sequelizeCreateColumns;
     private _sequelizeWhere;
-    private _sequelizeJoin;
     private _sequelizeUnion;
     private _addIfLastIteration;
 }

lib/services/sql.service.js

@@ -47,3 +47,2 @@ "use strict";
         var _a;
-        console.log(sqlQuery.condition);
         const updateQuery = this._sequelizeColumns(sqlQuery.values);
@@ -58,5 +57,27 @@ return `UPDATE ${this._tableName} SET ${updateQuery} ${(_a = this._sequelizeWhere(sqlQuery)) !== null && _a !== void 0 ? _a : ''}`;
     createJoinQuery(sqlQuery) {
-        const query = this._sequelizeJoin(sqlQuery.joinType, sqlQuery.tableName, sqlQuery.columnsToSelect, sqlQuery.columnsOn);
+        const query = this._sequelizeJoin(sqlQuery.joinType, sqlQuery.columnsOn, sqlQuery.columnsToSelect, sqlQuery.orderBy, sqlQuery.asc);
         return `${query}`;
     }
+    _sequelizeJoin(joinType, columnsOn, columnsToSelect, orderBy, asc) {
+        if (!joinType)
+            return;
+        let result = 'SELECT ';
+        columnsToSelect.forEach((column, index) => {
+            result += `${column.table
+                ? `${column.table}.${column.column}`
+                : `${this._tableName}.${column.column}`} ${(column === null || column === void 0 ? void 0 : column.as) ? `as ${column.as}` : ''} ${this._addIfLastIteration(columnsToSelect, index, ', ')}`;
+        });
+        result += `FROM ${this._tableName} `;
+        columnsOn.forEach((column) => {
+            result += `${joinType} ${column.from.table} ON ${column.from.table}.${column.from.column} = ${column.to.table}.${column.to.column} `;
+        });
+        if (orderBy === null || orderBy === void 0 ? void 0 : orderBy.length)
+            result += 'ORDER BY ';
+        orderBy === null || orderBy === void 0 ? void 0 : orderBy.forEach((order, index) => {
+            result += `${order.table}.${order.column} ${this._addIfLastIteration(orderBy, index, ', ')}`;
+        });
+        if ((orderBy === null || orderBy === void 0 ? void 0 : orderBy.length) && (asc === true || asc === false))
+            result += `${asc === true ? 'ASC' : asc === false ? 'DESC' : ''}`;
+        return result;
+    }
     createUnionQuery(sqlQuery) {
@@ -91,3 +112,2 @@ const query = this._sequelizeUnion(sqlQuery);
     _sequelizeWhere(query) {
-        console.log(query);
         if (!query || !query.condition)
@@ -114,17 +134,2 @@ return query.limit ? ` LIMIT ${query.limit}` : '';
     }
-    _sequelizeJoin(joinType, tableName, columnsToSelect, columnsOn) {
-        if (!tableName || !columnsOn || !columnsOn)
-            return;
-        let result = 'SELECT ';
-        const fromAlias = tableName[0];
-        const onAlias = this._tableName[0];
-        columnsToSelect.forEach((column, index) => {
-            result += `${fromAlias}.${column} ${this._addIfLastIteration(columnsToSelect, index, ', ')}`;
-        });
-        result += `FROM ${tableName} as ${fromAlias} ${joinType} ${this._tableName} as ${onAlias} ON `;
-        Object.entries(columnsOn).forEach((key) => {
-            result += `${fromAlias}.${key[0]} = ${onAlias}.${columnsOn[key[0]]}`;
-        });
-        return result;
-    }
     _sequelizeUnion(sqlQuery) {
@@ -131,0 +136,0 @@ var _a;

lib/table.js

@@ -16,3 +16,2 @@ "use strict";
         const result = new rxjs_1.Subject();
-        console.log('query', query);
         this._sqlService.pool.query(query, (err, rows, fields) => {
@@ -44,3 +43,2 @@ if (err)
         const result = new rxjs_1.Subject();
-        console.log(query);
         this._sqlService.pool.query(query, Object.values(sqlQuery.values), (err, rows, fields) => {
@@ -47,0 +45,0 @@ if (err)

package.json

 {
   "name": "@forty-boy/sql",
-  "version": "1.0.3",
+  "version": "1.0.4",
   "description": "A MySQL Library for Node.js",
@@ -5,0 +5,0 @@ "main": "lib/index.js",

README.md

@@ -1,2 +0,2 @@
-# @Forty/SQL
+# @Forty-boy/SQL
 
@@ -26,2 +26,6 @@ A MySQL Library for Node.js
 
+### Version 1.0.4
+
+- [Fixed issue with Join method](https://github.com/blaze-rowland/forty-sql/issues/7)
+
 ### Version 1.0.3
@@ -213,8 +217,17 @@
 ```
-userTable
+  productTable
   .join({
     joinType: 'INNER JOIN',
-    tableName: productTable.tableName,
-    columnsToSelect: ['id', 'name'],
-    columnsOn: { createdBy: 'id' },
+    columnsToSelect: [
+      { column: 'name' },
+      { column: 'price' },
+      { column: 'fullName', as: 'userName', table: userTable.tableName },
+      { column: 'dateOfBirth', table: userTable.tableName },
+    ],
+    columnsOn: [
+      {
+        from: { column: 'id', table: userTable.tableName },
+        to: { column: 'createdBy', table: productTable.tableName },
+      },
+    ],
   })
@@ -225,8 +238,17 @@ .subscribe((res) => console.log(res));
 ```
-userTable
+  productTable
   .join({
     joinType: 'LEFT JOIN',
-    tableName: productTable.tableName,
-    columnsToSelect: ['id', 'name'],
-    columnsOn: { createdBy: 'id' },
+    columnsToSelect: [
+      { column: 'name' },
+      { column: 'price' },
+      { column: 'fullName', as: 'userName', table: userTable.tableName },
+      { column: 'dateOfBirth', table: userTable.tableName },
+    ],
+    columnsOn: [
+      {
+        from: { column: 'id', table: userTable.tableName },
+        to: { column: 'createdBy', table: productTable.tableName },
+      },
+    ],
   })
@@ -237,8 +259,17 @@ .subscribe((res) => console.log(res));
 ```
-userTable
+  productTable
   .join({
     joinType: 'RIGHT JOIN',
-    tableName: productTable.tableName,
-    columnsToSelect: ['id', 'name'],
-    columnsOn: { createdBy: 'id' },
+    columnsToSelect: [
+      { column: 'name' },
+      { column: 'price' },
+      { column: 'fullName', as: 'userName', table: userTable.tableName },
+      { column: 'dateOfBirth', table: userTable.tableName },
+    ],
+    columnsOn: [
+      {
+        from: { column: 'id', table: userTable.tableName },
+        to: { column: 'createdBy', table: productTable.tableName },
+      },
+    ],
   })
@@ -245,0 +276,0 @@ .subscribe((res) => console.log(res));

No alert changes

Improved metrics

Total package byte prevSize

54280

increased by20.35%

Number of package files

32

increased by10.34%

Lines of code

806

increased by24.77%

Number of lines in readme file

296

increased by11.7%

No dependency changes