Add extra join condition in ON clause in Eagerly Loaded HQL Queries

Hibernate ORM
1

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

2

See Hibernate ORM 6.2.7.Final User Guide for filter options.

3

@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;
4

This won’t solve our use-case.

It will, because Hibernate filters should be applied in the ON clause.

5

@beikov I still don’t quite understand the use-case of Filters here:

  1. We don’t have a value to add for Filter. We want to compare property of Table A with that of Table B. As per my understanding we can’t do that using Filters.
  2. If we have multiple tables in the query, then we will have to create Filter for each of the table and enable it after beginning the session. This doesn’t look like the right solution for our use-case.

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?

6

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