[Helma-user] searching in subnode in helma

[Julian Tree]

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. Is there a way to add where  
sql to the already defined sql statement for a collection?

// assume jobs is a HopObject collection containing objects of the  
required type
jobs.subnodeRelation = "where user_type = "+session.user_type+" AND
user_id = "+session.user_id;


In PHP I would create SQL such as

select * from job where job_company_id = $logged_in_company_id AND  
job_name ILIKE '%$user_input%';
// returns the result set for job

select * from file where file_company_id = $logged_in_company_id AND  
file_name ILIKE '%$user_input%';
// returns the result set for file


In my Helma app I have a jobs collection defined for the company,
so in the company.jobs and company.files will only have data that  
belong to that company

company/type.properties
--------------------------------------------------
jobs				= collection(Job)
jobs.local	 	= company_id
jobs.foreign	 	= job_company_id

files				= collection(Job)
files.local	 	= company_id
files.foreign	 	= file_company_id



Is there anyway to add additional where SQL statement to further  
filter the the company.jobs collection and company.files collection.

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.

Thanks

Jullian