HQL Query to exclude items with spcfc collection elmntproprt

frostfreek · **Joined:** Wed Jun 27, 2007 3:26 pm **Posts:** 7

I have been pulling my hair out trying to get this query to work.

Let's say, with Annotations,

Code:

@Entity
class Family
{
    @Id 
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    int family_id;

    @OneToMany(targetEntity=Person.class, cascade = CascadeType.ALL, mappedBy = "family")
    public Set<Person> familyMembers = new HashSet<Person>();
}

@Entity
class Person
{
    @Id 
    @GeneratedValue
    private int person_id;
 
    @ManyToOne
    @JoinColumn(name="family_id")
    public Family family;
   
    public enum GENDER{MALE, FEMALE, OTHER};
    @Enumerated(value=EnumType.STRING)
    public GENDER gender;
}

Assuming that I got that right, a 'Family' contains a collection of Person.

Now, I can create a query to select a list of Family objects, where there exists a familyMember of a specific gender (eg., a family with a MALE in it):

Code:

from Family as family where family.familyMembers.gender='MALE'

However, I cannot figure out an efficient query to find a family that does NOT contain a member of a specific gender.

The best I have acheived is to use a subselect. However, with 1000 (Family) rows, my subselect takes over a half a second. I need to support 1000's of users, and 1000000s of rows. (I am currently using paging of 50 results).

Code:

from Family where 'MALE' not in (select gender from family.familyMembers)

I have tried all manner of with,(not) exists,in,elements, etc... and most end in syntax exceptions.

Thanks for any help, it is greatly appreciated!

- Jamie.[/code]

himawan · **Joined:** Sat Aug 19, 2006 6:31 pm **Posts:** 139

How about

from Family as family where family.familyMembers.gender!='MALE'

frostfreek · **Joined:** Wed Jun 27, 2007 3:26 pm **Posts:** 7

Unfortunately, that returns all family objects that contain a FEMALE or OTHER, whether or not there's a MALE in it. :-(

Thanks, tho...