8 Replies Latest reply on Jul 25, 2007 9:36 AM by Wolfgang Schwendt

    count(*) and group by

    Artur Chyzy Newbie

      Hello.
      I'm trying to show a list of objects.
      I'm using EntityQuery.

      I also show a message when list is empty so i put

      rendered="#{list.resultCount == 0}"
      

      on outputText but the excaption is thrown.
      The problem is that my query has group by i JPQL

      In query class method getCountEjbql
      protected String getCountEjbql()
       {
       String ejbql = getRenderedEjbql();
      
       Matcher fromMatcher = FROM_PATTERN.matcher(ejbql);
       if ( !fromMatcher.find() )
       {
       throw new IllegalArgumentException("no from clause found in query");
       }
       int fromLoc = fromMatcher.start(2);
      
       Matcher orderMatcher = ORDER_PATTERN.matcher(ejbql);
       int orderLoc = orderMatcher.find() ? orderMatcher.start(1) : ejbql.length();
      
       return "select count(*) " + ejbql.substring(fromLoc, orderLoc);
       }
      


      is only checking for order by and from.
      The count(*) is insterted before all... but it will not work with group by queries because then the list of objects will be in result but seam gets getSingleResult in getResultCount method and the exception will be thrown..

      public Long getResultCount()
       {
       if ( resultCount==null || isAnyParameterDirty() )
       {
       javax.persistence.Query query = createCountQuery();
       resultCount = query==null ?
       null : (Long) query.getSingleResult();
       }
       return resultCount;
       }
      


      For now i made a workaround
      rendered="#{not empty lista.resultCount }"
      


      but i think that everybody can see how this is looking like... terrible

      i think that when there is group by in query then it should be converted like this for count(*)
      select count(*) from (previous_query ) as some_name
      


      Is this a bug or i'm doing sth wrong ??

      Can someone put some light into my problem ??

        • 1. Re: count(*) and group by
          Wolfgang Schwendt Newbie

          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 Master

            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 Master

              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 Master

                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 Newbie

                  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 Master

                    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 Newbie

                      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 method

                      protected 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
                        Wolfgang Schwendt Newbie

                         

                        "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.