posted 11 years ago
I have an event entity with a composite event id. I have a basic named query like the following which works as expected.
The (:param is null or evt.parm = :param) in the where clause expression "short circuits" which allows me to use one query for multiple cases (with different parameters set to null).
I have facade code like:
Which would return results something like:
//uid, pos, sn, date, rating
1,1,1,"01-03-2010", 1
2,1,1,"01-03-2010", 2
3,1,1,"01-04-2010", 3
4,1,1,"01-05-2010", 4
5,1,1,"01-06-2010", 3
Now I would like a similar query that return a List<Event> with aggregate Sum values instead of the individual rating, to get something like:
//pos, sn, rating
1,1,3
1,2,10
I think I may need to use something like
select evt.sn, evt.pos, sum(evt.rating) as rating from Event evt where evt.sn in ( 1,2) and date >= DATE '01-01-2010' and date <= DATE '02-01-2010' Group by sn, pos;
or
select NEW Event( evt.eventId, evt.uid, evt.sn, evt.pos, sum(evt.rating) as rating) from Event evt where evt.sn in ( 1,2) and date >= DATE '01-01-2010' and date <= DATE '02-01-2010' Group by sn, pos;
Some of my confusion on this may be due to an entity design issue, but not sure. Is this good way to do this or is there a better way to do this? Should I have a new class (i.e. for use in a List<ResultClass>) for the results? Or use an Object [] type for the results? Or use an Object[] and then populate an individual Event?
Hope this makes sense..
Eric