Issue #23863 Fix item site search by number.

diff --git a/enyo-client/database/source/xm/javascript/item_site.sql b/enyo-client/database/source/xm/javascript/item_site.sql
index 5ddbacd..fe5879c 100644 (file)
--- a/enyo-client/database/source/xm/javascript/item_site.sql
+++ b/enyo-client/database/source/xm/javascript/item_site.sql
@@ -75,7 +75,7 @@ select xt.install_js('XM','ItemSite','xtuple', $$
       sqlCount,
       sql1 = 'select pt1.id ' +
              'from ( ' +
-             'select t1.%3$I as id {groupColumns} ' +
+             'select t1.%3$I as id{groupColumns} ' +
              'from %1$I.%2$I t1 {joins} ' +
              'where {conditions} {extra}',
       sql2 = 'select * from %1$I.%2$I where id in ({ids}) {orderBy}';
@@ -90,14 +90,14 @@ select xt.install_js('XM','ItemSite','xtuple', $$
           keySearch = param.value;
           sql1 += ' and t1.%4$I in (select item_id from item where item_number ~^ ${p1} or item_upccode ~^ ${p1}) ' +
             'union ' +
-            'select t1.%3$I as id {groupColumns} ' +
+            'select t1.%3$I as id{groupColumns} ' +
             'from %1$I.%2$I t1 {joins} ' +
             ' join itemalias on t1.%4$I=itemalias_item_id ' +
             '   and itemalias_crmacct_id is null ' +
             'where {conditions} {extra} ' +
             ' and (itemalias_number ~^ ${p1}) ' +
             'union ' +
-            'select t1.%3$I as id {groupColumns}  ' +
+            'select t1.%3$I as id{groupColumns}  ' +
             'from %1$I.%2$I t1 {joins} ' +
             ' join itemalias on t1.%4$I=itemalias_item_id ' +
             '   and itemalias_crmacct_id={accountId} ' +
@@ -218,7 +218,7 @@ select xt.install_js('XM','ItemSite','xtuple', $$
     }
 
     sql1 = XT.format(
-      sql1 += ') pt1 group by pt1.id {groupBy} {orderBy} %5$s %6$s;',
+      sql1 += ') pt1 group by pt1.id{groupBy} {orderBy} %5$s %6$s;',
       [tableNamespace, table, idColumn, backingTypeJoinColumn, limit, offset]
     );
 
@@ -234,10 +234,12 @@ select xt.install_js('XM','ItemSite','xtuple', $$
 
     /* Change table reference in group by and order by to pt1. */
     if (clause.groupByColumns && clause.groupByColumns.length) {
-      clause.groupByColumns = clause.groupByColumns.replace(/t1./g, 'pt1.');
+      clause.groupByColumns = clause.groupByColumns.replace(/ t1./g, ' pt1.');
+      clause.groupByColumns = clause.groupByColumns.replace(/ jt\d+./g, ' pt1.');
     }
     if (clause.orderByColumns && clause.orderByColumns.length) {
-      clause.orderByColumns = clause.orderByColumns.replace(/t1./g, 'pt1.');
+      clause.orderByColumns = clause.orderByColumns.replace(/ t1./g, ' pt1.');
+      clause.orderByColumns = clause.orderByColumns.replace(/ jt\d+./g, ' pt1.');
     }
     if (joinTables.length) {
       for (var j=0; j < joinTables.length; j++) {

diff --git a/test/database/joins.js b/test/database/joins.js
index c1fd25a..c0bbf5b 100644 (file)
--- a/test/database/joins.js
+++ b/test/database/joins.js
@@ -201,6 +201,19 @@ var _ = require("underscore"),
       });
     });
 
+    it('should be able to search item-site by number', function (done) {
+      var sql = 'select xt.js_init(true);select xt.post(${"nameSpace":"XM","type":"ItemSiteListItem","dispatch":{"functionName":"fetch","parameters":{"orderBy":[{"attribute":"item.number"}],"rowOffset":0,"rowLimit":50,"parameters":[{"attribute":"isActive","operator":"=","value":true},{"attribute":"item.number","operator":"MATCHES","isCharacteristic":false,"value":"yt"}]}},"encoding":"rjson","username":"admin","encryptionKey":"foo"}$);';
+
+      datasource.query(sql, creds, function (err, res) {
+        var results;
+        assert.isNull(err);
+        assert.equal(1, res.rowCount, JSON.stringify(res.rows));
+        results = JSON.parse(res.rows[1].post);
+        assert.isNumber(results.length);
+        done();
+      });
+    });
+
     it('should be able to do an item-site search with a keysearch', function (done) {
       var sql = 'select xt.js_init(true);select xt.post($${"nameSpace":"XM","type":"ItemSiteRelation","dispatch":{"functionName":"fetch","parameters":{"parameters":[{"attribute":["number","barcode"],"operator":"BEGINS_WITH","value":"BTR","keySearch":true}],"orderBy":[{"attribute":"number"},{"attribute":"barcode"}],"rowLimit":10}},"username":"admin","encryptionKey":"this is any content"}$$);';