Kyle Banks

Grails: Sort by hasMany Property Count

Written by @kylewbanks on Jun 18, 2013.

Something that I recently needed to add for the admin panel and home page of this site's redesign was to order posts by the number of views it has. I was actually quite surprised to find that Grails doesn't inject this functionality as part of it's standard CRUD functions on domain classes, such as list and findByXAndY.

The way my domain model works is very basic:

class Post {
    ...
    static hasMany = [views: View]
}

class View {
    ...
    static belongsTo = [post: Post]
}

The solution I came up with was to write a static method in the Post class that mimics the style of the injected CRUD methods. I want to statically invoke it and have it return a List of Post objects, sorted by the number of views it has. A simple HQL query should work just fine:

class Post {
    ...
    static hasMany = [views: View]

    static List<Post> orderByViewCount() {
        return Post.executeQuery("""
            SELECT post
            FROM Post post
            ORDER BY size(post.views) DESC
        """)
    }
}

This works great, but I decided to also overload it to allow additional parameters - namely max and sortOrder.

class Post {
    ...
    static hasMany = [views: View]

    static List<Post> orderByViewCount() {
        return this.orderByViewCount(Integer.MAX_VALUE, "DESC")
    }

    static List<Post> orderByViewCount(int max) {
        return this.orderByViewCount(max, "DESC")
    }

    static List<Post> orderByViewCount(String sortOrder) {
        return this.orderByViewCount(Integer.MAX_VALUE, sortOrder)
    }

    static List<Post> orderByViewCount(int max, String sortOrder) {
        return Post.executeQuery("""
        	SELECT post
        	FROM Post post
        	ORDER BY size(post.views) ${sortOrder}
    		""", [max: max])
    }
}

This gives the developer a little more flexibility when using this function by allowing them to limit the number of results, or change the sort order. Of course you could modify this to allow overloading of any parameters you want.

Let me know if this post was helpful on Twitter @kylewbanks or down below!