title: NHibernate HQL: left outer join with "with" clause does not work
body: <p>In an EAV system, I have a mapping that looks like this:</p>
  
  <pre><code>&lt;class name="Record"&gt;
     &lt;map name="Values" table="RecordFieldValue"&gt;
        &lt;key column="RecordFK"&gt;
        &lt;index column="FieldFK"&gt;
        &lt;element column="Value"&gt;
     &lt;/map&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>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.</p>
  
  <p>The desired SQL would look like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <a href="https://nhibernate.jira.com/browse/NH-514" rel="nofollow">https://nhibernate.jira.com/browse/NH-514</a>). So I tried this HQL:</p>
  
  <pre><code>from Record rec
  left join rec.Values vals with index(vals) = :field
  order by vals
  </code></pre>
  
  <p>Unfortunately, this produces the following error: <em>with-clause expressions did not reference from-clause element to which the with-clause was associated</em>. So I tried this instead:</p>
  
  <pre><code>from Record rec
  left join rec.Values vals with index(rec.Values) = :field
  order by vals
  </code></pre>
  
  <p>But that produced a new error: <em>with clause can only reference columns in the driving table</em>.</p>
  
  <p>Any ideas on how to get this work? Thanks.</p>
  
  <p>-- Brian</p>