Getting distinct results for one property with Grails criteria

Question

From a domain class instance in the database I want to get the last 20 updated records but distinct to a certain property, which in my case is color. I've tried 3 different approaches but none seem to work the way I expected.

First approach:

def corridors = TerminKorridor.findAll([max:20, sort:'lastUpdated', order:"desc"]).color.unique();

works but doesn't return 20 entries

Second approach:

 def corridors = corridorsCriteria.list{
            distinct("color")
            maxResults(20)
            order("lastUpdated", "desc")
         }

doesn't remove entries with same colors. Distinct doesn't work

Third approach:

def corridors = TerminKorridor.executeQuery("Select distinct a.color from TerminKorridor a order by a.lastUpdated desc",[max:20])

doesn't remove entries with same colors, distinct doesn't work.

Answer 1

So I think there is a problem with your logic somewhere here.

The goal is to get a list of 20 distinct colors ordered by lastUpdated.

You cant order by lastUpdated if you want a distinct list. It doesn't make sense. If you have 3 items with the same color, which lastUpdated would you use to do the ordering?

You have to choose if you are getting the last 20 records and then a distinct list of colors, which would almost certainly result in less than 20 records

OR

You get a list of 20 distinct colors

You could do this with some code though.

LinkedHashSet<Color> colors = new LinkedHashSet()

try {
    TerminKorridor.findAll([sort:'lastUpdated', order:"desc"]).each {
        if (colors.size() < 20) {
            colors.add(it.color)
        } else {
            throw new GetOutOfHereException()
        }
    }
} catch(GetOutOfHereException e) {
 null
}

You could make this better by using HQL to only load the colors instead of the entire objects

TerminKorridor.executeQuery("select t.color from TerminKorridor t order by t.lastUpdated desc").each { ... }