4 Replies Latest reply: Apr 9, 2013 12:09 PM by 984733 RSS

    Multiselect and Count

      I'd like to be able to get a list of entities, and the number of those entities, in the same query, if possible. (I don't want the number of rows returned - I want the total number of rows in the database.)

      I tried this:
      criteria.multiselect(myEntityRoot, builder.count(myEntityRoot)).distinct(true);

      The problem is, I'm getting a DB2 error:
      which says:
      The SELECT statement has one of the following errors:
      The identified expression and a column function are contained in the SELECT clause, HAVING clause, or ORDER BY clause but there is no GROUP BY clause
      The identified expression is contained in the SELECT clause, HAVING CLAUSE, or ORDER BY clause but is not in the GROUP BY clause.

      I think it wants me to do a groupBy, but that causes other problems (I think there are some JPA bugs involved, which is why I'm using distinct instead).

      Is there some way I can get both of these pieces of information at the same time?

      (We tried copying the original query to a new one, but I think we're running into another JPA bug where it's not copying parameters from a subquery correctly, because that's failing.)
        • 1. Re: Multiselect and Count
          You can't use count because there is nothing to count - you should get a count of 1 if it were allowed, since you are returning the same myEntityRoot that you are counting. You could perform the count within a subquery, but it would be inefficient use of the database.

          if all you want is the number of entities, why not just select myEntityRoot and then call size on the collection that is returned?
          • 2. Re: Multiselect and Count
            Thanks. The problem is, I don't want the count of rows returned to me - I want the total number of rows in the database (we are limiting the rows that are returned).

            I ended up reusing the CriteriaQuery from when I got the list of rows, and modifying it (it fails if I don't use 'countDistinct' and set 'distinct' to 'false'):

            Then I needed to recreate all the parameter expressions that I used in the original query. That did what I needed it to.
            • 3. Re: Multiselect and Count
              You cannot reuse criteria api expressions in different queries, so it is more difficult to copy queries and make slight changes without starting over.

              From the sounds of it, all you need is a simple "Select root, count(root) from YourEntity root where <your clauses> GROUP BY root". Can you detail the problem you get using group by in this fashion and the provider version you are using? You might be able to use root.<id> instead of root directly if it is having problems with using root directly in count or group by.

              Best Regards,

              Edited by: cdelahun on Apr 9, 2013 12:13 PM
              • 4. Re: Multiselect and Count
                Well, reusing the CriteriaQuery to create a new TypedQuery, and then adding the ParameterExpression entries, is working for me.

                My situation is this. There are many modules that we call to create the query - advanced search, etc. - many different things that can add predicates. So when we've done the original query to get a list of entities (limited by rows), then we need to find out how many entities would match those predicates (sorry, I misspoke before - I just said number of rows in the database).

                By the time we get the count, there could be many different predicates that have been added to the query. Which is why reusing the same query seemed like a good idea. And like I said, it really is working right now. I hope I'm not just lucking out in this situation, and will run into some where it doesn't work.