Geek Answers Handbook

Search for database records on the last record in a hasmany relationship

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 a list of all domains whose name starts with "M" and the last domainBs entry contains gmail in the email property.

I tried createCriteria and hql but didn't get the desired result, maybe I'm doing something wrong.

Below 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 this does not allow pagination and sorting.

Maybe someone has an idea to get a list of all domains whose name starts with "M" and the last domainBs contain gmail in the email property using createCriteria or hql or in any other way.

+6
source share
2 answers

Since you are looking for the latest email, you need to look at the maximum date created on your second domain. You can write it using HQL and use executeQuery to pass your pagination parameters. Just make sure the pointer is 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 click 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%'])
+4
source

In Grails 2.4, you can do something like this using the new correlated subquery function:

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

Unfortunately, I was not able to test our application in 2.4, so I can’t confirm if this will work.

+1
source

More articles:


All Articles