On 3/15/2011 3:31 AM, Emmanuel Bernard wrote:
> 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.
>
Thanks -- yes.
> 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.
>
Same here. We could add it later if requested.
> 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
>>>>
>>>>
>>>>
>