Grails sub query with createCriteria

Question

I have sql query like below

select transf, count(fname)  from peak_info where fname  in (select peakfile from pe_result where conid = 'GO:0006007' and fdr > 0.05) group by transf;

which I want to implement in grails create criteria. Currently, I am running the SQL query in bracket first and then run an outer query like below:

 def test2 = PeResult.createCriteria()
        def ptest=test2.list {
            eq("conid",conid.toString())
            gt("fdr","0.05")
            }

def peaknames = ptest.peakfile

def peakinfoFilter = PeakInfo.createCriteria()
def pifilter = peakinfoFilter.list { 
   'in'("fname", peaknames)  
    projections
        { 
           groupProperty "transF" 
            count "fname" 
        }  
}

I was wondering if there are other ways doing this into one query instead of running two queries?

score 0 · Answer 1 · answered Jan 19 '19 at 05:28

You probably can do something like this. Haven’t executed it but you got the idea. Have a look at subquery section of GORM.

def peakinfoFilter = PeakInfo.createCriteria()
def pifilter = peakinfoFilter.list {
   'in' "fname", PeResult.where{
                  conid == conid.toString()
                  fdr > "0.05"
            }. peakfile     
    projections
    {
       groupProperty "transF"
       count "fname"
    }
}

Grails sub query with createCriteria

1 Answers1