[Helma-user] SqlCollection

[Michael Platzer]

dear list,

i was always hoping for a Helma functionality, that would allow me to 
state an arbitrarily complex SQL-statement, that would return me a 
HopObject-collection, which can be used like all other collections.

Possible usage scenarios are:

 * search collections: the where-clause is being defined on-the-fly;
select * from AV_TEXT where TEXT_TEXT like '%hello%';

 * "mixed collections" with objects from different tables; e.g. "latest 
objects"
(select TEXT_ID as id, TEXT_PROTOTYPE as prototype, TEXT_CREATE_TIME as 
time from AV_TEXT) union (select CONTENT_ID as id, CONTENT_PROTOTYPE as 
prototype, CONTENT_CREATE_TIME as time from AV_CONTENT) order by time 
desc limit 5;

* grouped queries that sort by their some aggregate method; e.g. "users, 
that tagged the most"
select USER_ID as id, 'User' as prototype, count(*) as cnt from AV_USER 
left join AV_TAG on (USER_ID=TAG_F_USER_CREATOR) group by USER_ID order 
by cnt desc limit 5;

And just recently I found out, that I can acchieve this very easily by 
myself by defining a simple object  The usage is as simple as:
  var coll = new SqlCollection(dbcName, sql);

Attached you will find the according source code for SqlCollection. If 
others find this also useful, I would suggest adding this to the 
helmaLib, resp to jalaLib. whereever it fits better. please let me know.

greets,
  michi


p.s. the attached code requires the suggested changes to helma.Database 
that i sent to helma-dev yesterday: 
http://www.nabble.com/helma.Database---suggested-improvement-tf3841979s2589.html

-------------- next part --------------
A non-text attachment was scrubbed...
Name: SqlCollections.js
Type: application/x-javascript
Size: 2008 bytes
Desc: not available
Url : http://helma.org/pipermail/helma-user/attachments/20070531/662d9fcc/attachment-0001.js