nhibernate QueryOver 子查询连接连接 IsLike

Question

我是 nHibernate 和 QueryOver 的新手，在用 C# 将一些 sql 重写为 QueryOver 时遇到了麻烦。谁能帮助我吗？我的特殊问题是，我通过包含 IsLike 和连接值的析取来加入子查询，但我找不到正确的方法来做到这一点。

以下是所涉及的数据库表和一些示例数据的简化版本：

create table Rating (
  id          INT,
  description VARCHAR(20),
  value       INT
);

create table Category (
  id            INT,
  categoryName  VARCHAR(20),
  parentId      INT,
  path          VARCHAR(100)
);

create table Movie (
  id              INT,
  categoryId      INT,
  title           VARCHAR(50),
  description     VARCHAR(500),
  editorsRatingId INT,
  usersRatingId   INT
);

insert into Rating values (34, 'Bad', 1);
insert into Rating values (35, 'Good', 5); 
insert into Rating values (36, 'Excellent', 9);

insert into Category values (1, 'Sports', null, 'SPO');
insert into Category values (2, 'Baseball', 1, 'SPO/BAB');
insert into Category values (3, 'Motor', 1, 'SPO/MOT');
insert into Category values (4, 'Documentary', null, 'DOC');
insert into Category values (5, 'Nature', 4, 'DOC/NAT');
insert into Category values (6, 'Political', 4, 'DOC/POL');
insert into Category values (7, 'Constructions', 4, 'DOC/CON');

insert into Movie values (1, 3, 'A motor sports title', 'A motor sports description', 35, 34);
insert into Movie values (2, 2, 'A baseball title', 'A baseball description', 35, 36);
insert into Movie values (3, 5, 'A nature documentary', 'A nature documentary description', 36, 35);
insert into Movie values (4, 7, 'A construction documentary', 'A construction documentary description', 35, 36);

这是我想要使用 QueryOver 构建的 sql：

--Try running this with 0, 1 and 4 as :parentId
select c1.id, c1.categoryName, max(subQuery.eRating) as eRating1, max(subQuery.uRating) as uRating1
from Category c1
  join (
    select c.path, max(editorsRating.value) as eRating, max(usersRating.value) as uRating
    from Category c
      join Movie m on (m.categoryId = c.id)
      join Rating editorsRating on (editorsRating.id = m.editorsRatingId)
      join Rating usersRating on (usersRating.id = m.usersRatingId)
    group by c.path
    ) as subQuery on (subQuery.path = c1.path or subQuery.path like concat(c1.path,'/%'))
where coalesce(c1.parentId, 0) = :parentId
group by c1.id, c1.categoryName 

评级值应始终返回在所有级别的每个类别项的子类别中找到的聚合最大值。

如果有人能给我提示，特别是在连接值的子查询连接部分，我将不胜感激。