Total counts for DISTINCT select clause in the ListGrid (not grouped by)

[jaikumar]

SmartClient Version: v10.0p_2015-06-10/PowerEdition Deployment (built 2015-06-10)
Browser Version : IE 11.0.9600.17843

Total counts for DISTICNT select clause in the ListGrid

It looks the grid.getOriginalResultSet().getLength() is not working while we use the Distinct select clause in the ds.xml.
Example:

TABLE_A, TABLE_B (one to many)

DSRequest request = new DSRequest(DSOperationType.FETCH, "xxxFetch");
request.setProgressiveLoading(false);

grid.fetchData(criteria, new DSCallback() {
@Override
public void execute(DSResponse dsResponse, Object data, DSRequest dsRequest) {
System.out.println("TotalCount(ResultSet) : "+grid.getOriginalResultSet().getLength());
System.out.println("TotalCount(ResponceRow) : "+dsResponse.getTotalRows());
}
}, request);


-----------------
ds.xml (select clause only for TABLE_A fields)

<operationBinding operationType="fetch" operationId="xxxFetch">
<selectClause>DISTINCT $defaultSelectClause</selectClause>
<tableClause>TABLE_A, TABLE_B</tableClause>
<whereClause>TABLE_A.X = TABLE_B.X AND TABLE_B.Y=10 AND $defaultWhereClause</whereClause>
</operationBinding>

Please help me to get the distinct total count.

[Blama]

Hi Jaikumar,

this is definitely not related to SmartGWT clientside widgets (ListGrid).
Did you see the complete SQL (especially the SELECT-clause)? I'd assume that it contains too many columns and therefore the DISTINCT is not working as you expect it. Consider using the outputs-attribute on the operationBinding as well.

Best regards
Blama

[jaikumar]

Thanks Blama,
Yes many columns are there.. But I am getting the correct results in the grid but the total count returned by smartgwt is wrong. Is that we need to add group by clause when we use the DISTINCT in the select clause to get the proper count in the ResultSet.

[jaikumar]

Blama any help on this

[Blama]

Thought about it.
I'm pretty sure that SmartGWT uses the result of the rowcount-query (SELECT COUNT (*), see your server-logs) as number for your mentioned getOriginalResultSet().getLength().

Your <selectClause>DISTINCT $defaultSelectClause</selectClause> is not used for the rowcount-query, but this is OK, as SELECT COUNT(DISTINCT *) FROM myTable is not valid SQL.
The correct way to express DISTINCT in .ds.xml is shown at "Grouping without Summarizing" in the docs. I assume you need a <groupBy>$defaultSelectClause</groupBy> instead of your selectClause.
I never used it that way, though, so please test it.

Best regards
Blama

[jaikumar]

Yes groupBy will solve the problem then we can get the Total count but my point is just to get the Total count using the groupBy will cause the performance issue?

[Blama]

Sorry I don't understand.