[Helma-user] Sorting in group-by clause!

[Anton Pirker]

Hi list!

I have a database table like the following:

entry
------
entry_id  int
event_id int
created datetime
modified datetime
...

now i want to make a collection where i get all entries grouped by the 
event_id and then sort the groups after the modification date of the 
newest entry in the group.
in pure sql it would be something like this:

SELECT ENTRIES.EVID, MAX(ENTRIES.MODIFIED) modDate
FROM ENTRIES
GROUP BY EVID
ORDER BY modDate desc

i need the strange order-by, so i get always the newest entry of every 
group. the order-by will be performed after the grouping.

the (right) result:
event_id, entry_id, title, modified
420, 538, 'Gnarls Barkley', '2006-07-16 22:59:38'
359, 456, 'Deichkind', '2006-07-16 09:30:31'
451, 600, 'Klub Beton', '2006-07-16 09:29:47'
452, 605, 'Metalab Sommerfest', '2006-07-16 09:28:41'
453, 607, 'Icke Micke', '2006-07-14 15:36:31'
454, 609, 'Sharon Jones and the Dap-Kings', '2006-07-14 11:23:27'
450, 594, 'Barbarella', '2006-07-13 20:45:16'


I tried to achieve this in helma with following collection-definition:

type.properties:
entries = collection(Entry)
entries .order = modified desc
entries .group = evid
entries .group.order = modified desc

with this i get a collection of collection (representing the group). the 
ordering of the entries in the groups is fine, but the ordering of the 
groups is wrong. it seems that first the entries are ordered and then 
grouped. so the groups are not in order...

the result:
event_id, entry_id, title, modified
454, 609, 'Sharon Jones and the Dap-Kings', '2006-07-14 11:23:27'
453, 607, 'Icke Micke', '2006-07-14 08:35:49'
452, 605, 'Metalab Sommerfest', '2006-07-13 22:42:05'
451, 600, 'Klub Beton', '2006-07-13 15:39:05'
450, 594, 'Barbarella', '2006-07-12 11:05:20'
449, 592, 'Club Eggnog mit Mitropamusik', '2006-07-11 18:00:34'
448, 591, 'PLATZebo', '2006-07-10 18:53:21'



so i tried a dirty hack:

type.properties:
recentEntries                           = collection(Entry)
recentEntries.hints                     = ENTRIES.EVID, 
max(ENTRIES.CREATED) createDate,
recentEntries.group                     = EVID
recentEntries.group.order               = createDate desc


with the 'hints' property i add the MAX-function and give this column a 
name, so i can sort with it. (the first ENTRIES.EVID in the hints is 
because helma assumes, that the first column of the result is the ID)

this surprisingly workes somehow! i get the entries in the right order, 
but if i access the coll.size() method, ill get the following error:
  "Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP 
columns is illegal if there is no GROUP BY clause"
this is because helma generates this sql-statement:
  "SELECT ENTRIES.EVID, max(ENTRIES.CREATED) createDate, ENTRIES.ENID 
FROM ENTRIES WHERE ENTRIES.EVID=359"
a select statement with a max() and without group-by...


so. now i am at the end of my wisdom.
maybe i am trying to long to achieve my goal so i am blind for the 
obvious solution.
maybe someone can shed light on my brain!


thanks for every hint!
(though: a complete solution would be preferred ;)

nice, sunny day!
Anton