[Helma-user] group by

[Michael Platzer]

Hi,

The need for 'group by' in a helma collection is in general rather a 
sign, that the grouped property should be extracted (i.e. 'normalised') 
into its own table/object. Therefor I share Paul's opinion, that 
requiring more and more 'relational tricks' from Helma, should not be 
the way to go.

But what I would rather like to see in Helma (whereas I haven't thought 
this completely through) is a way to instantiate collections on the fly, 
by specifying a complex SQL query. Currently this is just possible in 
the following two ways:

A) Define a collection and set the where-clause on the fly via 
'subnodeRelation'. We use this trick, to handle search queries to the DB 
as standard collection.

SearchMgr/type.properties
  searchContentByTag = collection(Content)
  searchContentByTag.filter.additionalTables = left join tag on 
(CONTENT_ID = TAG_F_CONTENT)
  searchContentByTag.hints = distinct

SomeObj/someFunctions.js
  ..
  var coll = new SearchMgr();
  coll.subnodeRelation = "where .... order by ... limit ...";
  ..

B) Write your complete SQL-Statement, and fetch all HopObjects into a 
new HopObject.

SomeObj/someFunctions.js
  ..
  var dbc = getDBConnection("db_source_name");
  var sql = "select CONTENT_ID from content left join tag on (CONTENT_ID 
= TAG_F_CONTENT) where ...";
  var rows = con.executeRetrieval(sql);
  var coll = new HopObject();
  while (rows.next()) {
    coll.add(Content.getById(rows.getColumnItem("CONTENT_ID")));
  }
  ..

In both cases I can then access my 'custom query' as a regular Helma 
collection:
  for (var i=0; i<coll.count(); i++) {
    var content = coll.get(i);
    content.renderSkin("listItem");
  }

The disadvantage of A) is, that I have to define certain collections 
first in type.properties. And that I am not in control of the complete 
SQL-query.
The disadvantage of B) is, that I have to fetch *all* HopObjects (into 
Helma's Object-Cache), which is for large collections inacceptable.

I would love to see some sort of mechanism, that allows me to 
instantiate/define collections on the fly, that overcomes the cons that 
i just mentioned. (and while being in dreamland, that knows how to cache 
the entries of that collection; whereas that could also be handled by 
Query Caching on the database side)

  var coll = new Collection("Content", "select CONTENT_ID from ...");  
// (nameOfPrototype, sql)

I think such a mechanism could also help in Walter's case. As well as in 
Andi's case, who is used to writing direct SQL-statements.

greets
  michi


> 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:
>   
>> 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.
>>     
Isn't that rather a problem of MySQL?
>> 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
>>
>>     
> _______________________________________________
> Helma-user mailing list
> Helma-user at helma.org
> http://helma.org/mailman/listinfo/helma-user
>
>   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://helma.org/pipermail/helma-user/attachments/20070123/ea45ef63/attachment-0001.html