-->
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.  [ 3 posts ] 
Author Message
 Post subject: Finding distinct values from a set
PostPosted: Wed May 17, 2006 5:25 pm 
Newbie

Joined: Wed May 17, 2006 4:56 pm
Posts: 2
I have a question about how to select a set of distinct IDs from a set of objects a Hibernate object object references. For this example, assume I have a Person class, and a Child class. A Person has a set of Child (as seen in Person class below). Each Child has an ID.

Code:
@Entity
public class Person {
   private Set<Child> _children = new HashSet<Child>();
    private String name;
   
   @ManyToMany(targetEntity=Child.class, cascade=CascadeType.ALL)
   @JoinTable(
         name="CHILD_MAP",
         joinColumns={@JoinColumn(name="PERSONID")},
      inverseJoinColumns={@JoinColumn(name="CHILDID")}
   )
   public Set<Child> getChildren() {
      return _children;
   }
   public void setChildren(Set<Child> children) {
      this._children = children;
   }
   
}



Before Hibernate, I was able to do a query something like this, to get a distinct list of the parent's name, and all children IDs:

Code:
SELECT DISTINCT
  P.name,
  CMAP.childid
FROM
  child_map CMAP,
  person P
WHERE
  CMAP.personid = P.id


However, I am confused about how to do this with HQL.
I thought that I could do this in HQL:

Code:
select distinct
  p.children.id,
  p.name
from
  Parent p


This does not seem to work, and I get an error like:
Code:
Invalid path: 'p.children.id'


How do I work with a set when I want to get distinct elements back in a join like this? I have seen examples where the set is used in the WHERE clause to filter results (e.g. WHERE exists elements(p.children)), but none of these examples seem applicable to my problem. There is probably a simple answer to this, but I have not thought of it yet. Where can I find some good examples that work with mapping sets?

Thanks!


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 17, 2006 5:47 pm 
Expert
Expert

Joined: Fri Jul 22, 2005 2:42 pm
Posts: 670
Location: Seattle, WA
try this:
select distinct c.id, p.name
from Parent p join p.children c

_________________
--------------
Konstantin

SourceLabs - dependable OpenSource systems


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 17, 2006 6:31 pm 
Newbie

Joined: Wed May 17, 2006 4:56 pm
Posts: 2
Thank you kgignatyev, that is exactly what I was looking for!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 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.