gpt4 book ai didi

grails - 默认情况下,标准使用 “inner join”而不是 “left join”方法使我的查询不按我计划的方式工作

转载 作者:行者123 更新时间:2023-12-03 11:34:06 25 4
gpt4 key购买 nike

问题是:在此特定示例中,如何使GORM生成左联接而不是内联接?

测试平台:

给定A,B和C类:

class A{
B someObject
}

class B{
C importantObject
}

class C{
boolean interestingFlag
}

我想列出A类的所有元素:
  • 他们的B.C对象为null或
  • 他们的B.C对象有趣的标志值是假的

  • 到目前为止我尝试过的是:

    此方法将生成A的正确列表,其中B.C为空(条件2被注释掉),或者生成A的正确列表,其中B.C.interestingFlag = false(无论条件1是否被注释掉)。当两个条件都取消注释时,它仅返回A.B.C.interestingFlag = false(A.B.C = null条件被忽略)的元素列表。
    // approach 1 (conditional 1 is ignored)
    def result = A.withCriteria{
    someObject{
    or{
    isNull('importantObject') // conditional 1, works well when conditional 2 is commented out
    importantObject{
    eq('interestingFlag', false) // conditional 2, works well alone, discards conditional 1 when both of them are uncommented
    }
    }
    }
    }

    编辑:
    根据评论中的要求,我粘贴了一个休眠生成的SQL:
    Hibernate: select this_.id as id1_2_, this_.version as version1_2_, 
    this_.some_object_id as some3_1_2_, someobject1_.id as id2_0_,
    someobject1_.version as version2_0_, someobject1_.important_object_id as
    important3_2_0_, importanto2_.id as id0_1_, importanto2_.version as version0_1_,
    importanto2_.interesting_flag as interest3_0_1_ from a this_
    inner join b someobject1_ on this_.some_object_id=someobject1_.id
    inner join c importanto2_ on someobject1_.important_object_id=importanto2_.id
    where ((someobject1_.important_object_id is null or (importanto2_.interesting_flag=?)))

    当我直接将其复制并粘贴到pgAdmin查询工具中时,做了一些更改(将内部联接更改为左联接,并提供了趣味的Flag =“false”参数),一切都按我的意愿工作(我得到了ABC = null和ABCimportantFlag =错误的对象)
    Hibernate: select this_.id as id1_2_, this_.version as version1_2_, 
    this_.some_object_id as some3_1_2_, someobject1_.id as id2_0_,
    someobject1_.version as version2_0_, someobject1_.important_object_id as
    important3_2_0_, importanto2_.id as id0_1_, importanto2_.version as version0_1_,
    importanto2_.interesting_flag as interest3_0_1_ from a this_
    left join b someobject1_ on this_.some_object_id=someobject1_.id
    left join c importanto2_ on someobject1_.important_object_id=importanto2_.id
    where ((someobject1_.important_object_id is null or (importanto2_.interesting_flag=false)))

    最佳答案

    经过测试的工作解决方案:

        def result = A.withCriteria{
    createAlias('someObject', 'so', CriteriaSpecification.LEFT_JOIN)
    createAlias('so.importantObject', 'imp', CriteriaSpecification.LEFT_JOIN)
    or {
    isNull('so.importantObject')
    eq('imp.interestingFlag', false)
    }

    }

    注释中建议的解决方案更新:
        def result = A.withCriteria{
    createAlias('someObject', 'so', JoinType.LEFT_OUTER_JOIN)
    createAlias('so.importantObject', 'imp', JoinType.LEFT_OUTER_JOIN)
    or {
    isNull('so.importantObject')
    eq('imp.interestingFlag', false)
    }

    }

    关于grails - 默认情况下,标准使用 “inner join”而不是 “left join”方法使我的查询不按我计划的方式工作,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17083204/

    25 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com