[eclipselink-users] ReadAllQuery/ReportQuery count..

[vaidya nathan <vaidyaatdst@xxxxxxxxx>]

Good Morning Eclipselink users,

Am having problems doing a count on a query that fetches objects using
aggregate functions. I would like to use the query object that i use
for getting the objects to do a count and am not having success . Lets
say, I have a following entity called

public class BaseEntity //Base Entity which gets inherited by other Entities
{
   private Long int;
   private Date recorded;
}

@Entity
public class BusClass extends BaseEntity
{
}
and when i query on the BusClass this is how i do

public  BusClass retrieveBusClass(Paginationinfo pI)
{
    ExpressionBuilder eb = new ExpressionBuilder(qbe.getClass()) ;
    ReportQuery query = new ReportQuery(eb) ;
    QueryByExamplePolicy qbePolicy = new QueryByExamplePolicy() ;
    query.setExampleObject(qbe) ;
    query.setQueryByExamplePolicy(qbePolicy) ;
    query.setShouldFilterDuplicates(true) ;
    query.setDistinctState(ReportQuery.USE_DISTINCT) ;
    query.addAttribute("id") ;
    query.addGrouping("id") ;
    query.addMaximum("recorded") ;
    Query jpaq = JpaHelper.createQuery(query, getEntityManager());
    List<ReportQueryResult> results = jpaq.getResultList() ;
    pI.setCount(getCount(query)) ;;// this is where my problem is ..
}

and this is the query i get finally when i turn on logging..
SELECT DISTINCT ID, MAX(RECORDED) FROM BUSCLASS GROUP BY ID

I need to get a total row Count on this and that's where my problem is
lets say i want to generalize the getCount in one place , following is
how my method looks like

Integer getCount(ReportQuery reportQuery)
{
    reportQuery.addCount();
    Query jpaq = JpaHelper.createQuery(query, getEntityManager());
    countObject = jpaq.getSingleResult();
}

The problem is that this gives the following sql
SELECT DISTINCT ID, MAX(RECORDED),COUNT FROM BUSCLASS GROUP BY ID

whereas i was expecting it to give
select count(*) from (SELECT DISTINCT ID, MAX(RECORDED) FROM BUSCLASS
GROUP BY ID)

The only way in which i am able to get the above statement is if i use
native query something like this

Integer getCount(ReportQuery query)
{
            if (query.getSQLString() != null)
            {
                q1 = getEntityManager().createNativeQuery("select
count(*) from (" + query.getSQLString() + ")");
                countObject = q1.getSingleResult();
            }
}

Is there a way in which i can use the ReportQuery itself to achieve
what i want to do instead of using nativeQuery since native queries is
generally intended to provide support for those cases where it is
necessary to use the native SQL of the target database in use .

Also per the links
http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause
we can possibly use subselects to achieve what i want to achieve but
we are not using jpql and i cant see an example of using subselects
using ReportQuery/ReadAllQuery which we are currently using..Also i am
not sure whether ReportQuery supports subselects (Didnt see a
ReportQuerySubSelect class for example or examples of how to use
ReportQuery itself to achieve what i want to achieve)

I want to use the same Query that i am using to read data to get a
count(without the pagination info which i later add). Is this
possible?

Cheers
Vaidya