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?

Rhetic

NHibernate 3 related: #7134483 – Laceration

Answer 1

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.

Biquadrate

I believe this approach should also allow you to use the criteria API, if you would prefer that. – Biquadrate

I was trying to avoid this route, as there is a further WHERE clause applied to the orderitems that I left out for brevity sake. So are you saying that its not possible to apply a limit on a sub query? – Rhetic

I don't believe it is possible to have a limit on the subquery, sorry. I fear you will need two queries (one to select the ids of the n orders that you want and then another one on the orderlines where orderId in returned list of ids). But depending on the total number of order lines for the top orders you want I would probably do more in code (i.e. fetch join all order lines for the top n orders, and then filter in code to select the order lines that you want). – Biquadrate

You could also take a look at filters, either on the fly (e.g. session.Filter(order.OrderLines, "where this.amount > 2")) or predefined (hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/…). – Biquadrate

Thanks Fried, splitting it into two seems to work well for what I'm doing. – Rhetic

Answer 2

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)

Obelize

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());

Sailor