Require Group Count in case of groupBy using QBselect

[binkal.patel]

Hello

Developed Custom Query at Web component level using reference of below document link

https://wiki.servoy.com/display/DOCS/Fo ... perty+type
https://wiki.servoy.com/display/DOCS/QBSelect

Code :

var childFoundset = parentFoundset.duplicateFoundSet();
var query = parentFoundset.getQuery();
query.result.addPk();
var pkColumns = query.result.getColumns();

query.result.clear();

for (var pkIndex = 0; pkIndex < pkColumns.length; pkIndex++) {
query.result.clear();
query.result.add(pkColumns[pkIndex].min);
query.result.add(query.columns.article_id.count, 'maximum_items')
query.groupBy.add(groupColumn);
query.sort.clear();
}
childFoundset.loadRecords(query);

Query Genereated:- select top 201 max(article_id), count(article_id) as maximum_items from article group by articlestatus_id order by articlestatus_id asc

Error:- ERROR org.sablo.websocket.WebsocketEndpoint - Error: Wrapped java.lang.IllegalArgumentException: The query does not have the correct number of pks in the select

Please provide guild for find group count using QBselect query

[sean]

Hi Binkal,

It's not clear from your post which problem exactly you are trying to solve. Maybe a better explanation about what you want to achieve?
The code example is confusing. Why do you add and remove the columns?
Are you just trying to find out which are the PK columns?

Can be done using JSTable.getRowIdentifierColumnNames
i.e.

Code: Select all

var pks = databaseManager.getTable(foundset).getRowIdentifierColumnNames;


The error you get is because you are trying to load the results back into a foundset and it wants only the PKs for that.

You could instead use databaseManager.getDataSetByQuery(q)

Here is a webinar which shows a bit more about QueryBuilder API
https://www.youtube.com/watch?v=WxPqhJZtr4A

[binkal.patel]

Hi sean,

Thanks for your reply, let me elaborate more details
I have developed code into NGClient-> Webcomponents-> Server-side .js as show in attached screen (code.png)
I have developed API on DataGridServer.Js file as (getGroupFoundSet) and this JS file code as below:
Note: component JS file isn’t supported databaseManager.getDataSetByQuery(q)

My Code:

$scope.getGroupedFoundset = function(idForFoundsets)
{
var parentFoundset = $scope.model.foundset.foundset;
var query = parentFoundset.getQuery();
var childFoundset = parentFoundset.duplicateFoundSet();
query.result.addPk();
var pkColumns = query.result.getColumns();
query.result.clear();
query.result.add(pkColumns[0].min);
query.groupBy.add(query.columns.articletype_id);
query.sort.clear();
query.sort.add(query.columns.articletype_id.desc);

var dps = {};
for (var idx = 0; idx < $scope.model.columns.length; idx++) {
dps["dp" + idx] = $scope.model.columns[idx].dataprovider;
}
childFoundset.loadRecords(query);
$scope.model.filterfoundset.push({
foundset: {
foundset: childFoundset,
dataproviders: dps,
sendSelectionViewportInitially: false
},
filterType:"main",
foundsetUUID: childFoundset,
key: groupKeys
});
return childFoundset;
}

Using this custom query I get result of group data as show in screen (result.png)

Every group have their child data as show in (GroupChildData.png)

Now, I want to extend this Custom query code (this method code $scope.getGroupedFoundset ) so will get group count along with group data, For example in viewport one more column dp3 having data as 7, 25, 1276, 27145.
Please provide your guild line regarding,What kind of modification require for above custom query, to archive group count data along with group

[sean]

Hi Binkal,

I see you are doing this in a component js file. I missed that.

Can you talk about the component you are building and why you need a custom component.
(Building a grid component is no small task)

Is it not possible to create a dataset by query and convert it to a mem data source?
Then you can bind it to existing grid components.
What features do you miss from existing grid components? (i.e. table-view forms, servoy extra grid, ag grouping grid)

Also, we plan to announce the next version of the AG Grid component next week.