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
>>
>>
>>
>