[Helma-user] difficult object relational mapping

[Anton Pirker]

Hi list!

I am trying to do a rather difficult object relational mapping (orm) and 
i am stuck. Maybe someone can help!


My object-relations:
--------------------

i have events. every event has several (1..n) entries (posts). every of 
these entries has several (1..n) changes. a change has a date.

Now i want to have a collection of events ordered by the date of the 
changes of the entries.


In SQL this looks like this:
-----------------------------

select 	
    ev.event_id,
    ev.event_title,
    max(ch.change_datetime)
from 	
    events ev,
    entries en,
    changes ch
where
    ev.venue_id = 56 and
    ev.event_id = en.event_id and
    en.entry_id = ch.change_parent_id and
    ch.change_parent_prototype = "Entry"
group by
    ev.event_title
order by
    max(ch.change_datetime) desc

Note: the changes are wired to parent-prototypes (in this case the 
entry) in a generic way, like described here: 
http://helma.org/bugs/show_bug.cgi?id=516


I came of with this collection of my collection:
------------------------------------------------

recentEvents = collection(Event)
recentEvents.hints = events.event_id, max(changes.change_datetime),
recentEvents.filter = events.venue_id = ${venue_id} and events.event_id 
= entries.event_id and entries.entry_id = changes.change_parent_id and 
changes.change_parent_prototype = "Entry"
recentEvents.filter.additionalTables  = entries,changes
recentEvents.group = events.event_title
recentEvents.group.order = max(changes.change_datetime) desc


Which generates a SQL-Statement just like the above one. In the 
resulting collection there are the right number of objects.
But if i retrieve one of the object instead of getting an [HopObject 
Event] i get an [object HopObect] which looks like this: 
{groupname:"516"}, where 516 is the id of the event.




So, has anyone done something like this before? Can i do this only with 
helmas ORM? (and is it possible in a not-so-ugly way?)

Any help is greatly appreciated!


Regards,
Anton