CODEWITHSUNDEEP
sqlgrailsgrails-orm
genonymous
genonymous

Reputation: 1750

Mapping SQL query to GORM query

Is my mapping below of the SQL query to the GORM query correct? I would appreciate any helpful comments. Also, why isn't there any option in createAlias function to specify the condition on which we want the LEFT JOIN to occur?

SQL query:

select tg.geck_id as geck_id, min(Fault.id) AS id, Trace.frame_number,
Module.module, Symbol.symbol, Report.email as email, Fault.version, 
Fault.short_os,Fault.fault_date as fault_date from Report, Trace, Module,  
Symbol, Fault LEFT JOIN TraceGroup16Map tgmap ON tgmap.fault_id = Fault.id 
LEFT JOIN TraceGroup16 tg ON tg.id = tgmap.group_id where Report.id = 
Fault.report_id AND Fault.id = Trace.fault_id AND Trace.symbol_id IN (select 
id from Symbol where symbol like '%mysymbol%') AND Trace.module_id IN 
(select id from Module where module like '%mymodule%') AND Trace.module_id = 
Module.id AND Trace.symbol_id = Symbol.id group by Fault.pid, Report.file 
ORDER BY Fault.fault_date DESC

GORM query:

def trace = Trace.createCriteria()
    def results = trace.list(max:max, offset:offset) {      
        createAlias('module','mod', CriteriaSpecification.LEFT_JOIN)
        createAlias('symbol','sym', CriteriaSpecification.LEFT_JOIN)
        createAlias('fault', 'fault',CriteriaSpecification.LEFT_JOIN)
        createAlias('fault.report', 'report', CriteriaSpecification.LEFT_JOIN)
        createAlias('fault.tgmap', 'tg', CriteriaSpecification.LEFT_JOIN)
        createAlias('tg.traceGroup16','tr', CriteriaSpecification.LEFT_JOIN)        
        projections
        {
            property('fault.id')
            property('tr.geckId')
            property('report.email')
            property('fault.ver')
            property('fault.shortOs')
            property('fault.faultDate')
            property('frameNumber')
            property('mod.module')
            property('sym.symbol')
            groupProperty 'fault.pid'
            groupProperty 'report.file'
        }
        // Handle Unknown module case
        if (module.length() > 0 && symbol.length() > 0 && module != symbol)
        {
            and
            {
                like('mod.module', '%' + mymodule + '%')
                like('sym.symbol', '%' + mysymbol + '%')
            }

        }
        order("fault.faultDate", "desc")
    }

Upvotes: 0

Views: 248

Answers (1)

rcgeorge23
rcgeorge23

Reputation: 3694

Easiest way to verify this is to turn on SQL logging for Hibernate and check the SQL that Hibernate spits out.

Reference

Or, if you're using mysql, the solution I prefer is to turn on query logging and tail the query log. The benefit of this is that you see query parameters in place within the query (rather than hunting for ?s)

Reference

Upvotes: 1

Related Questions