Create Query with GBGroupBy clause

Classic Servoy
1

Hi,
Cannot make work GBgroupBy clause. I have order detail table. Each order has different items. All items grouped to specific group (parentid field, type integer). I just want to find how many item groups I have on my order(The form designed to show each individual order). I try to create Query and group by parentid, but query return me all records. Here the code:

var q = foundset.getQuery()
** q.result.add(q.columns.parentid)**
q.groupBy.addPk() // have to add this line or will bomb on var ds = databaseManager.getDataSetByQuery(q, maxReturnedRows) this line request Primary Key
** q.groupBy.add(q.columns.parentid)**
maxReturnedRows = -1
** var ds = databaseManager.getDataSetByQuery(q, maxReturnedRows);**
** var ncount = ds.getMaxRowIndex()**

On my order I have only 2 groups for 25 items on order. The variable nCount always return me 25 . Why?

The regular select statement works fine:
var query = 'select parentid from orderdetail ’
** query = query + ’ where orderid = ’ + vOrderID**
** query = query + ’ group by parentid’ **
var _dataset = databaseManager.getDataSetByQuery(‘pcd’,query,null,-1)

What the difference between 2 statements?

2

Leonid,

Your query also does a group-by on the pk.

Try this:

    var q = foundset.getQuery()
    q.result.clear()
    q.result.add(q.columns.parentid)
    q.groupBy.add(q.columns.parentid)

Rob

3

It doesn’t work.
I added in group by Pk because i received error on this line var ds = databaseManager.getDataSetByQuery(q, 100);
Servoy required a primary key.

4

Ah, I forgot to clear the order-by which also contained the pk.

var q = foundset.getQuery()
q.result.clear()
q.result.add(q.columns.parentid)
q.result.add(q.columns.parentid.count)
q.sort.clear().add(q.columns.parentid)
q.groupBy.add(q.columns.parentid)
var ds = databaseManager.getDataSetByQuery(q, -1)

Rob