Danilo Tommasina wrote:
Hi Jakob,that's what i've done.
yup I tried to 'patch' the getCount() method and also saw that setting the copy-groupBy flag to true wouldn't solve the problem.
copying the 'group by' information may be done by iterating on the results of the Query.getGroubBy() and adding the columns to the report query.
however, this doesn't return me what i really expected (on sybase) since it returns me several rows containing the count of the elements available in the 'groups' and not the total number of grouped results.this isn't the official name, isn't it ;)
Actually the number i would expect may be extracted counting the rows in the count-query resultset.
However i don't know if every db does the count the same manner as sybase, so this may not be a viable solution.
actually for 'real' databases (also not sybase) it is possible to use a ditinct clause for doing exactly what i need, however scheissbase <=
12.0 knows only VARCHAR with a max length of 255, so for longer texts a data type TEXT (a CLOB) must be used. of course no distinct query can be done if a table contains TEXT fields.
the horrible trick to simulate a distinct query in sybase is this (for anyone interested in it):
in case the platform contains the string 'sybase' then instead of setting the distinct flag, add a group by clause for the PK.
(this is supported by sybase, but will fail on oracle)
drawback is that a count query on this fake distinct query will return wrong results. and here comes the second horrible trick.
since ojb ignores the group by clause and a OJB distinct count query adds the distinct clause only on PK columns (that should never be of type TEXT) then it is possible to safely set the 'distinct' flag for the count query also for sybase.
so for doing a 'results count limited distinct query' i do the following (for sybase):
- set the distinct flag on query - do count query - if count > than maximum results throw exception - else reset the distinct flag - add group by for the PK - do the query
brrrrrr, horrible but works
i was thinking of: select count (distinct key1 || key2) from .... when we have multiple pks.
this does of course not solve the group by at all :( and of course string concatenation is not the same on any platform (|| is the ansi char for concatenation, but ms sql-server uses +, ms-access &, mysql has a function called concat...) how is it in s...base ?
imo an easy way to solve the problems would be to use an enclosing count query:
select count(*) from (original query)
but this does not yet work in mysql !
so, sorry for the long text, but maybe sombody else has the same problem and that's the (horrible) way i solved it.
if somebody has a better solution i would be glad to hear it, thanks
anyway cheers danilo
jakob
hi danilo,
the groupBy information is defined in the query not in the criteria, so the copy() does not make much sense. but there's a problem when counting reportqueries using groupBy:
Criteria crit = new Criteria();
ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit);
q.setColumns(new String[] { "groupName", "sum(allArticlesInGroup.stock)", "sum(allArticlesInGroup.price)" });
q.addGroupBy("groupName");
broker.getReportQueryIteratorByQuery(q); while (iter.hasNext()) { results.add(iter.next()); }
int count = broker.getCount(q); assertEquals(results.size(), count); <<< FAILS
results in this sql:
SELECT A0.KategorieName,sum(A1.Lagerbestand),sum(A1.Einzelpreis) FROM Kategorien A0 LEFT OUTER JOIN BOOKS A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr LEFT OUTER JOIN CDS A1E2 ON A0.Kategorie_Nr=A1E2.Kategorie_Nr LEFT OUTER JOIN Artikel A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr GROUP BY A0.KategorieName
ojb generates a wrong count-sql :
SELECT count(*) FROM Kategorien A0
or when including groupBy:
SELECT count(*),A0.KategorieName as ojb_col_1 FROM Kategorien A0 GROUP BY A0.KategorieName
how should the count look like (without using a subquery) ?
jakob
Danilo Tommasina wrote:
Hi,
Is there a reason why the GroupBy clause is beeing ignored in a query when doing a PersistenceBorker.getCount( Query query )?
In RC4 release I found this piece of code:
class PersistenceBokerImpl
public int getCount(Query query) throws PersistenceBrokerException {
(...)
// build a ReportQuery based on query // orderby needs to be cleared if (query.getCriteria() != null) { reportCrit = query.getCriteria().copy(false, false, false); }
(...)
}
The line:
reportCrit = query.getCriteria().copy(false, false, false);
copies the Criteria ignoring the GroupBy clause, thus causing the count query to return a wrong number of elements.
Is there a reason for this or has it been fixed after RC4?
thx bye Danilo
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
