Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Passing parent entities field values to @Filter annotation
PostPosted: Wed Mar 15, 2017 7:16 am 
Newbie

Joined: Tue Mar 14, 2017 1:40 pm
Posts: 2
Using Hibernate 5.1.0 and MySQL to store a tree structure in the database.
The structure looks like following:

Code:
class Node {

@Id
private int id;

@ManyToOne
private Node parent;

@OneToMany(fetch = EAGER)
private Set<Node> children = new HashSet<>();

@OneToMany(fetch = EAGER)
@Filter(name = "lastRecordsFilter", condition = ** Descripted below **)
private Set<Value> values = new HashSet<>();

}


As you can see the child nodes and value are fetched eagerly. This is necessary, as I am doing calculations on the parent node, which depend on the parent nodes values and child values.
But not of all values, every recorded. Just the last recorded for every type, as a value has a timestamp and a type.
For further understandment a look on the Value entity:

Code:
class Value {

@Id
@ManyToOne
@JoinColum(name = "node_id", referrencedColumnName = "id")
private Node node;

@Id
private Date timestamp;

@Id
private String type;

private double value;

}


A simple filter condition to receive the wanted values would be:

Quote:
condition = "(node_id, type, timestamp) IN (SELECT v.type, MAX(v.timestamp) FROM Value v WHERE v.node_id = :node_id GROUP BY v.type DESC)"


Obviously when fetching the values for the children it would substitute :node_id with the parent's id, which I define when enabling and setting filter parameters, and therefore returns empty value sets. The query created by Hibernate looks something like:

Quote:
select * from Value va0_
where (va0_.node_id, va0_.type, va0_.timestamp) IN (SELECT v.type, MAX(v.timestamp) FROM Value v WHERE v.node_id = ?1 GROUP BY v.type DESC)
and va0_.node_id = ?1


?1 is the filter parameter and therefore stays the same, parent node_id for all children.
?2 is correctly replaced by the correct node_id for which the values are retrieved.

To solve this problem I used a hack and changed the filter to.

Quote:
condition = "(node_id, type, timestamp) IN (SELECT v.type, MAX(v.timestamp) FROM Value v WHERE v.node_id = ? GROUP BY v.type DESC)#"


Meaning Hibernate substitutes the first parameter, like it is a query parameter and not a filter parameter.
And to avoid an exception, as the number of parameters in the query would be greater than the number of given parameters, the end part is commented out by adding # to the end.

This can't be a good solution, that's why I am asking here how I could solve this a proper way.


Top
 Profile  
 
 Post subject: Re: Passing parent entities field values to @Filter annotation
PostPosted: Wed Mar 15, 2017 2:43 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1038
I would drop the EAGER fetching which is almost always a bad thing, and I would just fetch the ids of the entities that I need to load with an SQL query, then use an entity query to fetch the parent entities along with their children by the parent entity identifiers from the first query.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Passing parent entities field values to @Filter annotation
PostPosted: Tue Mar 21, 2017 12:00 pm 
Newbie

Joined: Tue Mar 14, 2017 1:40 pm
Posts: 2
vlad wrote:
I would drop the EAGER fetching which is almost always a bad thing, and I would just fetch the ids of the entities that I need to load with an SQL query, then use an entity query to fetch the parent entities along with their children by the parent entity identifiers from the first query.


Thanks for the answer vlad. I am changed the fetch type to lazy loading, but am facing problems with the filter.

I don't know how to filter the values based on the owning enitites id and not the parent id.


Top
 Profile  
 
 Post subject: Re: Passing parent entities field values to @Filter annotation
PostPosted: Tue Mar 21, 2017 12:10 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1038
The @Filter condition takes an SQL clause so you can filter be the currently annotated entity attributes. Why do you think you cannot reference the currently annotated entity identifier?

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 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.