NHibernate HQL SELECT TOP in sub query

Question

Is there a way of using SetMaxResult() on a sub query? Im writing a query to return all the order items belonging to the most recent order. So I need to limit the number of records on the sub query.

The equivalent sql looks something like:

SELECT i.*
FROM tbl_Orders o
JOIN tbl_OrderItems i on i.OrderId = o.Id
WHERE
o.Id in (SELECT TOP 1 o.Id FROM tbl_Orders o orderby o.Date desc)

Im using hql specifically because criteria api doesnt let you project another domain object (Im querying on orders but want to return order items)

I know that hql doesnt accept "SELECT TOP", but if I use SetMaxResult() it will apply to the outer query, not the subquery.

Any ideas?

Answer 1

From NHibernate 3.2 you could use SKIP n / TAKE n in hql at the end of the query. You query will be:

SELECT i.*
FROM tbl_Orders o
JOIN tbl_OrderItems i on i.OrderId = o.Id
WHERE
o.Id in (SELECT o.Id FROM tbl_Orders o orderby o.Date desc take 1)

Answer 2

Just query the orders (and use SetMaxResult) and do a 'fetch join' to ensure all orderitems for the selected orders are loaded straight away. On the returned orders you can then access the order items without this resulting in a new SQL statement being sent to the database.

Answer 3

I encountered this problem too, but didn't found a solution using HQL...

Subqueries with top would be very nice, since this is faster then doing a full join first. When doing a full join first, the SQL Servers join the table first, sort all rows and select the top 30 then. With the subselect, the top 30 column of one table are taken and then joined with the other table. This is much faster!

My query with Subselect takes about 1 second, the one with the join and sort takes 15 seconds! So join wasn't an option.

I ended up with two queries, first the subselect:

IQuery q1 = session.CreateQuery("select id from table1 order by id desc");
q1.SetMaxResults(100);

And then the second query

IQuery q2 = session.CreateQuery("select colone, coltwo from table2 where table1id in (:subselect)");
q2.SetParameterList("subselect", q1.List());