-->
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.  [ 6 posts ] 
Author Message
 Post subject: Criteria nested select using max of child collection
PostPosted: Mon Sep 05, 2016 5:21 am 
Newbie

Joined: Mon Sep 05, 2016 4:18 am
Posts: 3
Hi,
I have:

Code:
@Entity
public class Parent implements Serializable {
   private static final long serialVersionUID = 1L;

   @Id
   private Integer id;
   private String name;
   @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.MERGE)
   @JoinTable(name = "PARENT_CHILD", joinColumns = {@JoinColumn(name = "PARENT_PID")}, inverseJoinColumns = {@JoinColumn(name = "CHILD_PID")})
   @OrderBy("persistenceId DESC")
   private List<Child> childs = new ArrayList<Child>();
}


Code:
@Entity
public class Child implements Serializable{   
   private static final long serialVersionUID = 1L;

   @Id
   private Integer id;   
   private String name;
   private Integer age;
}


----------------------
Parent
----------------------
id |name
----------------------
1 |John
2 |Kevin
3 |David

----------------------
Child
----------------------
id |name |age |parent_id
----------------------
1 |Marc |18 |1
2 |Jim |10 |1
3 |Bob |17 |1
4 |Steve |17 |2
5 |Anne |21 |2
6 |Simon |23 |2
7 |Carl |25 |3
8 |Matt |17 |3
9 |Jason |3 |3

I want only the parent where the last child inserted in DB has 17 years old.

Expeted result is:
1 |John
The 3rd child Bob's has 17 years old.

With hibernate Criteria, what is the solution?

Thanks in advance


Top
 Profile  
 
 Post subject: Re: Criteria Many to Many inner join: search in last record
PostPosted: Mon Sep 05, 2016 5:34 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
First of all, I'd recommend you switch to LAZY instead of EAGER because EAGER is bad for collections.

Code:
@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.MERGE)


Second, you need to think of the SQL query first prior to trying to translate it to Criteria. What SQL query have you come up with?


Top
 Profile  
 
 Post subject: Re: Criteria Many to Many inner join: search in last record
PostPosted: Mon Sep 05, 2016 5:58 am 
Newbie

Joined: Mon Sep 05, 2016 4:18 am
Posts: 3
In SQL is:

Code:
SELECT p.*, c.*
FROM Parent p
INNER JOIN Parent_Child pc ON p.id = pc.Parent_pid
INNER JOIN Child c ON c.id = pc.Child_pid
WHERE c.id = (
    SELECT Max(pc1.Child_pid) as id1
    FROM Parent_Child pc1
    WHERE pc1.Parent_pid = p.id
) AND c.age = 17;


Thanks


Top
 Profile  
 
 Post subject: Re: Criteria Many to Many inner join: search in last record
PostPosted: Mon Sep 05, 2016 6:28 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
First, this SQL query can be translated to HQL like this:

Code:
SELECT p.
FROM Parent p
JOIN p.childs c
WHERE c.id = (
    SELECT max(c1.id) a
    FROM Child c1
    WHERE c1.parent = p
) AND c.age = 17;


For Criteria, try this query:

Code:
DetachedCriteria innerCriteria = DetachedCriteria.forClass(Child.class, "inner")
    .add(Restrictions.eqProperty("inner.parent.id","upper.id"))
    .setProjection(Projections.projectionList().add(Projections.max("inner.id")));

DetachedCriteria outerCriteria= DetachedCriteria.forClass(Parent.class, "upper");
outerCriteria.createAlias("upper.childs", "children");
outerCriteria.add(Subqueries.propertyEq("children.id", innerCriteria ));
outerCriteria.add(Restrictions.eqProperty("children.age", 17));

List<Parent> parents = outerCriteria.getExecutableCriteria(session).list();


Top
 Profile  
 
 Post subject: Re: Criteria nested select using max of child collection
PostPosted: Mon Sep 05, 2016 10:33 am 
Newbie

Joined: Mon Sep 05, 2016 4:18 am
Posts: 3
Thanks,
I try it.

Jenna


Top
 Profile  
 
 Post subject: Re: Criteria nested select using max of child collection
PostPosted: Thu Sep 08, 2016 8:53 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
It worked, right?


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 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:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.