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.

Alidad · Accepted Answer · 2014-05-10T08:47:52.513

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

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. — Alidad, May 09 '14 at 19:48
I am created DomainA instance with name `Manish` and DomainB instance with email `test@gmail.com` and query gives me empty list. Waiting for the repository. Thanks — MKB, May 10 '14 at 05:44
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. — MKB, May 10 '14 at 11:20

score 1 · Answer 2 · answered May 09 '14 at 15:42

1

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.

answered May 09 '14 at 15:42

Aaron

546
3
8

I am using grails 2.3.5, Your query gives me error, I have corrected this using http://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. – MKB May 09 '14 at 17:45

Search records bases on latest record in a hasmany relationship

2 Answers2