Error duplicating the association path in sleep mode

Question

I have a problem when I try to programmatically build a Criteria object based on user search criteria. Simply put, the user can enter one or more search criteria and, based on the fact that they are included, I create a Criteria object, execute a query based on this and show the results. Unfortunately, when I use certain search criteria together, I get this error:

repeating association path: assignment

In my database, I have a table containing problems (called Issue). A problem can have several people assigned to it in different roles (for example, an auditor and QA specialist can be assigned to her). Jobs are saved in a separate table called Assignment. The tables look something like this:

+-------------+ +-----------------+ | Issue | | Assignment | +-------------+ +-----------------+ | issueID | | assignmentID | +-------------+ | issueID | | assigneeID | | isActive | | reviewType | +-----------------+ 

The column identifier column is the primary key in Issue and the foreign key in Assignment.

In my Java code, I have something like this:

 public List<MortalityCase> searchCases(SearchCriteria criteria) { Criteria search = HibernateUtil.getSession().createCriteria(Issue.class); if ( criteria.getIssueNumber() != null ) { search.add(Restrictions.eq("issueNumber", criteria.getIssueNumber())); } ... if ( criteria.getAuditor() != null ) { search.createCriteria("assignments") .add(Restrictions.eq("assignee", criteria.getAuditor())) .add(Restrictions.eq("isActive", "Y")) .add(Restrictions.eq("reviewType", getReviewType(TypeOfReview.AUDITOR))); } if ( criteria.getQASpecialist() != null ) { search.createCriteria("assignments") .add(Restrictions.eq("assignee", criteria.getQASpecialist())) .add(Restrictions.eq("isActive", "Y")) .add(Restrictions.eq("reviewType", getReviewType(TypeOfReview.QA_SPECIALIST))); } ... return search.list(); } 

This works great if the user is looking for problems assigned to either the Auditor or a QA specialist. However, if the user tries to find the problem assigned to both this auditor and this QA specialist, I get the above error.

I found this thread on the Hibernate forums, which was launched 7 years ago and indicates that this is a limitation for Hibernate. I was hoping that something would be done since then, but the stream really remained “alive” until June 2010 without mentioning the answer / correction.

Is there a way to do what I'm trying to do here?

Answer 1

If both getAuditor() and getQASpecialist() not equal to zero, then you do not want the query to be executed, where the value of assignee is equal to criteria.getAuditor() OR criteria.getQASpecialist() ?

eg:

 if ( criteria.getAuditor() != null && criteria.getQASpecialist() != null) { search.createCriteria("assignments") .add(Restrictions.or( Restrictions.eq("assignee", criteria.getAuditor()), Restrictions.eq("assignee", criteria.getQASpecialist()) ) .... }