Hibernate ManyToMany - only return unique elements

Question

I have three tables:

Company
id  name
1   Fred Co.

Person
id   name
1    Fred

Employee
id company_id person_id rank
1  1          1         1
2  1          1         2

Now the rank is used by a different application, but for what I'm using I don't care about it. What I want to do is create a hibernate mapping (via the domain model) that will return only the unique employees.

I currently have

class Person {
    @Column(name = "id")
    Long id;

    @Column(name = "name")
    String id;
}

class Company {
    @Column(name = "id")
    Long id;

    @Column(name = "name")
    String id;

    @ManyToMany(fetch = FetchType.LAZY)
        @JoinTable(name = "EMPLOYEE", 
                joinColumns = { @JoinColumn(name = "COMPANY_ID") },
                inverseJoinColumns = { @JoinColumn(name = "PERSON_ID") })
    @LazyCollection(LazyCollectionOption.EXTRA)
    List<Person> employees = new ArrayList<Person>();
}

But this means that fredCo.employees.size() == 2. How do I filter so that only unique people are returned?

I'd like to do this via the domain model.

Edit: While the example above is small, in real life the Person object is quite large and has 1000s of employees per company (thus extra lazy).

Now I can (and have) got a

companyDao.getEmployeesFor(Company...)
companyDao.getEmployeeCountFor(Company...)

but it feels a little icky and I'd like to do it without Dao.

Answer 1

If the rank is always set and is always '1' for the first record then you could add a simple @Where/@WhereJoinTable clause to the mapping restricting the collection to those records with a rank of 1.

https://access.redhat.com/knowledge/docs/en-US/JBoss_Enterprise_Application_Platform/5/html/Hibernate_Annotations_Reference_Guide/entity-hibspec-collection.html

AN alternative would be to change the collection to a Set and implements an equals() method on Person that does not involve the 'rank' field.

Update:

Okay, as you stated previously this will not work. I have done some testing and it also seems that when @LazyCollection is defined on an association an @Where restriction on this association is ignored anyway. Calling size() on the extra-lazy collection actually leads Hibernate to issue a count() query against the database. The additional clause is not appended.

Why the @Where should be ignored I have no idea but this has been queried elsewhere and seems to be an open issue:

https://forum.hibernate.org/viewtopic.php?f=9&t=988631&view=previous

https://hibernate.onjira.com/browse/HHH-3319

I think you can still do what you want by creating an additional Employee entity which maps to a VIEW defined using the previous SQL inner query - the filtering is then done at the db level. Mapping an entity to a view is just the same as a table.

Then replace your many-to-many with two one-to-many associations from Company and Person to Employee.