Hibernate subquery. Multiple rows and single row combined in one query

I am trying to create a query, which gets all rows for certain names, but only the oldest for another restriction.

key | someId| someOtherId| name | value | date 1 | 1 | 123 |frank | 0 | 1/1/2013 2 | 1 | 123 |frank | 0 | 2/1/2013 3 | 2 | 123 |peter | 300 | 3/1/2013 4 | 2 | 123 |peter | 400 | 4/1/2013

If "someId" is 1, I want only the older row. for peter, I need all peter rows. So in this example, I'd like to get the rows with key 1, 3 and 4. I tried to create a subquery, but I am not sure how to add it to my main criteria. I tried criteria.Add(Subqueries.geAll( value, detachedCriteria); not sure what I need to provide for value, since I just need the whole row.

and I tried criteria.Add(Subqueries.Exists(detachedCriteria)); which results in an exception "cannot use subqueries on a criteria without a projection."

So my code looks something like this right now:

var criteria = session.CreateCriteria(typeof(Entity)); DetachedCriteria singleStatusResult = DetachedCriteria.For(typeof(Entity)); ICriterion criterion = null; lock (lockObject) { foreach (Param param in requestedParams) { ICriterion newCriterion; if (param.name == "someSpecialText") { singleStatusResult.Add(Restrictions.And(someId, someOtherId)); singleStatusResult.AddOrder(Order.Asc(date)); singleStatusResult.SetMaxResults(1); } else { newCriterion = RestrictionsExtension.AndConcatinated(someId, name, someOtherId); criterion = criterion == null ? newCriterion : Restrictions.Or(criterion, newCriterion); } } } criteria.Add(criterion); criteria.Add(Subqueries.Exists(singleStatusResult));return criteria.List();

Not sure if I really need the detachedCriteria anyway. What am I missing?

Category:hibernate Time:2018-12-04 Views:0
Tags: hibernate

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 4.774 (s). 14 q(s)