limit results returned by distinct query

Question

In my Grails app's domain model I have a 1-N relationship between Event and Performance (because many artists may perform at each event). I want to retrieve the 5 events that have most recently had a performance added.

I tried doing this with the following criteria query:

    List<Event> event = Event.createCriteria().listDistinct {
        performances {
            order("dateCreated", "desc")
            isNotNull("dateCreated")
        }
        maxResults(count)
    }

However it seems that you can't limit the result set of a distinct query using maxResults. Is there another way to achieve this, e.g. using HQL?

Answer 1

Should this suffice?

Event.executeQuery("select distinct e from Event as e \
                    inner join e.performances as p \
                    where p.dateCreated is not null \
                    order by p.dateCreated desc", [max: 5])

If you also need performances corresponding to each event in the same query, you can use inner join fetch.