The doc is minimal, I'm pasting it for info
>>>
You may supply extra join conditions using the HQL with keyword.
from Cat as cat
left join cat.kittens as kitten
with kitten.bodyWeight > 10.0
<<<
http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/queryhql.html#queryhql-joins
this essentially concatenate an SQL version of kitten.bodyWeight > 10.0 to the rest of the SQL ON clause used to auto join the Cat table.
My email was really a question, I wanted to confirm that what you were proposing was essentially the same concept.
As for a full ON clause replacement, a few users and customers have requested it over the years. But we have not implemented such a feature yet. I suspect people are willing to do adhoc joins even across non mapped associations. I see it as much less useful than the proposed feature.
On 14 mars 2011, at 22:21, Linda DeMichiel wrote:
> Hi Emmanuel,
>
> Could you elaborate on what you see as the need for this. Also, I haven't been
> able to locate documentation on the Hibernate WITH construct -- could you point
> us in that direction.
>
> thanks,
>
> -Linda
>
> On 3/13/2011 3:11 PM, Emmanuel Bernard wrote:
>> Just to clarify,
>> You propose that the JP-QL ON syntax essentially add additional SQL constraints to the ON clause, is that correct?
>> In this case, I'd suggest that we use a different name than ON (would we one day want to support the full replacement of the ON clause). I know that's the reason why we've chosen the WITH keyword.
>> Emmanuel
>> On 11 mars 2011, at 22:30, Linda DeMichiel wrote:
>>> Outer joins with ON conditions have been requested a number of times.
>>>
>>> The issue is that the current JPQL and criteria API syntax do not
>>> support capturing the semantics that SQL provides.
>>>
>>> Chapter 4 of the JPA spec states that outer joins "enable the
>>> retrieval of a set of entities where matching values in the join
>>> condition may be absent."
>>>
>>> The issue appears with the semantic difference between restrictions
>>> applied in the WHERE clause and in the SQL outer join ON clause.
>>>
>>> The SQL semantics for the outer join
>>>
>>> SELECT A.*, B.*
>>> FROM A LEFT JOIN B
>>> ON C1 AND C2 ... Cn
>>>
>>> are the following:
>>>
>>> SELECT A.*, B.*
>>> FROM A, B
>>> WHERE C1 AND C2 ... AND Cn
>>>
>>> UNION ALL
>>>
>>> SELECT A.*, NULL, NULL, ..., NULL
>>> FROM A
>>> WHERE A.* NOT IN (SELECT A.*
>>> FROM A, B
>>> WHERE C1 AND C2 ... AND Cn )
>>>
>>>
>>> This means that if an A tuple ai does not join with any B tuple according to
>>> the condition C1 AND C2 ... AND Cn, then the tuple [ai.*, NULL, ....,
>>> NULL] *will* appear in the query result (even if some predicate condition Cj
>>> applies only to the A columns of A and is false for ai).
>>>
>>>
>>> To illustrate more concretely, the following example was posted
>>> in a request on my blog (thanks Bernard, if you are reading!)
>>>
>>>
>>> Let's say you want a list of car dealers and how many new
>>> vehicles they're selling.
>>>
>>> In SQL you can say:
>>>
>>> SELECT d.name, count( v.id )
>>> FROM dealer d LEFT OUTER JOIN vehicle v
>>> ON v.dealer_id = d.dealer_id AND v.type = 'New'
>>> GROUP BY d.name
>>>
>>> In JPQL, you only have the WHERE clause which limits your results,
>>> making the outer join useless:
>>>
>>> SELECT d.name, count( v.id )
>>> FROM dealer d LEFT OUTER JOIN d.vehicleList v
>>> WHERE v.type = 'New'
>>> GROUP BY d.name
>>>
>>>
>>> Were we to include support for the "ON" syntax and semantics,
>>> the JPQL equivalents would be the following:
>>>
>>> SELECT d.name, count(v.id)
>>> FROM dealer d LEFT OUTER JOIN d.vehicle v
>>> ON v.type = 'New'
>>> GROUP BY d.name
>>>
>>> and
>>>
>>> SELECT d.name, count(v.id)
>>> FROM dealer d LEFT OUTER JOIN d.vehicle v
>>> WHERE v.type = 'New'
>>> GROUP BY d.name"
>>>
>>> In the first of these queries, all dealers are included in the result,
>>> including dealers with no vehicles that qualify.
>>>
>>> In the second, we're only looking at dealers that have at least one
>>> new vehicle.
>>>
>>>
>>> I propose that we should add support for the JPQL syntax
>>>
>>> LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable
>>> [ON conditional_expression]
>>>
>>> with the semantics that:
>>>
>>> SELECT a, b.x1, b.x2, ... bxn
>>> FROM A a LEFT JOIN a.b b
>>> ON c1...cn
>>>
>>> is equivalent to the semantics of
>>>
>>> SELECT a, b.x1, b.x2, ... bxn
>>> A a JOIN a.b b
>>> WHERE c1...cn
>>>
>>> unioned with
>>>
>>> SELECT a, null1, null2, ... nulln
>>> FROM A a
>>> WHERE a NOT IN (SELECT a' FROM A a' JOIN B b'
>>> WHERE c1...cn)
>>>
>>>
>>> For the criteria API equivalent, we could add methods
>>>
>>> on(Expression<Boolean> restriction);
>>> and
>>>
>>> on(Predicate... restriction);
>>>
>>> to the Join interface.
>>>
>>> I'm not however sure what it is best that these methods return.
>>>
>>> To be complete, we'd also need the method
>>>
>>> Predicate getOn();
>>>
>>> on the Join interface as well.
>>>
>>>
>>> Finally, we should also discuss whether we should support the use of
>>> ON with inner joins. SQL supports this, with the semantics that it is
>>> equivalent to the use of WHERE.
>>>
>>>
>>> Opinions??
>>>
>>> thanks,
>>>
>>> -Linda
>>>
>>>
>>>