SQLTemplate is a preferred way. I really want to see derived entities
gone, though there is no immediate plans to do that (2.0?).
A fundamental problem with aggregate queries (those queries that group
something) is that returned data loses an *identity* of an unaggregated
row as it is a combination of data across multiple rows. So if you want to
get a list of objects instead of untyped DataRows, those will be different
kind of objects, not the same as the ones that map to a root table.
[that's really an FAQ, I need to add it to our new Wiki]...
While handling this category of objects need to (and will) be improved in
Cayenne, you have a number options right now (note that any such objects
are almost always read-only, this should simplify things):
(1) Use a DataRow directly - appropriate for most display operations that
require data but not behavior.
(2) Use DerviedDbEntities - Don't require SQLTemplate, but limited to
GROUP BY within the same root table. Due to this limitation will likely be
thrown out at some point.
(3) Create your own POJO Java class that takes a DataRow (or a Map) in
constructor. Downside is lack of auto relationships and Modeler support.
(4) Create a separate read-only ObjEntity with a fake underlying DbEntity
that describes the result of your SQLTemplate. Use it as a root of
SQLTemplate. Actually in your case you can do away with using your
"ungrouped" entity, as the columns will be the same, but you need to
realize that you will be dealing with a different type of objects... Don't
try to modify it for one thing...
(5) Do a GROUP BY in Java in memory.
I prefer options (1) or (3), but your app. may have its specifics...
Andrus
> Let's say I have a table with the following columns:
>
> lineitem_id
> category
> amount
>
> How do I do a group by to get the total amounts per category.
> Can this be done with SQLTemplate Scripting only?
> I'm wondering how that works because it one of the parameters to the SQL
> Template function is the class to be returned (If I understand it
> correctly).
>
> Using SQLTemplate how could I map it to category and total?
> Or do I need a derived entity? (although I thought I saw somewhere that
> derived entities are being phased out)
>
> Thanks again!
>
> Fredrik Liden
This archive was generated by hypermail 2.0.0 : Tue Feb 22 2005 - 17:35:01 EST