[Helma-user] searching in subnode in helma

[Michael Platzer]

Julian Tree schrieb:
> how does helma handle user input searching.  For example I have a  
> search box that allows user to search for Job or Company where  
> job_name or file_name contains the value entered by the user.
>
> I know there a subnodeRelation, but it completely redefine the where  
> sql statement for the entire collectio.
we do the following trick: we define a SearchMgr prototype, which 
contains for each search a dedicated collection. when performing a 
search, we create a temporary instance of this SearchMgr and set the 
subnodeRelation of that collection to whatever we want.

  SearchMgr/type.properties
    storyMgrFrontendSearch = collection(Text)
    storyMgrSearchByTag = collection(Text)
    storyMgrSearchByTag.filter.additionalTables = left join AV_TAG on 
(TEXT_ID = TAG_F_TEXT)
    storyMgrSearchByTag.hints = distinct
    sysMgrSites = collection(Site)
    sysMgrUsers = collection(User)
    ....

and then, at the code line where you need the search result:
   var searchMgr = new SearchMgr();
   var searchColl = searchMgr.storyMgrFrontendSearch;
   // construct sql
   var sql = "where TEXT_F_SITE = " + this._parent._id;
   if (param.query) sql += "and (TEXT_TEXT like '%" + 
param.query.encodeSql() + "%' or TEXT_TITLE like '%" + 
param.query.encodeSql() + "%')";
   ...
   sql += "order by TEXT_CREATE_TIME";
   searchColl.subnodeRelation = sql;

> Is there a way to add where sql to the already defined sql statement for a collection?
>   
no, you just can overwrite the complete sql beginning with the WHERE-clause.
> I know it is possible to have helma loop through the jobs collection  
> and look for matching job_name, but I feel like we are not taking  
> advantage of the database. Database is optimized for searching, so  
> for something like that I would rather see the database performing  
> the search.
>   
yes, searching should definitely be performed by the database.

  michi