Custom Count Query

Classic Servoy
1

Trying to implement counter function for displaying number of records in custom component’s ServerJS file
following Code is prepared:-

  • @return {QBResult}
    */

var query = parentFoundset.getQuery();
parentFoundset.loadRecords(query);
console.log(query);
/** @type {QBResult} */
query.result.addPk().add(query.columns.article_id.count);
query.groupBy.addPk().add(query.columns.article_id);
// query.columns.articlestatus_id.count;

but returning Error:-
ERROR com.servoy.j2db.util.Debug - select top 201 article_id, article_id, count(article_id) from article group by article_id , article_id order by article_id asc parameters:
ERROR org.sablo.websocket.WebsocketEndpoint - Error: Wrapped com.servoy.j2db.dataprocessing.DataException: Unknown errorCode 100
Ambiguous column name ‘article_id’. (C:\Users\shivdev.panchal\servoy_workspace\components1\gt\gt_server.js#38)

Any suggestion on writing a proper query

2

What about

var count = databaseManager.getFoundSetCount(foundset);

?

Beware that this has a cost (slow down your database if called frequently) for very large foundsets.

3

the Following Code:- var count = databaseManager.getFoundSetCount(foundset); is supported in client side which is already providing me the record count.
But it is not valid in custom component’s server.JS file giving an error “databaseManager/ datasources” is not defined.

for loading reords:- foundset.getQuery() is used in custom component’s server.js file

Query example which is used in server.js file:-

var query = datasources.db.example_data.person.createSelect();
query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq(‘john’))
foundset.loadRecords(query)

var query = datasources.db.example_data.orders.createSelect();
query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)

both the above queries display error of datamanager / datasources in not defined.

Current Example

/**
*@type {QBSelectdb:/vrms_test/article}

  • @return {QBResult}
    */
    var query1 = parentFoundset.getQuery();
    query1.result.add(query1.columns.articlestatus_id.count);
    query1.groupBy.add(query1.columns.articlestatus_id);
    console.log(‘aticle status id count:’ + parentFoundset.loadRecords(query1));

Error
ERROR com.servoy.j2db.util.Debug - select top 201 article_id, count(articlestatus_id) from article group by articlestatus_id order by article_id asc parameters:
ERROR org.sablo.websocket.WebsocketEndpoint - Error: Wrapped com.servoy.j2db.dataprocessing.DataException: Unknown errorCode 100
Column ‘article.article_id’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

4

Although still bit unsure what you’re trying to do here and why,
I do see that the count you’re trying to get in the ‘Current Example’ is never going to work.

This query, is not correct:

select top 201 article_id, count(articlestatus_id) from article group by articlestatus_id order by article_id asc

If you copy it, execute it in a query tool you will get the same error. So this isn’t even Servoy failing.

Because the parent foundset query you trying to use, is this:

select top 201 article_id from article order by article_id asc

extending it with the aggregate is not going to work.

I guess if you want a count on this selection, you should use the parent query within a new query, for example:

select articlestatus_id, count(article_id) from article group by articlestatus_id where article_id in (select top 201 article_id from article order by article_id asc)

Hope this helps to get this working.

5

Hi,

When I look at your original query it looks like you try to fetch the data AND get the record count back in 1 single query.
You can do this with a Window Function like so:

SELECT TOP 201 article_id
    , COUNT(1) OVER (PARTITION BY 1) AS recordcount
FROM article 
ORDER BY article_id ASC

This will show the full record count in each row, even though you are just fetching 201 of them.

Hope this helps.

6

requesting assistance for writing query mentioned below in servoy’s custom component’s server JS file

SELECT COUNT(article_id)
FROM article
where articlestatus_id = articlestatus_id
group by articlestatus_id
ORDER BY articlestatus_id ASC

Note:- article_id is the primary key in the table

7

Code:-
var query = parentFoundset.getQuery();
query.result.addPk();
var pkColumns = query.result.getColumns();
console.log(query);
query.result.clear();

for (var pkIndex = 0; pkIndex < pkColumns.length; pkIndex++) {
query.result.clear();
query.result.add(pkColumns[pkIndex].max);
query.result.add(query.columns.article_code.count, ‘maximum_items’)
query.groupBy.add(groupColumn);
query.sort.clear();
}

Query Genereated:- select top 201 max(article_id), count(article_code) 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