Grouping in dataGrid component.

Forum to discuss the new web client version of Servoy.

Grouping in dataGrid component.

Postby jay.rao » Tue Apr 18, 2017 3:16 pm

Hello Team,
I have few questions regarding server side filtering and grouping.Actually I would like to achieve grouping functionality using DevEX Data Grid,I am able to sort the data using $scope.model.foundset.sort(" dp0","asc ") but now as I know servoy provides this capablity of applying sort directly to database so I would like to know if its possible for us to create same functionality that would directly help us to fire the sql query for grouping from the angular component itself.Please find below example for sample code for sorting.
Code: Select all
controller: function($scope, $element, $attrs) {
         $scope.data=[];
         $scope.isRefreshed=false;
         $scope.isFirstTime=true;
         $scope.isSortingFirstTime=true;
         $scope.isSortingChanged=false;
         $scope.isGroupByFirstTime=true;
         $scope.prevSort='';
         var startIndex='';
         var endIndex='';
         var groupByKey='';
         var data=new DevExpress.data.CustomStore({
            load:function(loadOptions){
                    var fs = $scope.model.foundset;
                  var deferred = new $.Deferred();
                  if(loadOptions.sort){
                     var sqlSortDirection = "asc";
                  var columnName=loadOptions.sort[0].selector;
                  if(loadOptions.sort[0].desc==false){
                     sqlSortDirection="asc";    
                  }
                  else{
                     sqlSortDirection="desc";
                  }
                  fs.sortColumns=columnName+ " " +sqlSortDirection;
                  if($scope.prevSort!=fs.sortColumns){
                     $scope.isSortingChanged=true;
                  }                 
                    
                     if($scope.isSortingFirstTime||$scope.isSortingChanged){
                        $scope.grid.beginCustomLoading();
                        fs.sort([{ name: columnName, direction: sqlSortDirection }]);
                        $scope.prevSort=fs.sortColumns;
                        fs.loadExtraRecordsAsync(0).then(function(){
                           var aa=fs.viewPort.rows;
                           deferred.resolve(fs.viewPort.rows);    
                          // alert("Rows after sorting :"+fs.viewPort.rows.length);
                           $scope.isSortingFirstTime=false;
                           $scope.isSortingChanged=false;
                           $scope.grid.endCustomLoading();
                        return deferred.promise();
                        }); 
                     }                    
                     else{
                        if(loadOptions.skip==fs.viewPort.size-50){
                        startIndex=fs.viewPort.size;
                        $scope.data=[];
                        fs.sort([{ name: columnName, direction: sqlSortDirection }]);
                        fs.loadExtraRecordsAsync(100).then(function(){
                           endIndex =fs.viewPort.size;
                           for(var i=startIndex;i<endIndex;i++){
                               $scope.data.push(fs.viewPort.rows[i]);
                            }
                            
                           deferred.resolve($scope.data);    
                           alert("Rows after sorting :"+fs.viewPort.rows.length);
                           $scope.isSortingFirstTime=false;
                        return deferred.promise();
                        });
                        }
                     }
                     return deferred.promise();
                  }
                 else [color=#FF0000]if(loadOptions.group){
                     if($scope.isGroupByFirstTime){
                        groupByKey=loadOptions.group[0].selector;
                        loadOptions.group[0].isExpanded=true;
                        $scope.data = DevExpress.data.query(fs.viewPort.rows)
                         .groupBy(groupByKey)
                        .toArray();
                        $scope.isGroupByFirstTime=false;
                        }
                       deferred.resolve($scope.data);
                       return deferred.promise(); [/color]
                  }
                 else
                 {
                    startIndex=fs.viewPort.size;
                    if($scope.isFirstTime){
                       $scope.data=$scope.data.concat(fs.viewPort.rows);
                       }
                    else{
                       $scope.data=[];
                    }
                if(!$scope.isRefreshed){
                          return fs.loadExtraRecordsAsync(100).then(function(){
                          endIndex =fs.viewPort.size;
                          for(var i=startIndex;i<endIndex;i++){
                            $scope.data.push(fs.viewPort.rows[i]);
                         }
                         if(loadOptions.group){
                            groupByKey=loadOptions.group[0].selector;
                            loadOptions.group[0].isExpanded=true;
                            $scope.data = DevExpress.data.query(fs.viewPort.rows)
                            .groupBy(groupByKey)
                           .toArray();
                             }
                          deferred.resolve($scope.data);
                          $scope.isFirstTime=false;
                          alert("Rows:"+fs.viewPort.rows.length);
                          return deferred.promise();
                             });
                    };
                   
                 }
               
            }
         });}



