Custom Count Query

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

What about

var count = databaseManager.getFoundSetCount(foundset);

?

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

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.

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.

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.

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

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