Nested set query using NHibernate's QueryOver API

Question

I'm storing a hierarchy of categories using the nested sets model, that is, each category has a Left and a Right value and each category has a higher Left and a smaller Right than any parent.

I'd like to query all categories that are sub-categories of a certain category (including that category). That is, I need the whole subtree starting at a given category.

If the id of the root of the subtree is given by @catId, I could use the following SQL to fetch the subtree:

select * 
    from Category c, 
    (select [Left], [Right] from Category where Id = @catId) as t
where c.[Left] >= t.[Left] 
 and c.[Right] <= t.[Right]

Now I'm trying to do similar using NHibernate and the QueryOver API. That's where I'm a bit stuck.

If I split this up into two queries, it's easy:

var cat = session.Get<Category>(catId);

var matches = session.QueryOver<Category>()
                     .Where(x => x.Left >= cat.Left && x.Right <= cat.Right)
                     .List();

But that's two queries - one would be better. I tried to come up with a solution that uses subqueries, and although this technically works, the query probably isn't optimal, because now two subqueries are executed instead of one.

var matches = session.QueryOver<Category>()
                     .WithSubquery.WhereProperty(x => x.Left)
                        .Ge(QueryOver.Of<Category>()
                          .Where(c => c.Id == catId)
                          .Select(c => c.Left))
                      .WithSubquery.WhereProperty(x => x.Right)
                        .Le(QueryOver.Of<Category>()
                           .Where(c => c.Id == catId)
                           .Select(c => c.Right)).List();

(In addition to this, not all DBMS support subqueries that return scalar values, like SqlServerCE, but that's another issue)

Are there better ways to achieve this? If necessary, I can switch the query API for this case. So if e.q. there's a neat way to do it in HQL, I'm fine with it.

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

I thought to get a sub-tree including the tree in nested sets would be something like (SQL):-

SELECT 
    node.*
FROM 
    Category AS node,
    Category AS parent
WHERE 
    node.left BETWEEN parent.left AND parent.right
    AND parent.TreeID = @catID
ORDER BY 
    node.left;

This would use a CROSS JOIN which is not possible in QueryOver (AFAIK)

However it is possible in either HQL or even better linq.

edited May 23 '17 at 10:25

Community

1
1

answered Apr 16 '13 at 09:08

Rippo

22,117
14
78
117

Using Linq did the trick. My original query semantically did the same cross join, it was only expressed differently. The linq query generated by NHibernate uses the exact same query plan. Thanks a lot! – Andre Loker Apr 16 '13 at 13:02
Great, glad to be of help. Was wondering about the differences between the queries, good to see SQL server uses same query plan. – Rippo Apr 16 '13 at 13:17

Nested set query using NHibernate's QueryOver API

1 Answers1