In the above highlighted part we are trying to group at client side but now similar to sorting we would like to achieve that for grouping.We donot want to use handler to pass the parameter to the form.js and manage database call as it would make it less resuable,also the foundset returned from loadAllRecords() and that of $scope.model.foundset.viewPort.rows would be quite different thus it would be difficult to manage.
jay.rao
 
Posts: 59
Joined: Mon Apr 10, 2017 1:32 pm

Re: Grouping in dataGrid component.

Postby paronne » Tue Apr 18, 2017 3:49 pm

Hi,

very interesting feature the serverside grouping you are looking at. There is not such client side api to perform grouping similarly to how you sort records in foundset. At the moment using handlers to manage grouping is your only option.

For your information during the next Servoy World (17-20 May) there will be a session on advanced grid features, such as grouping. I suggest you to join the Servoy World event, it can be a good opportunity to get inspiration and develop ideas related to this topic. You can get more information about Servoy World at https://servoy.com/servoyworld2017/

Regards,
Paolo
paronne
 
Posts: 202
Joined: Fri Nov 02, 2012 3:21 pm

Re: Grouping in dataGrid component.

Postby jay.rao » Wed Apr 19, 2017 6:49 am

Hello Paolo,
Thank you for your time and invitation.So as we do not have client side api we would look into your suggestion and let you know.



Thank you
Globis_Jay
jay.rao
 
Posts: 59
Joined: Mon Apr 10, 2017 1:32 pm

Re: Grouping in dataGrid component.

Postby paronne » Wed Apr 19, 2017 8:31 am

You are welcome
paronne
 
Posts: 202
Joined: Fri Nov 02, 2012 3:21 pm

Re: Grouping in dataGrid component.

Postby Andrei Costescu » Wed Apr 19, 2017 9:39 am

Until you get to ServoyWorld :), something to chew on:

Servoy 8.1.3 Has property types "foundsetRef" and "rowRef".
https://wiki.servoy.com/display/public/ ... erty+Types

Those can be used as described here:
https://wiki.servoy.com/display/public/ ... dset-usage

These were created to handle exactly this kind of scenarios (such as server-side grouping, tree components, ...).
Andrei Costescu
Servoy
Andrei Costescu
 
Posts: 1018
Joined: Tue Jun 26, 2007 3:14 pm

Re: Grouping in dataGrid component.

Postby jay.rao » Thu Apr 20, 2017 9:00 am

Hello Andrei,
Thank you for the suggestion I am looking into it.
As per your suggestion I have few questions which may help me achieve what I want.
Currently in my grid my
Code: Select all
dataSource: fs.viewPort.rows
where rows includes
Code: Select all
dp0,dp1,_svy_rowId which are article_code,article_id respectively
Capture.PNG

