How to add an extra condition in ON clause in eagerly loaded HQL queries?
Example:
select a from EntityA as a
fetch outer join EntityB as b
on a.B=b and a.CreatedDate=b.CreatedDate;
I am getting this error:
with-clause not allowed on fetched associations; use filters
But if I add condition in where clause, we will miss out some records of EntityA which do not have an associated EntityB
See Hibernate ORM 6.2.7.Final User Guide for filter options.
@beikov This won’t solve our use-case. We want to add the condition in ON clause not WHERE clause.
Because if we are doing a LEFT OUTER JOIN, in that case if the joined entity is null, it won’t give any results. But if the same condition is put in ON clause, it will give all left table entities even if the right entity is null.
Example:
select * from A left outer join B ON A.ID = B.A_FK AND A.CREATED_DATE = B.CREATED_DATE;
This query will give A records even if B is null. This is the query we want to generate using HQL
But on the other hand, the below query will not give such records.
select * from A left outer join B on A.ID = B.A_FK where A.CREATED_DATE = B.CREATED_DATE;
This won’t solve our use-case.
It will, because Hibernate filters should be applied in the ON clause.
@beikov I still don’t quite understand the use-case of Filters here:
I see that we are able to add multiple join conditions using Hibernate Criteria query, but why is that not allowed in HQL queries? Or am I missing something?
Any ON condition that you add to a fetch join might lead to data loss when flushing data back to the database, which is why Hibernate ORM tries hard to prevent you from shooting off your own leg.
Managed entities and filtering of associations only works safely through filters. If you have different join conditions, consider mapping the tables again in a different entity model or use a DTO approach (i.e. avoid fetch joins altogether).
Hi @beikov , I just wanted to say that I don’t understand this argument of “Hibernate ORM tries hard to prevent you from shooting off your own leg” at all. When you don’t use cascading of collections this is not a problem at all, and even if you do, then you would probably know what you are doing when you specify additional join condition.
Imagine a simple case of having a table User and Role. Each User can have 0 or many Roles, and each Role has validity dates “validFrom” and “validTo”. I want to fetch every User with their active Roles (validFrom <= now <= validTo). But I also want to fetch a User if he doesn’t have any active Roles. This simple use case is impossible to do in a single select without a “join on condition”, because some Users might have only inactive Roles, and some might not have any Role.
No @Filter or @Where annotation helps because they are applied to WHERE part of the query, at least according to hibernate documentation (unlike what you said earlier that “Hibernate filters should be applied in the ON clause”).
Will this really never be supported, because you want to protect us from potentially losing data?
This is not true. Filters on collections are applied in the ON clause. If that is not the case, this is a bug.
The way to go is to use filters. Period.
Ok, I actually tested it, and you are correct that when @Filter is applied to a collection that we are join fetching, the condition is applied to the ON clause.
However, literally none of your examples at Hibernate ORM User Guide show it or mention this fact. None of the generated SQL queries there have additional ON clause, because none of the examples use @Filter on “join fetch” collection. I think it is crucial to add such examples, given that it is the only way to achieve additional conditions in ON clauses.
So, I hope you understand why I thought it would be applied to WHERE.
You just assumed something, because you couldn’t find information in the user guide. It’s best to try things out though if you’re unsure and when you have questions then, ask them 
If you think that an example in the docs is going to help users, please provide a PR against our Git repository.