Grails criteria query retruing duplicate instances

Question

I have a domain class called Order and that class has hasMany relation with Item class. When I am querying for the list of orders with certain restrictions I am getting as many instances of Order as there are items.

So for example Order instance has say references to 3 instances of Item then , criteria call on Order is returning 3 duplicate instances of Order. I am not sure but if it's worth mentioning that the domain class Order has fetchMode set to "eager".

I am really puzzled with what's going on there. Any help in this regard will be greatly appreciated. Snippet of code is attached:

def clazz = "cust.Order"
def criteria = clazz.createCriteria()
        println("clazz == "+Order.list())// returning correct data i.e unique instance of order
        def filter = {
                    // trimmed down all filtering criteria for debugging
            }//close filter
        List results = criteria.list(max:params?.max,offset:params?.offset,filter)
            results.each{Object data->
                println(data.getClass())
            }
        println("results == "+results)

Thanks again

Northman

Answer 1

Criteria API is just a wrapper for constructing a SQL query. In your case, the query in question has JOINs in it (because of the eager fetching), and returns a cartesian product of Orders and their matching Items. Each row returned is included in results as a separate Order instance.

The easiest way to remove duplicates is to put all the results in a Set, like this:

def resultSet = new HashSet()
resultSet.addAll(results)
println("results == " + resultSet)

Disregard

This worked but is there any better way to solve this ? Like changing fetch mode during query or any thing like that ? – Northman

Answer 2

One solution is to use this inside your query:

resultTransformer org.hibernate.Criteria.DISTINCT_ROOT_ENTITY

Elisabethelisabethville

Answer 3

If you call criteria.listDistinct instead of criteria.list duplicates will be eliminated

Mccallum

It should be noted that this can change the type of the results from a ResultSet to a List of Domain objects (breaks pagination in some cases). – Carolynncarolynne

You cannot added params like max or offset with listDistinct (The listDistinct() method does not work well with the pagination options maxResult and firstResult. If you need distinct results with pagination, we currently recommend that you use HQL), at least in grails 2.x or below – Winther

Answer 4

You could also use dynamic finders, as in Order.findAllBy* .Depending on how complicated your filter is, this could be easy or tough :)

Temperament

My filter is quite complicated and very dynamic too, I don't think findBy will work in this case. Filter applies to nested objects as well. That's one of the reason I decided to use criteria over native SQL – Northman