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.

Taiga

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%'])

Copalm

This query gives me empty list each time. – Taiga

Do you have the data to satisfy the requirements? query is case sensitive. I pretty much used your domains and build some sample data and tested it. I'll checkin the code to github tonight. – Copalm

I am created DomainA instance with name Manish and DomainB instance with email [email protected] and query gives me empty list. Waiting for the repository. Thanks – Taiga

Your query always works fine, it is my bad that I am trying with offset: 5 and not providing sufficient data in DB. Many thanks to your time and effort. – Taiga

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.

Baculiform

I am using grails 2.3.5, Your query gives me error, I have corrected this using grails.org/doc/2.3.7/guide/GORM.html#whereQueries documentation. Your query gives me incorrect result. It include DomainA instances whose most recent DomainB is ot gmail but have a gamil in old records. – Taiga