Criteria Subquery, transform from HQL to ICriteria

jeaf · **Joined:** Thu Aug 27, 2009 5:08 am **Posts:** 3

Hi
Im wondering if this is possible to get the same result with ICriteria regarding this hql-query.

Code:

select rel from Release rel where rel.Details.CreatedBy = 'James' or 
rel in (select elements(pkg.Releases) from Package pkg where pkg.Details.CreatedBy = 'James') or
rel in (select elements(pkg.Releases) from Package pkg left join pkg.Log log where log.CreatedByUser = 'James')

The one thing i could get with ICriteria was how to get or, ive created a couple of detached criterias that was combined but the separator word in the resulting sql where AND instead of OR. Is this type of querys possible with ICriteria? I could find any documentation about the SubQueries.In and i wasnt able to get it working.

Edit
There is relations in the mappings between all of the objects, but from Releases to Package its a many to many. Package.Log is a collection, Releases is also a collection.

Regards

wolli · **Posted:** Fri Aug 28, 2009 4:27 am

criteria.Add(Expression.Or(....., .....))

jeaf · **Joined:** Thu Aug 27, 2009 5:08 am **Posts:** 3

Thanks, I have some troubles matching the Packages with the Releases (There is a many to meny between thoose)

I can get the correct packages with this but i dont se how i can connect them to the correct release. Any ideas?

Code:

 DetachedCriteria userPackages = DetachedCriteria.For<Package>();
            userPackages.Add(Expression.Or
                (
                    Subqueries.PropertyIn("Id", DetachedCriteria.For<Package>()
                            .Add(Restrictions.Eq("Details.CreatedBy", username)).SetProjection(Projections.Property("Id"))),
                    Subqueries.PropertyIn("Id", DetachedCriteria.For<LogEntry>()
                            .Add(Restrictions.Eq("CreatedByUser", username)).SetProjection(Projections.Property("BelongsTo.Id")))
                )).SetProjection(Projections.Id());

var packages = Session.CreateCriteria(typeof (Package))
                    .Add(Subqueries.PropertyIn("Id", userPackages))
                    .List()
                ;

Thanks!

wolli · **Posted:** Fri Aug 28, 2009 8:16 am

Just a try:

Session.CreateCriteria(typeof (Release), "rel")
.CreateAlias("r.Packages", "pkg")
.CreateAlias("r.Log", "log")
.Add(Expression.Or(
.Add(Restrictions.Eq("rel.Details.CreatedByUser", username)),
.Add(Expression.Or(
.Add(Restrictions.Eq("pkg.Details.CreatedByUser", username)),
.Add(Restrictions.Eq("log.Details.CreatedByUser", username))))

... I didn't count the brackets ... hope there a correct. If "Details" isn't a component then you may need CreateAlias("rel.Details", "rd") instead of "r.Details.CreatedByUser".