Hi all,
I've got two tables in my db Projects and Reports. Each Project instance keeps collection of reports. I'm using criteria query to filter projects set so I can easily build dynamic query based on Projects properties. I would also like to query Projects table based on Reports properties. For instance I want to get all projects with ALL reports having some particular status. I'm doing this like:
Code:
Criteria.Add(Expression.Sql(QUERY_ALL_REPORT_BY_STATUS, reportStatus, NHibernateUtil.Int32));
where
Code:
protected const string QUERY_ALL_REPORT_BY_STATUS =
"(select count(Id) from reports where ProjectId = {alias}.Id and StatusId = ?) = (select count(Id) from reports where ProjectId = {alias}.Id) and (select count(Id) from reports where ProjectId = {alias}.Id) > 0";
I don't even know how to use aliases in tis query. I'm shure that there is some better way to solve this issue.
Waiting for any suggestions.
Thanks
Login
FAQ
Search