Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: Re[eclipselink-users] comended method for group-by time values? 
Thanks James, I wrestled with this one all day yesterday.

I'm still using 1.0.2 since I tried updating to 1.1 and ran into a bunch of new problems I wasn't ready to deal with yet.

With 1.0.2 I tried the expression API but the roundDate and truncateDate() methods threw errors about the functions not being supported on the DB.  I assume this is because I'm using postgresql and the postgresql platform doesnt enable these functions or something.  Then I tried to use the getFunction methods but because postgresql actually uses the db field as the second argument, and the "base" of the function wants a query key, that didnt really work.  Then I turned to the literal() method to just try to throw in the entire function w/ the field as a string literal, and that appeared to be a noop, at least for the postgresql platform.  It didnt render anything into the query.  The group by showed up but nothing in the select or group by was there.

So, finally I went with pre and postfixSQL methods and achieved what I was looking for.  Looks kinda like this:
   ExpressionBuilder expressionBuilder  = new ExpressionBuilder();
   ReportQuery reportQuery = new ReportQuery(Action.class, expressionBuilder);
   reportQuery.addCount();
   reportQuery.addAttribute("year", expressionBuilder.get("createDate").prefixSQL("extract(year from ").postfixSQL(")"));
   reportQuery.addAttribute("month", expressionBuilder.get("createDate").prefixSQL("extract(month from ").postfixSQL(")"));
   reportQuery.addGrouping(expressionBuilder.get("createDate").prefixSQL("extract(year from ").postfixSQL(")"));
   reportQuery.addGrouping(expressionBuilder.get("createDate").prefixSQL("extract(month from ").postfixSQL(")"));

That yields a query something like the following:
SELECT COUNT(*), extract(year from CREATEDATE), extract(month from CREATEDATE)
FROM ACTION
GROUP BY extract(year from CREATEDATE), extract(month from CREATEDATE)

I havent looked into the query caching quiet yet, but I'm hoping that the use of prefixSQL and postfixSQL or any of the other methods I've used do not mess with the eligibility for these report queries to be cached.

I'm super pressed for time right now, but when I am able to recover I'll look into requesting the JPQL extensions and the pgsql eclipselink platform support for date functions and maybe even upgrading to eclipselink 1.1 :)

Thanks again!
- Phillip





----- Original Message ----
> From: James Sutherland <jamesssss@xxxxxxxxx>
> To: eclipselink-users@xxxxxxxxxxx
> Sent: Tuesday, April 14, 2009 10:07:11 AM
> Subject: Re: Re[eclipselink-users] comended method for group-by time values?
> 
> 
> JPQL does not currently have any such date functions, but EclipseLink does
> provider several such functions through its Expression criteria API.  So you
> could define the query using EclipseLink Expressions.
> 
> To have the function support adding to EclipseLink JPQL see,
> https://bugs.eclipse.org/bugs/show_bug.cgi?id=219814
> 
> You could also use a native SQL query.
> 
> For object caching, since you are using group-by and aggregating data, you
> will not be reading object, so not using the object cache.  You can use the
> query-cache, the query-cache is enabled through Query hints in EclipseLink,
> and any Query can use the query-cache, no matter if it using native SQL or
> functions.
> 
> 
> 
> Phillip Ross-3 wrote:
> > 
> > 
> > Hi all...
> > 
> > I'm wondering if anyone has any recommendations/best practices for
> > executing queries that use date/time values in the group-by clause.  I'm
> > looking to to implement some reports using different aggregate functions
> > using the timestamps as the groupings.  But, the date/time values are
> > timestamps in the database and in SQL there would be some aggregate
> > function i'd use on the timestamp fields to specify the grouping level in
> > the group-by clause.
> > 
> > I dont immediately see any date-specific functions in the standard JPA
> > query language that would be helpful to extract a
> > year/month/week/day/hour/minute/second component from the timestamp values
> > for grouping.
> > 
> > Anyone have a recommendation for doing this?  The final result would be
> > something like aggregating records such as records-per-hour/day/month/year
> > using the transformed timetamp value from the DB.  I'm also wanting
> > cachable results, so the query would be run against the DB until the
> > expiration... but I understand there are limitations on the cachability of
> > the queries depending on what functions they may use, etc.
> > 
> > Anything available in JPA?  EclipseLink expression API?  Do/Should I use
> > native queries and use DB-specific SQL?
> > 
> > Thanks in advance for any advice!
> > - Phillip
> > 
> > 
> 
> 
> -----
> ---
> http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
> http://www.eclipse.org/eclipselink/
> EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
> TopLink 
> Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
> http://wiki.oracle.com/page/TopLink TopLink 
> Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
> http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
> Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
> -- 
> View this message in context: 
> http://www.nabble.com/Recomended-method-for-group-by-time-values--tp23029063p23040071.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
> 
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top