-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 
Author Message
 Post subject: Filtered collection with @Where
PostPosted: Thu Nov 23, 2017 5:04 pm 
Newbie

Joined: Fri May 25, 2012 8:52 am
Posts: 6
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.

Code:
@OneToMany(fetch = FetchType.EAGER)
    @JoinTable(name="HRP_BBMCHI07", joinColumns = {
        @JoinColumn(name = "COMPANY", referencedColumnName = "COMPANY", insertable = false, updatable = false),
        @JoinColumn(name = "BBM_BBST_TORZSSZAM", referencedColumnName = "TORZSSZAM", insertable = false, updatable = false)
    })
    @Where(clause = "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)")   
    List<HrpBbmchi07> felettes;


Regards., Sandor


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Fri Nov 24, 2017 6:02 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Szia Sandor,

One issue is the use of EAGER:

Code:
@OneToMany(fetch = FetchType.EAGER)


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

Quote:
a column may not be outer-joined to a subquery


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:

Code:
@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;


Now, the @OneToMany becomes:

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


Vlad


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Fri Nov 24, 2017 7:35 am 
Newbie

Joined: Fri May 25, 2012 8:52 am
Posts: 6
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


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Fri Nov 24, 2017 8:35 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Code:
This is why HrpBbstorzs has composite key with 2 columns and in other hand HrpBbmchi07 has 3 of them (valtdatum is the third).


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

Code:
@ManyToOne
@JoinColumns({
        @JoinColumn(name = "COMPANY", referencedColumnName = "COMPANY"),
        @JoinColumn(name = "BBM_BBST_TORZSSZAM", referencedColumnName = "TORZSSZAM")
})
@Where(clause = "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 HrpBbstorzs hrpBbstorzs ;


And on HrpBbstorzs:

Code:
@OneToMany(mappedBy = "hrpBbstorzs")
List<HrpBbmchi07> felettes = new ArrayList<>();


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Fri Nov 24, 2017 10:00 am 
Newbie

Joined: Fri May 25, 2012 8:52 am
Posts: 6
Works but my collection did not get filtered. Still contains two records :(.


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Fri Nov 24, 2017 3:14 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
What's the logged JDBC statements. Try enabling SQL statement logging.


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Mon Nov 27, 2017 5:19 am 
Newbie

Joined: Fri May 25, 2012 8:52 am
Posts: 6
This is what I have now in HrpBbmchi07

Code:
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "COMPANY", referencedColumnName = "COMPANY", insertable = false, updatable = false),
        @JoinColumn(name = "BBM_BBST_TORZSSZAM", referencedColumnName = "TORZSSZAM", insertable = false, updatable = false)
    })
    @Where(clause = "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 HrpBbstorzs bbstorzs;


And here is the sql statement:

Code:
select felettes0_.COMPANY as COMPANY2_27_0_, felettes0_.BBM_BBST_TORZSSZAM as BBM_BBST_TORZSSZAM1_27_0_, felettes0_.VALTDATUM as VALTDATUM3_27_0_, felettes0_.BBM_BBST_TORZSSZAM as BBM_BBST_TORZSSZAM1_27_1_, felettes0_.COMPANY as COMPANY2_27_1_, felettes0_.VALTDATUM as VALTDATUM3_27_1_, felettes0_.FELETTESNEVE as FELETTESNEVE4_27_1_, felettes0_.FELETTESTORZSSZAM as FELETTESTORZSSZAM5_27_1_ from HRP_BBMCHI07 felettes0_ where felettes0_.COMPANY=? and felettes0_.BBM_BBST_TORZSSZAM=?


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


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Mon Nov 27, 2017 6:35 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
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.


Top
 Profile  
 
 Post subject: Re: Filtered collection with @Where
PostPosted: Mon Nov 27, 2017 10:05 am 
Newbie

Joined: Fri May 25, 2012 8:52 am
Posts: 6
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,


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.