sql - 将SQL查询映射到GORM查询

Question

我下面的SQL查询到GORM查询的映射正确吗？我将不胜感激任何意见。另外，为什么在createAlias函数中没有任何选项来指定我们希望LEFT JOIN发生的条件？

SQL查询:

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查询:

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")
    }

Answer 1

验证此问题最简单的方法是打开Hibernate的SQL日志记录并检查Hibernate吐出的SQL。

Reference

或者，如果您使用的是mysql，我更喜欢的解决方案是打开查询日志记录并尾随查询日志。这样做的好处是您可以在查询中看到查询参数(而不是寻找？s)

Reference