Re: [eclipselink-users] ReadAllQuery/ReportQuery count..

[vaidya nathan <vaidyaatdst@xxxxxxxxx>]

can someone suggest a fix for this ? I am struck with this problem and
i dont like the kludge particularly :)


On Fri, Aug 17, 2012 at 9:30 AM, vaidya nathan <vaidyaatdst@xxxxxxxxx> wrote:
> 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