Duplicate issue using Grails withCriteria with many-to-many relationship

Question

I have two tables: Question and Answer, with a many-to-many relationship (that is, questions can have multiple answers and answers that can be reused by multiple Questions). For normalization, I have a cross-reference table between them named Question_Answer, which has a two-to-one relationship with both tables. These are their class definitions:

class Question { int id int text static hasMany = [questionAnswers : QuestionAnswer] } class Answer { int id int text static hasMany = [questionAnswers : QuestionAnswer] } class QuestionAnswer { int id Question question Answer answer } 

I am trying to get a list of answers based on certain criteria. Here is my criteria request (using the Grails withCriteria function):

 def listing = Answer.withCriteria { cache false order "id", "asc" eq("id", myAnswerID) questionAnswers { question { isNotNull("text") } } } 

Here is an example of the problem I am facing:

I have an answer that matches 3 different Questions. What I want in the listing is a 1 Answer object, with its questionAnswers list populated with three matching QuestionAnswer objects. Instead, I get 3 identical Answer objects, all with their filled questionAnswers lists.

Is there an easy way to achieve what I want? I hope that I am just missing something small.

Any help / suggestions are appreciated.

Thanks BJ

Answer 1

Try adding this to your query to tell the criteria to return only individual response objects:

 resultTransformer org.hibernate.Criteria.DISTINCT_ROOT_ENTITY