So lets say I go for grouping/searching at ServerSide, I pass all the required parameters to my handler and next to serverSide js and fire the sql query for grouping or filtering, the query would return the data in foundset which now includes rows with columns article_code,additonal_goods etc (it returns all the fields and not the fields which are strictly included in grid's column)
fs.PNG


So is there any way to pass this foundset to my client side's $scope.model.foundset.viewPort.rows which includes only dp0,dp1 i.e mapping the serverSide's foundset(article_code,article_id) with the clientSIde foundset(dp0,dp1)?


Also take a look at my client side $scope.data,where I 've grouped my column. So is there any possiblity to group at server side and pass the foundset to the client and then restructure the foundset's row to my original client side's $scope.data
grouping.PNG
You do not have the required permissions to view the files attached to this post.
jay.rao
 
Posts: 59
Joined: Mon Apr 10, 2017 1:32 pm

Re: Grouping in dataGrid component.

Postby Andrei Costescu » Thu Apr 20, 2017 9:36 am

I guess here it depends a lot on how you want/need to do it.

So you do all those things then you do the query on server side.
Then you said you want to "pass this foundset to my client side's $scope.model.foundset.viewPort.rows which includes only dp0,dp1". Here (I guess it might also depend on what the action was, grouping or filtering) I see two options. Do you want to use only one foundset to do everything or multiple foundsets? If in the scenario you thought about you only want $scope.model.foundset to change then:
  • if the foundset instance remains the same on server and you need the same dps - you don't have to do anything, just populate the foundset with the data you want - it will get updated on client automatically
  • if you query in a separare/different foundset then you can assign the new server side foundset value to the property using what foundset property provides. So for example

    Code: Select all
    elements.myFoundsetBasedBean.foundset = {
                foundset: myNewServerSideFoundset,
                dataproviders: {
                    dp1 : "article_code",
                    dp2 : "article_id"
                },
                sendSelectionViewportInitially: false,
                initialPreferredViewPortSize: 15
    };


  • if you need to send the new foundset to the client besides the foundset(s) you already have sent you just add another property to your bean that holds an array with foundsets and foundset reference ids and add the value to that property, then return the server foundset from server-side component scripting (return type in spec file for that api would be "foundsetRef"). So what you see there in the example from the link in previous post - here is a simplified version (doesn't list server side call params that you might need and recordRef type usage, doesn't list specific query logic):

    spec file:
    Code: Select all
        "serverscript": "mycomppck/mycompname/mycomp_server.js",
    (...)
        "model":
        {
            "hashedFoundsets": { "type": "hashedFoundset[]", "default": [] },
            (...)
        },
        "types":
        {
            (...),
            "hashedFoundset" : {
                "foundset": "foundset",
                "foundsetUUID": "foundsetRef"
            } 
        },
        "api" : {
            "queryAndGetNewFoundsetFromServer" : {
                "returns" : "foundsetRef",
                "parameters" :
                [
                    (...)
                ]
            },
    (...)



    mycomp_server.js:
    Code: Select all
    (...)
    $scope.getGroupedChildFoundsetUUID = function(...) {
            // execute query and get new foundset

             
            $scope.model.hashedFoundsets.push({ foundset: {
                foundset: myNewServerSideFoundset,
                dataproviders: {
                    dp1 : "article_code",
                    dp2 : "article_id"
                },
                sendSelectionViewportInitially: false,
                initialPreferredViewPortSize: 15
            }, foundsetUUID: myNewServerSideFoundset}); // send it to client as a foundset property and a foundsetRef (UUID) property
             
            return myNewServerSideFoundset; // return the UUID that points to this foundset (return type will make it UUID)
        };

    then on the client you will get the new foundset value in hashedFoundsets property and you can reference it by it's UUID from now on thorough the foundsetRef type

About preparing the data in the format needed by client directly from server. You already know what is possible for that. The foundset on server-side is what it is, on client side - that is the structure you will get for it (and get updates and everything in it). If it's not directly compatible with what your bean needs (which would be quite strange if it was btw : )) you will need to convert it there to what the underlying component/library needs. A totally different approach is possible where you only send static JSON ('object' type) or datasets (there is also a dataset type) to client based on server-side queries (but then you get no automatic updates and need more code on server as well I think).
Andrei Costescu
Servoy
Andrei Costescu
 
Posts: 1018
Joined: Tue Jun 26, 2007 3:14 pm

Re: Grouping in dataGrid component.

Postby jay.rao » Thu Apr 20, 2017 11:47 am

Hello Andrei,


Thank you for the detailed explaination,it would be helpful to others too.
Sorry to ask silly question. :)
So extracting from what you have explained thoroughly I would like to add my case briefly.
Lets say,I am using $scope.model.foundset.viewPort.rows to populate my data in grid so if I use handlers to pass my search parameters to serverSide.js and run sql query to get searched records in the foundset(from serverSide js).So this updated foundset would directly be reflected in my client side's foundset i.e $scope.model.foundset.viewPort.rows?

Code: Select all
controller: function($scope) {
         var orders = new DevExpress.data.CustomStore({
             load: function (loadOptions) {
                 var deferred = $.Deferred();
                  if(loadOptions.filter)
                  {
                     $scope.handlers.onFilterMethodId(searchString,column);
                  }
                 return deferred.promise();
         }
         })

I am passing my searchString and column to the server side handler.

Code: Select all

//Server Side Handler
function onFilterMethodId(searchString,column,event)
{
   var _dp = columnName
   var _search_string = 'searchString'
   
   application.output('in method')
   
   var _fs = databaseManager.getFoundSet('development','article')
   if(_fs.find()){   
       _fs.article_code = '%' + _search_string + '%';
       _fs.search();
   }
   }
}

This would return me the foundset(i.e article_code,article_id,name,email... all the fields from table) unlike client's foundset i.e $scope.model.foundset.viewPort.rows(which contains dp0,dp1)
So do I have to return this serverSide's foundset inorder to reflect it at the client's foundset(fs.viewPort.rows)?

All I want is to return this server side's foundset to client side without manually changing it to dp0,dp1.
I dont want to create new foundset,I would want to just use the existing foundset at both the end. :? :(
jay.rao
 
Posts: 59
Joined: Mon Apr 10, 2017 1:32 pm

Re: Grouping in dataGrid component.

Postby Andrei Costescu » Thu Apr 20, 2017 1:17 pm

jay.rao wrote:Lets say,I am using $scope.model.foundset.viewPort.rows to populate my data in grid so if I use handlers to pass my search parameters to serverSide.js and run sql query to get searched records in the foundset(from serverSide js).So this updated foundset would directly be reflected in my client side's foundset i.e $scope.model.foundset.viewPort.rows?

Note: I noticed in last two posts you refer to handlers. You shouldn't use handlers for that (so the things that any developer using your component can set) but call component server-side scripting API (the one that for 8.1.3 is defined in "api" section of the spec file; [side note] for 8.2 it will be "internalApi" but still backwards compatible). There is information about server-side component/service scripting here in "Serverside scripting" section (I guess that should be moved to it's own separate wiki page). So you use "$scope.servoyApi.callServerSideApi(...)" in browser (not $scope.handlers.on...) and that ends up executing code in your mycomp_server.js file. The advantage here is that the solution developer doesn't have to write&add the handlers himself/herself as that is probably component specific behavior only. The example from the foundset property page that combines foundsetRef with foundset and recordRef does use it like that. Maybe study that a bit more in-depth and ask what is not clear in there.

And yes if in that serverside js you do something like $scope.model.foundset.loadRecords(...) that will automatically be reflected in browser property content. ($scope.model.foundset.viewPort.rows will be updated but probably also $scope.model.foundset.viewPort.startIndex, $scope.model.foundset.serverSize and others to reflect the new state on the server)

jay.rao wrote:This would return me the foundset(i.e article_code,article_id,name,email... all the fields from table) unlike client's foundset i.e $scope.model.foundset.viewPort.rows(which contains dp0,dp1)
So do I have to return this serverSide's foundset inorder to reflect it at the client's foundset(fs.viewPort.rows)?

All I want is to return this server side's foundset to client side without manually changing it to dp0,dp1.
I dont want to create new foundset,I would want to just use the existing foundset at both the end. :? :(

In your example you use databaseManager.getFoundSet(...) which will not be the same foundset in the component - it will be a different foundset on the same table. So it doesn't affect your $scope.model.foundset property in any way. Only if you would then assign it to elements.myFoundsetBasedBean.foundset as shown in my previous post. If you want to reuse the foundset, just do as I said in the beginning of this post.

Also both foundsets on server do have all columns. Only the foundset-type-to-client can be configured to send only some dataproviders as shown in previous post. Btw., you can also read current $scope.model.foundset.dataproviders - see what that gives you in server-side mycomp_server.js (if you want the same dps on new foundsets you can read the dps on main foundset from there).

Just to be clear. You have for a component (the same for NG services/plugins):
Code: Select all
BROWSER/CLIENT Component JS <-----> SERVER Component JS <---> |  <---> Solution JS code or other Servoy provided content
                            <---[server side JS optional]---> |

So you are playing here within "BROWSER/CLIENT Component JS" and "SERVER Component JS" - where you can use $scope.model.... or $scope.api.... and so on to do what you need. Although the content of for $scope.model.foundset is in client what you know - all that with $scope.model.foundset.viewPort.rows and so on and on server-side it is exactly what you would get in solution js for that property (as documented in wiki - foundset property type page for "Runtime property access").
The "Solution JS code or other Servoy provided content" so handler implementation for example are only meant to be used by the developers that use your component. There you would access your component's model (what is available for solution scripting at least) with elements.myFoundsetBasedBean....

Use Servoy 8.1.3 rc when trying all this out. Not all the features I am mentioning are available in 8.1.2.
Andrei Costescu
Servoy
Andrei Costescu
 
Posts: 1018
Joined: Tue Jun 26, 2007 3:14 pm

Re: Grouping in dataGrid component.

Postby jay.rao » Thu Apr 20, 2017 1:45 pm

Hello Andrei,


It was so silly of me to compare server js with form's js.It was a huge mistake,just as you were answering this question while back I was going through the foundset property and I found that I was referring to totally different thing and I am sorry for that.I would go through everything just to be clear and get back to you.

I have created a new server file in my js and I would be using that to get my things done.


Thank you
jay.rao
 
Posts: 59
Joined: Mon Apr 10, 2017 1:32 pm

Re: Grouping in dataGrid component.

Postby Andrei Costescu » Thu Apr 20, 2017 1:51 pm

Np. It's a lot to take in at once. :)
Andrei Costescu
Servoy
Andrei Costescu
 
Posts: 1018
Joined: Tue Jun 26, 2007 3:14 pm

Re: Grouping in dataGrid component.

Postby jay.rao » Fri Apr 21, 2017 7:57 am

Hello Andrei,
I am back with few questions again :lol:
Search.PNG



As you said I went the documentation of foundset property and it was indeed very helpful.Actually now as you can see above in the image I have written a script for searching in the database.I call my SeverApi method from the client side and pass the searching parameters to server-side FilterApi and enter the find mode but I doesnt find anything.I am performing search() on JSFoundset as I want to simple return the same foundset back to my client(not creating a new foundset,update the foundset that was on the clientSide).
PS-Also it would be possible to access all the server side method in this component JS ,if I am not wrong.


Regards
You do not have the required permissions to view the files attached to this post.
jay.rao
 
Posts: 59
Joined: Mon Apr 10, 2017 1:32 pm

Re: Grouping in dataGrid component.

Postby Andrei Costescu » Thu Apr 27, 2017 11:38 am

I suspect the question here is why don't you get any results right?
You expect to get results and there are none?

As you can see columnName param is "dp1" there, which is correct I guess for what you want to do.
But then you want to search by the dataprovider that is used as dp1 on client right?
So that "parentFoundset.columnName = ..." doesn't sound right... I guess you don't have a column in that table really named "columnName" right?

So what you need to do there is search based on the real dataprovider (so in this case a column name) that is set in "dp1".
You should be able to do that by something like
Code: Select all
parentFoundset[$scope.model.foundset.dataproviders[columnName]] = ...

So you get the real column name associated with "dp1" from the foundset typed property.
Andrei Costescu
Servoy
Andrei Costescu
 
Posts: 1018
Joined: Tue Jun 26, 2007 3:14 pm

Re: Grouping in dataGrid component.

Postby jay.rao » Thu May 04, 2017 8:11 am

Hello Andrei,
Thank you for the reply.Yes we done something similar to this and now we are capable of filtering any column with any conditions.
jay.rao
 
Posts: 59
Joined: Mon Apr 10, 2017 1:32 pm


Return to Servoy NGClient

Who is online

Users browsing this forum: No registered users and 10 guests