代码之家 › 专栏 › 技术社区 › Joseph

Left Join过滤掉了我希望在使用HQL时显示的行

left-join hql nhibernate sql-server

Joseph · 技术社区 · 14 年前

我有一些类似于以下HQL的东西:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where w1.ManufacturedDate > :manufacturedDate
and w2.ManufacturedDate > :manufacturedDate
and w3.ManufacturedDate > :manufacturedDate
group by car.Make

这个HQL是在我的用户运行报表时通过他们的选择动态生成的。这里需要注意的重要一点是,用户正在动态选择筛选,这会导致此HQL动态构建where子句。

我的where子句生成工作正常。

我的问题是,如果在SQLServer2008中运行生成的查询,最终结果是如果有轮子(w1、w2或w3)没有任何如果ManufacturedDate大于给定参数,则报告将被完全删除省略那张唱片。

我发现了原因, in this article here .

我的问题是,我无法将这些过滤器添加到HQL中的with子句中。在上面的例子中,我可以将约束添加到With子句中,但我构建的报告引擎让用户可以进行比这更高级的过滤,其中大部分是With子句中不允许的。我曾经尝试过将一些生成的HQL放入with中,并验证了这一点,大多数查询都不会运行,因为它们需要对其他表进行额外的约束。

所以我的问题是:

有没有办法修改这个HQL来找回丢失的记录?在我上面列出的那篇文章中,有人提到,如果你考虑到空值,你可以拿回记录,但我不知道他指的是什么方式,如果可能的话,我肯定想知道怎么做。

使现代化

我不得不将动态过滤器放在where子句中,因为用户可能也会这样做:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where w1.Buyer.FirstName = :firstName
and w2.Buyer.FirstName = :firstName
and w3.Buyer.FirstName = :firstName
group by car.Make

如果我重写with子句

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1 with w1.Buyer.FirstName = :firstName
left join Wheel w2 with w2.IsFrontWheel == true 
    and w2.Buyer.FirstName = :firstName
left join Wheel w3 with w3.IsFrontWheel == false 
    and w3.Buyer.FirstName = :firstName
group by car.Make

不会运行,NHibernate抛出异常。

4 回复 | 直到 14 年前

KM. 14 年前

您的问题是筛选WHERE子句中的左连接列:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where w1.ManufacturedDate > :manufacturedDate --<<<<
and w2.ManufacturedDate > :manufacturedDate   --<<<<
and w3.ManufacturedDate > :manufacturedDate   --<<<<
group by car.Make

试试这个:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1 with w1.ManufacturedDate > :manufacturedDate
left join Wheel w2 with w2.IsFrontWheel == true AND w2.ManufacturedDate > :manufacturedDate
left join Wheel w3 with w3.IsFrontWheel == false AND w3.ManufacturedDate > :manufacturedDate
group by car.Make

当你比较 w2.ManufacturedDate > :manufacturedDate 这就像 null > :manufacturedDate 结果是NULL,行被丢弃,突出起来就像是一个内部连接。将条件移动到“左联接”的“开”位置可防止出现这种情况。

编辑
试试这个:

select count(distinct car.id), 
count(distinct w1.id), 
count(distinct w2.id), 
count(distinct w3.id)
from Car car
left join Wheel w1
left join Wheel w2 with w2.IsFrontWheel == true
left join Wheel w3 with w3.IsFrontWheel == false
where ISNULL(w1.ManufacturedDate,'99991231') > :manufacturedDate
and ISNULL(w2.ManufacturedDate,'99991231)' > :manufacturedDate
and ISNULL(w3.ManufacturedDate,'99991231') > :manufacturedDate
group by car.Make

Frederik Gheysels 14 年前

查看HQL中的“with”关键字 click

Matt Gibson 14 年前

我要去潜水,尽管我对HQL一无所知:)

如果需要避免使用WITH,那么无论结果发生了什么,都需要WHERE子句为true。从你的问题来看,我并不完全清楚这种情况,但大致如下:

where ISNULL(w1.ManufacturedDate, *some_future_value*) > :manufacturedDate

...其中,*some_future_value*是您以某种方式生成的一个值,它比manufacturedDate参数更接近未来。所以,也许你的最大可能日期值,或“:manufacturedDate+1”,或任何适合你的东西。

Diego Mijelshon 14 年前

尝试以下方法:

left join Wheel w1
left join w1.Buyer b1 with b1.FirstName = :firstName

其他的都一样。应该可以。