[Helma-user] group by

[VividVisions, Walter Krivanek]

Hi,

I wonder if there's a way (or if it's complex to implement) to add  
GROUP BY to collections without having the collection grouped into  
HopObjects.

Use case:

The HopObject "MyObj" has children of the same prototype (or an  
extended prototype).
I want to fetch a collection of MyObjs, ordered by the most recently  
changed children.

Example 1:
myColl = collection (MyObj)
myColl.hints = DISTINCT
myColl.filter.additionalTables = JOIN MYOBJ AS MYOBJ2 ON  
(MYOBJ2.PARENT_ID = MYOBJ.ID)
myColl.order = MYOBJ2.CREATE_TIME DESC

Example 1 returns the right objects, but in the wrong order, because  
DISTINCT doesn't order rows before it "cuts away" duplicates.

Example 2:
myColl = collection (MyObj)
myColl.filter.additionalTables = JOIN MYOBJ AS MYOBJ2 ON (MYOBJ2.  
PARENT_ID = MYOBJ.ID)
myColl.group = MYOBJ.ID
myColl.group.order = max(MYOBJ2.CREATE_TIME) DESC

Example 2 returns the right objects in the right order, but as a  
grouped collection which is totally unnecessary in this case.

My current workaround is to use Example1 and reorder the collection  
in the application .
Another workaround would be to directly access the database and use  
GROUP BY or sub-selects but then I'd lose the benefits of HopObject  
collections.
I could even hack something like "myColl.filter = .... GROUP BY  
(MYOBJ.ID" which produces the right query, but that's not very  
beautiful, is it?
All these options are not very satisfying because they mean  
additional work or "ugly" results in comparison to standard collections.

IMHO, it would be great to be able to add GROUP BY to the collection  
and to nevertheless retrieve a standard collection.
Any ideas?

Best,
Walter