NHibernate HQL:使用“with"进行左外连接条款不起作用 [英] NHibernate HQL: left outer join with "with" clause does not work

问题描述

在 EAV 系统中，我有一个如下所示的映射:

In an EAV system, I have a mapping that looks like this:

<class name="Record">
   <map name="Values" table="RecordFieldValue">
      <key column="RecordFK">
      <index column="FieldFK">
      <element column="Value">
   </map>
</class>

我想选择一些记录，按特定字段的每个记录的值排序.但是，请注意，并非所有记录实际上都具有该字段的值.在这种情况下，仍应提取记录并使用空值对其进行排序.

I would like to select some Records, ordered by the value of each Record for a specific Field. However, note that not all Records will actually have a Value for that Field. In this case, the record should still be fetched and sorted with a null value.

所需的 SQL 如下所示:

The desired SQL would look like this:

select rec.*, val.Value
from Record rec
left outer join RecordFieldValue val
on val.RecordFK = rec.PK and val.FieldFK = :field
order by val.Value

经过大量挖掘，我发现在HQL中修改left join的on"子句的正确方法是使用with"关键字(参见https://nhibernate.jira.com/browse/NH-514).所以我尝试了这个 HQL:

After a lot of digging, I found that the correct way to modify the "on" clause of the left join in HQL is with the "with" keyword (see https://nhibernate.jira.com/browse/NH-514). So I tried this HQL:

from Record rec
left join rec.Values vals with index(vals) = :field
order by vals

不幸的是，这会产生以下错误:with-clause 表达式没有引用与 with-clause 相关联的 from-clause 元素.所以我尝试了这个:

Unfortunately, this produces the following error: with-clause expressions did not reference from-clause element to which the with-clause was associated. So I tried this instead:

from Record rec
left join rec.Values vals with index(rec.Values) = :field
order by vals

但这产生了一个新错误:with 子句只能引用驱动表中的列.

But that produced a new error: with clause can only reference columns in the driving table.

关于如何获得这项工作的任何想法?谢谢.

Any ideas on how to get this work? Thanks.

--布赖恩

推荐答案

这有效:

from Record rec
left join rec.Values vals with vals.index = :field
order by vals

不完全直观或有据可查，但它可以完成工作.

Not exactly intuitive or well-documented, but it gets the job done.

这篇关于NHibernate HQL:使用“with"进行左外连接条款不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！