-
1. Re: count(*) and group by
wschwendt Jul 24, 2007 7:20 PM (in response to artur.chyzy)While we are at it:
Is COUNT(*) valid anyway, according to JSR 220/EJB Persistence specification V.3.0???
Interestingly, the Seam approach to generate the count query works if the query has no GROUP BY clause. But it doesn't look like a legal EJBQL expression according to the EJBQL BNF grammar.
Is COUNT(*) missing in the JSR 220 specification?
select_clause ::= SELECT [DISTINCT] select_expression {,select_expression}*
select_expression ::=
single_valued_path_expression |
aggregate_expression |
identification_variable |
OBJECT(identification_variable)
aggregate_expression ::=
{ AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) |
COUNT ([DISTINCT] identification_variable | state_field_path_expression |
single_valued_association_path_expression)
state_field_path_expression ::=
{identification_variable | single_valued_association_path_expression}.state_field
single_valued_association_path_expression ::=
identification_variable.{single_valued_association_field.}* single_valued_association_field -
2. Re: count(*) and group by
christian.bauer Jul 24, 2007 8:28 PM (in response to artur.chyzy)The "*" is a Hibernate HQL extension to JPA QL. I wouldn't use it because, as you figured out, it depends on the other projected attributes in the query and how that is grouped. The issue is that we can't really provide a generic count-query without the "*" in the Seam framework. So this is a catch 22...
-
3. Re: count(*) and group by
christian.bauer Jul 24, 2007 8:30 PM (in response to artur.chyzy)Note that
select count(*) from (previous_query ) as some_name
is not valid either because HQL/JPA-QL has no closure and therefore doesn't support from-clause subselects. -
4. Re: count(*) and group by
christian.bauer Jul 24, 2007 8:32 PM (in response to artur.chyzy)Finally, there _is_ a portable trick for retrieving the total number of rows a query would return:
ScrollableResults resultCountCursor = session.createQuery(q).scroll(); resultCountCursor.last(); long count = resultCountCursor.getRowNumber() + 1 resultCountCursor.close(); // Now do a q.llist() to retrieve the result
The problem here is that the crap JDBC drivers of certain databases (MySQL) do retrieve the resultset of server-side cursors to the client, which would usually result in an OOME - especially if the resultset is large and you actually want to paginate through it. -
5. Re: count(*) and group by
artur.chyzy Jul 25, 2007 3:37 AM (in response to artur.chyzy)what about the native sql solution..
override the count query methods and use native sql for it... the select count(*) from (previous_query) as some_name should work on all databases because this is not using some db specific functions
what do you think ?? is this a worth approach ?? -
6. Re: count(*) and group by
christian.bauer Jul 25, 2007 6:44 AM (in response to artur.chyzy)I don't think we want SQL in the Seam code, and also, you'd need to generate the (previous query) part first. Hibernate doesn't really have many convenient hooks to do that, other ORM tools (JPA compatible) don't have any.
-
7. Re: count(*) and group by - also problem with JOIN
dmitriy.lapko Jul 25, 2007 8:44 AM (in response to artur.chyzy)Hi!
I experienced similar problem with JOIN constructions in my EJBQL.
I also used EntityQuery, I had:public String getEjbql() { return "SELECT item FROM Item item LEFT JOIN FETCH item.itemStorageDetails INNER JOIN FETCH item.unitType ut INNER JOIN FETCH ut.localized utl"; }
I had problem with count(*) query also, so I just overwrote the methodprotected String getCountEjbql() { return removeJoinClause(super.getCountEjbql()); }
May be you can also add there some specific logic to generate the EJB QL query you would like to get, you can do there any parsing you would like to have. -
8. Re: count(*) and group by
wschwendt Jul 25, 2007 9:36 AM (in response to artur.chyzy)"artur.chyzy" wrote:
what about the native sql solution..
override the count query methods and use native sql for it...
if you override Query.getCountEjbql() there's actually no real need to resort to native SQL queries, at least in most cases. JPA QL allows a COUNT expression in a query, even though it's not legal (or portable) to write COUNT(*). See BNF grammar above taken from the JPA spec. So you could easily provide a custom JPA QL count query.
The reason why in Seam the count query gets generated obviously has to do with the fact that the Seam framework supports the XML-based declaration of EntityQuery objects and query restriction fragments, without the developer being required to write any Java code.