NHibernate HQL: left outer join with clause does not work

Question

In the 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 sorted by the value of each record for a specific field. However, note that not all records will have a value for this field. In this case, the record should still be selected and sorted with a null value.

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 

After repeated searches, I found that the correct word for the "on" sentence of the left join in HQL is the keyword "c" (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 

Unfortunately, this leads to the following error: in-clause expressions do not reference the clause element to which the with condition was bound. So I tried this:

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

But this gave rise to a new error: the cl clause can only refer to columns in the driving table.

Any ideas on how to get this working? Thanks.

- Brian

Answer 1

It works:

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

Not entirely intuitive or well documented, but it does its job.