Hi,

My goal is to fill up following collection and select only the row which has latest date.
I tried @Where and @WhereJoinTable as well. Got two kind of errors. First is ora-01799
"a column may not be outer-joined to a subquery"
second is ORA-00904 "Invalid identifier".
I spent a day to find out how to do that without luck.
Any ideas are welcome.



Regards., Sandor

Szia Sandor,

One issue is the use of EAGER:



Don't do that! It's very bad for performance, and that's also the cause of your issue:



Now, going back to your mapping. The difference between @Where and @WhereJoinTable is that the former applies the HrpBbmchi07 table while the latter to the intermediary table in a many-to-many relationship.

In your case, you are better off using @JoinFormula:

Assuming the Parent entity table where the @OneToMany annotation is now stored is called PARENT:



Now, the @OneToMany becomes:



Vlad

Hi,

Thanks for the useful advices. I have couple of questions need to be clarified.

For better understanding my scenario.
I have a parent entity (HrpBbstorzs https://pastebin.com/TCrVJBGT) which is for employee data and a collection need to be filled up (HrpBbmchi07 https://pastebin.com/4Re7g7Xr) which contains managers belong to particular employee.
Employee can has more than one manager and managers can have multiple employees.

Should I put this relation into HrpBbmchi07 entity ?

@ManyToOne
@JoinFormula(
"(HRP_BBMCHI07.COMPANY = PARENT.COMPANY AND HRP_BBMCHI07.BBM_BBST_TORZSSZAM = PARENT.TORZSSZAM AND VALTDATUM in (select max(VALTDATUM) from HRP_BBMCHI07 F where F.FELETTESTORZSSZAM=FELETTESTORZSSZAM AND F.BBM_BBST_TORZSSZAM=BBM_BBST_TORZSSZAM AND F.COMPANY=COMPANY group by valtdatum))"
)
private Parent parent;

If so and

@OneToMany(mappedBy = "parent")
List<HrpBbmchi07> felettes = new ArrayList<>();

is kept in HrpBbstorzs then the following error occurs.

org.hibernate.AnnotationException: A Foreign key refering hu.bluesystem.hrportal.model.HrpBbstorzs from hu.bluesystem.hrportal.model.HrpBbmchi07 has the wrong number of column. should be 2

This is why HrpBbstorzs has composite key with 2 columns and in other hand HrpBbmchi07 has 3 of them (valtdatum is the third).

TIA,

Sandor

In this case, then keep thee @JoinColumn on the @ManyToOne side:



And on HrpBbstorzs:

Works but my collection did not get filtered. Still contains two records :(.

What's the logged JDBC statements. Try enabling SQL statement logging.

This is what I have now in HrpBbmchi07



And here is the sql statement:



It seems to me that @Where annotation completely ignored in this case.

It looks like a bug. You should create a replicating test case and see if it happens with Hibernate 5.2, and then open a new Jira issue.

I use latest 5.2.12.Final.
I created a test case as you recommended. Filled up some test data but I can't model the use case. My persistent bag (felettes) in HrpBbstorzs is empty.

Would you please have a look this: https://github.com/sfeher/hibernate-test-case-templates

TIA,