mql2 - npm Package Compare versions

Comparing version 0.1.8 to 0.1.9

ljoin.js

@@ -6,2 +6,4 @@ import {
 
+import { plural, singular } from 'pluralize';
+
 export default async function load_ljoin({
@@ -47,12 +49,24 @@ ready_sqls, add_column, tag, MQL_DEBUG,
     const query = left.query();
-    if (query && query.text) query.text = 'AND ' + query.text.replace(/WHERE|AND/i, '');
-    return QUERY`
-    SELECT ${CL(...colums)}
-    FROM ${TB(left.table)} AS ${TB(left.as)}
-    ${where_in}
-    ${tag(query)}
-    `;
+    if (query && query.text) query.text = query.text.replace(/^\s*WHERE/i, 'AND');
+    return left.row_number.length == 2 ?
+      QUERY `
+      SELECT *
+      FROM (
+        SELECT
+          ${COLUMN(...colums)}, 
+          ROW_NUMBER() OVER (PARTITION BY ${CL(left.key)} ORDER BY ${left.row_number[1]}) as "--row_number--"
+        FROM ${TB(left.table)} AS ${TB(left.as)}       
+        ${where_in || tag()}
+      ) AS "--row_number_table--"
+      WHERE "--row_number_table--"."--row_number--"<=${left.row_number[0]}`
+      :
+      QUERY`
+      SELECT ${CL(...colums)}
+      FROM ${TB(left.table)} AS ${TB(left.as)}
+      ${where_in || tag()}
+      ${tag(query)}
+    `
   }
 
-  function left_join_query(left, where_in_query, QUERY) {
+  function left_join_query(left, where_in, QUERY) {
     let i = 0;
@@ -71,8 +85,9 @@ left.lj_as = 'lj'+ i++ + "//"+left.depth;
           right.lj_as = 'lj'+ i++ + "//"+right.depth;
-          if (query && query.text) query.text = 'AND ' + query.text.replace(/WHERE|AND/i, '');
+          if (query && query.text) query.text = query.text.replace(/^\s*WHERE/i, 'AND');
           join_columns.push(
             uniq(add_as_join(right, right.lj_as).originals
               .concat(right.as + '.' + right.key + ' AS ' + `${right.lj_as}>_<${right.key}`)
-              .concat(left.as + '.id' + ' AS ' + `${right.lj_as}>_<id`))
+              .concat(right.as + '.id' + ' AS ' + `${right.lj_as}>_<id`))
           );
+
           join_sqls.push(SQL `
@@ -93,11 +108,26 @@ LEFT JOIN
         if (!query) return query;
-        query.text = (where_in_query ? 'AND ' : 'WHERE ') + query.text.replace(/WHERE|AND/i, '');
+        query.text = query.text.replace(/^\s*WHERE/i, where_in ? 'AND' : 'WHERE');
         return query;
       },
-      (query) => QUERY `
-        SELECT ${COLUMN(...cat(join_columns))}
-        FROM ${TB(left.table)} AS ${TB(left.as)}
-        ${SQLS(join_sqls)}
-        ${where_in_query || tag()}
-        ${tag(query)}`,
+      (query) => left.row_number.length == 2  ?
+        QUERY `
+        SELECT "--row_number_table--".*
+        FROM (
+          SELECT 
+            ${COLUMN(...cat(join_columns))},
+            ROW_NUMBER() OVER (PARTITION BY ${CL(left.as + '.' + left.key)} ORDER BY ${left.row_number[1]}) as "--row_number--"
+          FROM ${TB(left.table)} AS ${TB(left.as)}
+          ${SQLS(join_sqls)}
+          ${where_in || tag()}
+          ${tag(query)}
+        ) AS "--row_number_table--"
+        WHERE "--row_number_table--"."--row_number--"<=${left.row_number[0]}`
+        :
+        QUERY `
+          SELECT ${COLUMN(...cat(join_columns))}
+          FROM ${TB(left.table)} AS ${TB(left.as)}
+          ${SQLS(join_sqls)}
+          ${where_in || tag()}
+          ${tag(query)}`,
+      left.row_number.length == 2 ? map(r => delete r['--row_number--'] && r) : r => r,
       map(row => {
@@ -134,2 +164,3 @@ const before_result_obj = {};
           option.rels = [];
+          option.row_number = option.row_number || [];
         }),
@@ -142,3 +173,3 @@ ([left, ...rest]) => {
             if (me.rel_type == '-') {
-              me.left_key = me.left_key || (me.is_poly ? 'id' : me.table.substr(0, me.table.length-1) + '_id');
+              me.left_key = me.left_key || (me.is_poly ? 'id' : singular(me.table) + '_id');
               me.key = me.key || (me.is_poly ? 'attached_id' : 'id');
@@ -148,3 +179,3 @@ left.left_joins.push(me);
               me.left_key = me.left_key || 'id';
-              me.key = me.key || (me.is_poly ? 'attached_id' : left.table.substr(0, left.table.length-1) + '_id');
+              me.key = me.key || (me.is_poly ? 'attached_id' : singular(left.table) + '_id');
             }
@@ -171,8 +202,8 @@ left.rels.push(me);
         function recur([left, results]) {
+          if (reject(r=>r, results).length) return ;
           return go(
             left.rels,
             cmap(async function(me) {
-              const next_result = cat(map(r => r._ ? r._[me.as] : null, results));
               const f_key_ids = uniq(filter((r) => !!r, pluck(me.left_key, results)));
-              if (me.rel_type == '-' || !f_key_ids.length) return recur([me, next_result]);
+              if (me.rel_type == '-' || !f_key_ids.length) return recur([me, cat(map(r => r._ ? r._[me.as] : null, results))]);
               return go(
@@ -179,0 +210,0 @@ (!me.left_join_over && me.left_joins.length ?

mql.js

@@ -40,3 +40,2 @@ import {
 
-
 const add_s = word => last(word) == 'y'
@@ -132,3 +131,2 @@
     if (queries.find(is_injection)) return SymbolInjection;
-
     var query = reduce((res, query) => {
@@ -279,2 +277,3 @@ if (!query) return res;
           option.rels = [];
+          option.row_number = option.row_number || [];
         }),
@@ -315,3 +314,2 @@ function setting([left, ...rest]) {
               FROM ${TB(me.table)} AS ${TB(me.as)} ${me.query}`;
-
           return go(
@@ -322,3 +320,3 @@ [lefts, me],
                 const query = me.query();
-                if (query && query.text) query.text = 'AND ' + query.text.replace(/WHERE|AND/i, '');
+                if (query && query.text) query.text = query.text.replace(/^\s*WHERE/i, 'AND');
 
@@ -328,19 +326,29 @@ var fold_key = me.rel_type == 'x' ?
 
-                var colums = uniq(add_column(me).originals.concat(me.where_key + (me.rel_type == 'x' ? ` AS ${fold_key}` : '')));
+                const colums = uniq(add_column(me).originals.concat(me.as +'.'+me.where_key + (me.rel_type == 'x' ? ` AS ${fold_key}` : '')));
 
-                var in_vals = filter(a => a != null, pluck(me.left_key, lefts));
-
-                const rights = !in_vals.length ? [] : await QUERY `
+                const in_vals = filter(a => a != null, pluck(me.left_key, lefts));
+                const is_row_num = me.row_number.length == 2;
+                const rights = (!in_vals.length ? [] : await (is_row_num ?
+                  QUERY `
+                  SELECT *
+                  FROM (
+                    SELECT
+                      ${COLUMN(...colums)}, 
+                      ROW_NUMBER() OVER (PARTITION BY ${CL(me.where_key)} ORDER BY ${me.row_number[1]}) as "--row_number--"
+                    FROM ${TB(me.table)} AS ${TB(me.as)} 
+                    ${me.xjoin} 
+                    WHERE ${IN(me.as +'.'+me.where_key, in_vals)} ${me.poly_type} ${tag(query)}
+                  ) AS "--row_number_table--"
+                  WHERE "--row_number_table--"."--row_number--"<=${me.row_number[0]}`
+                  :
+                  QUERY `
                   SELECT ${COLUMN(...colums)}
                     FROM ${TB(me.table)} AS ${TB(me.as)} 
                     ${me.xjoin} 
-                    WHERE 
-                      ${IN(me.where_key, in_vals)}
-                      ${me.poly_type}
-                      ${tag(query)}`;
+                    WHERE ${IN(me.where_key, in_vals)} ${me.poly_type} ${tag(query)}`));
 
-                var [folder, default_value] = me.rel_type == '-' ? [index_by, () => ({})] : [group_by, () => []];
-
+                const [folder, default_value] = me.rel_type == '-' ? [index_by, () => ({})] : [group_by, () => []];
                 return go(
                   rights,
+                  is_row_num ? map(r => delete r['--row_number--'] && r) : r => r,
                   folder(a => a[fold_key]),
@@ -393,3 +401,3 @@ folded => each(function(left) {
     return {
-      VALUES, IN, NOT_IN, EQ, SET, COLUMN, CL, TABLE, TB, SQL, MQL_DEBUG,
+      VALUES, IN, NOT_IN, EQ, SET, COLUMN, CL, TABLE, TB, SQL, SQLS, MQL_DEBUG,
       QUERY,
@@ -426,3 +434,3 @@ QUERY1,
 
-  return { CONNECT, VALUES, IN, NOT_IN, EQ, SET, COLUMN, CL, TABLE, TB, SQL, MQL_DEBUG }
+  return { CONNECT, VALUES, IN, NOT_IN, EQ, SET, COLUMN, CL, TABLE, TB, SQL, SQLS, MQL_DEBUG }
 }
@@ -429,0 +437,0 @@

package.json

 {
   "name": "mql2",
-  "version": "0.1.8",
+  "version": "0.1.9",
   "description": "query builder",
@@ -5,0 +5,0 @@ "main": "index.js",

No alert changes

Improved metrics

Total package byte prevSize

34163

increased by6.03%

Lines of code

616

increased by7.32%

No dependency changes