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.
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)