Grails 2.5.0 - Fast lookup of items in collection over a domain

Question

I have the following code for sending internal messages over the web app:

Domain:

class Mail {
    ...
    User from
    static hasMany = [to: User]
    ...
}

What I want to do is enable a User who logs in and is addressed in the "to" field to view all the Mails addressed to them. It's easy to do this with the "from" field using:

def totalMails = Mail.countByFrom(user)
...
def mails = Mail.findAllByFrom(user, [max: rows, offset: offset])

Doing this with the "to" field is more difficult, and I'm not sure how to do it and make the lookups fast.

Answer 1

You can use criteria, where, or HQL queries. The HQL query will give you an idea of how the criteria and where queries work at the database level.

Criteria

def totalMails = Mail.createCriteria().get {
    to {
        eq('id', user.id)
    }

    projections {
        count('id')
    }
}

def mails = Mail.createCriteria().list(max: rows, offset: offset) {
    to {
        eq('id', user.id)
    }
}

Where

def totalMails = Mail.where {
    to.id == user.id
}.count()

def mails = Mail.where {
    to.id == user.id
}.list(max: rows, offset: offset)

HQL

def totalMails = Mail.executeQuery 'select count(m) from Mail as m inner join m.to as t where t.id = :id', [id: user.id]

def mails = Mail.executeQuery 'select m from Mail as m inner join m.to as t where t.id = :id', [id: user.id], [max: rows, offset: offset]