[Helma-user] group by

[Paul Alexandrow]

I don't know how often you plan to call/update this collection and how
big your tables are, but a faster solution would be to store a
lastChildUpdate column for MyObjs anyway (but I bet you already thought
of that). In addition, for me this seems to be a more elegant OO-like
way to solve that problem and personally I don't like using too much
relational 'tricks' in my helma applications. But that's personal taste
only of course.

cheers,
-paul


VividVisions, Walter Krivanek wrote:
> 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
> _______________________________________________
> Helma-user mailing list
> Helma-user at helma.org
> http://helma.org/mailman/listinfo/helma-user
>