Count Records From A hasMany Relationship In Grails

Recently I was tasked with trying to determine a distinct count between two tables with a many-to-many and one-to-many relationship. More specifically I needed to find out how many users were signing into the system on any given day. This is not to be confused with how many logins a particular user had for any given day.

In the following example you’ll see two domain classes and a controller class. The User class represents all the users in my system. The username is unique and that is why I made it my primary key.

class User {

    String firstName;
    String lastName;
    String userName;

    static hasMany = [ logs: Log ]

    static mapping = {
        id generator: "assigned", name:"userName", sqlType:"varchar(255)", column:"userName"
        logs column: "userName"
    }

}

The Log class represents when a particular user has signed into the system. There can be infinite amounts of sign ins per user per day.

class Log {

    String roleName;
    Date accessDate

    static belongsTo = [ user: User ]

}

Again, I needed to figure out how many users signed into the server today using all the available GORM that Grails has to offer. In the controller, I created a new Date object and zeroed out the time because in my scenario it is not relevant. Then using withCriteria, I can create my own custom query without actually writing any SQL. The createAlias allows me to use the hasMany property that I defined in my domain class.

Doing a simple query was not good enough because that would produce an inaccurate count if I had multiple sign ins per day. I instead wanted a distinct count so regardless how many times someone signed in, it only registered once in this result because I don’t care how many times everyone signed in.

class IndexController {

    def index() {
        def today = new Date();
        today.clearTime();
        def userCount = User.withCriteria() {
            createAlias("logs", "l")
            eq("l.roleName", "Moderator")
            between("l.accessDate", today, today + 1)
            projections {
                countDistinct("userName")
            }
        }
        return [ loginCountToday: userCount[0] ]
    }

}

To test this code, add the following to your BootStrap.groovy file:

class BootStrap {

    def init = { servletContext ->
        def u1 = null;
        def u2 = null;
        if(!User.count()) {
            u1 = new User(userName: "nraboy", firstName: "Nic", lastName: "Raboy").save(failOnError: true)
            u2 = new User(userName: "dvezzani", firstName: "David", lastName: "Vezzani").save(failOnError: true)
            new Log(roleName: "Moderator", accessDate: new Date(), user: u1).save(failOnError: true)
            new Log(roleName: "Moderator", accessDate: new Date(), user: u1).save(failOnError: true)
            new Log(roleName: "Moderator", accessDate: new Date(), user: u2).save(failOnError: true)
        }
    }

}

If everything went smooth, it should return a count of two even though there were three access logs for the day.

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.