Search records bases on latest record in a hasmany relationship

Question

I have two domains

class DomainA {
    String name
    Date dateCreated
    Date lastUpdated

    static transients = ['email']

    static hasMany = [domainBs: DomainB]

    public String getEmail() {
        DomainB.mostRecentRecord(this).get()?.email
    }
}

and

class DomainB {
    String email
    Date dateCreated
    Date lastUpdated

    static belongsTo = [domainA: DomainA]

    static namedQueries = {
        mostRecentRecord { domainA ->
            eq 'domainA', domainA
            order('dateCreated', 'desc')
            maxResults(1)
        }
    }
}

My requirement is to get list of all DomainA whose name starts with "M" and latest domainBs record contains gmail in their email property.

I tried createCriteria and hql but did not get desired result, may be I am doing something wrong.

Following is my current code

List<DomainA> listA = DomainA.findAllByNameIlike("M%")
List<DomainB> listB = []
listA.each { entity ->
    DomainB domainB = DomainB.mostRecentRecord(entity).get()
    if (domainB && (domainB.email.contains('gmail'))) {
        listB.add(domainB)
    }
}

but it does not allows pagination and sort.

Can someone have any idea to get list of all DomainA whose name starts with "M" and latest domainBs contains gmail in their email property using createCriteria or hql or any other way.

Answer 1

Since you are looking for most recent email you need to look at the max dateCreated on your secondDomain. You can write it using HQL and using executeQuery to pass your pagination params. Just make sure to have an index on your dateCreated.

FirstDomain.executeQuery("select fd from FirstDomain fd,SecondDomain sd where \
      sd.firstDomain.id=fd.id and fd.name like :name and sd.email like :email \ 
      and sd.dateCreated = (select max(sd2.dateCreated) from SecondDomain sd2 \ 
            where sd2.firstDomain.id = fd.id))",
[email:'%gmail%',name:'M%'], [max: 10, offset: 0]) 

sample code : just hit the firstDomainController

        def fd
        // most recent email is gmail
        fd = new FirstDomain(name:"Mtest")
        fd.addToSecondDomain(new SecondDomain(email:'yahoo.com'))
        fd.addToSecondDomain(new SecondDomain(email:'gmail.com'))
        fd.save(flush:true)

        // most recent is yahoo
        fd = new FirstDomain(name:"MMtest")
        fd.addToSecondDomain(new SecondDomain(email:'gmail.com'))
        fd.addToSecondDomain(new SecondDomain(email:'yahoo.com'))
        fd.save(flush:true)

        // will return "Mtest"
        FirstDomain.executeQuery("select fd from FirstDomain fd,SecondDomain sd where sd.firstDomain.id=fd.id and fd.name like :name and sd.email like :email and sd.dateCreated = (select max(sd2.dateCreated) from SecondDomain sd2 where sd2.firstDomain.id = fd.id))",[email:'%gmail%',name:'M%'])

Answer 2

In Grails 2.4, you should be able to do something like this using the new correlated subquery functionality:

DomainA.where {
    name like 'M%' && exists DomainB.where { 
        id == max(id).of { email like '%gmail%' }
    }
}

Unfortunately, I haven't been able to test our application in 2.4 so I can't confirm if this will work.