Apache Cayenne > Index > Cayenne FAQ > Mapping Aggregate Queries

Aggregate queries are a common type of SQL queries that build their result rows by combining data from multiple table rows in certain ways. E.g.:

 SELECT t0.ARTIST_ID, t0.ARTIST_NAME, COUNT(1) TOTAL_PAINTINGS 
 FROM ARTIST t0, PAINTING t1 
 WHERE t0.ARTIST_ID = t1.ARTIST_ID 
 GROUP_BY t0.ARTIST_ID, t0.ARTIST_NAME

A fundamental problem in mapping aggregate queries 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 than the ones mapped to a root table. Note that any such objects are almost always read-only, this simplifies things. Handling of this category of objects will be streamlined in Cayenne future releases, however there is already a number of options:

  1. (recommended) Use SQLTemplate returning DataRows without converting them to objects. This is appropriate for most display operations that require data but not behavior.
  2. (recommended) Extention of (1) - Convert DataRow to an object by creating your own POJO Java class that takes a DataRow (or a Map) in constructor. Downside is lack of auto relationships and Modeler support.
  3. 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.
  4. Do an in-memory GROUP BY in Java.