无法获取查询执行一对多关联的快速加载的位置

Question

我使用Grails v3.3.9。

我无法让查询急切地加载一对多关联。尝试了各种方法。

这是相对于尝试在单元测试中使用它（在运行时应用程序也失败）发布的。

场景：我有两个域类，一个叫做“OrgRoleInstance”，它有集合

这是从组织到站点的双向一对多。

我使用新的DomainUnitTest特性创建了一个新的单元测试。在测试设置中，我创建了三个组织，每个组织添加一个站点，然后我将最后一个站点添加到第三个组织（组织“C”）。安装工作正常，所有实例都保持不变。

在where查询测试中，我寻找一个有2个站点的组织（即组织“C”）。

当我在debug中运行测试时，where查询返回一个大小为1的数组，其中包含org“C”，因此where子句中的测试可以看到sites集合不是空的——到目前为止还不错。

我也做了一个直接的网站。获取（4）以获取最后一个站点。我做了一个断言检查来检查站点是否正确。org ref是从where查询返回的同一个实例。这是真的，过去了。

但是，当您查看orgs[0]条目时，sites集合为空。简单的println无法访问查询返回的null sites属性。

class OrgRoleInstanceSpec extends Specification implements DomainUnitTest<OrgRoleInstance> {


    def setup() {
        OrgRoleInstance

        List<OrgRoleInstance> orgs = []
        ["A","B","C"].each {
            OrgRoleInstance org = new OrgRoleInstance(name:it, role:OrgRoleInstance.OrgRoleType.Customer)
            org.addToSites(new Site( name: "$it's Head Office", status:"open", org:org))
            orgs << org

        }
        orgs[2].addToSites (new Site( name: "${orgs[2].name}'s Branch Office", status:"open", org:orgs[2]))
        OrgRoleInstance.saveAll(orgs)
        assert OrgRoleInstance.count() == 3
        println "# of sites : " + Site.count()
        assert Site.count() == 4
        assert Site.get(2).org.id == orgs[1].id
    }


    void "where query and individual get " () {
        given :

        def orgs = OrgRoleInstance.where {
            sites.size() == 2
        }.list(fetch:[sites:"eager"])

             def org = OrgRoleInstance.get(2)
            List orgSites = org.sites

            def branch = Site.get(4)

            assert branch.org.is (orgs[0]) //assert is true

            println orgs[0].sites[1].name  //orgs[0].sites is null !


        expect:
        orgs.size() == 1

    }

}

我用标准尝试过这个，用基本的findAll（获取：[站点："渴望"）等。

然而，我尝试了这一点，我无法让查询返回网站的集合。

我想在查询中这样做，而不是通过OrgeRoleInstance域类中的mapping子句

我启用SQL日志记录并启动grails控制台。然后我键入以下脚本（使用我的引导数据）。

手动键入的控制台脚本

import com.softwood.domain.*

def orgs = OrgRoleInstance.where {
 id == 4
 sites{}
 }.list() /* (fetch:[sites:"eager"]) */

println orgs[0].sites

当运行时，输出以下内容——基本上看起来确实运行了热切的选择——并且我使用内连接获得了填充条目的orgs.sites结果：

groovy> import com.softwood.domain.* 
groovy> def orgs = OrgRoleInstance.where { 
groovy>  id == 4 
groovy>  sites{} 
groovy>  }.list() /* (fetch:[sites:"eager"]) */ 
groovy> println orgs[0].sites 

2019-01-23 14:02:00.923 DEBUG --- [      Thread-18] org.hibernate.SQL                        : 
    select
        this_.id as id1_16_1_,
        this_.version as version2_16_1_,
        this_.role as role3_16_1_,
        this_.name as name4_16_1_,
        sites_alia1_.id as id1_21_0_,
        sites_alia1_.version as version2_21_0_,
        sites_alia1_.org_id as org_id3_21_0_,
        sites_alia1_.name as name4_21_0_,
        sites_alia1_.status as status5_21_0_ 
    from
        org_role_instance this_ 
    inner join
        site sites_alia1_ 
            on this_.id=sites_alia1_.org_id 
    where
        this_.id=?
2019-01-23 14:02:00.923 TRACE --- [      Thread-18] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [4]
2019-01-23 14:02:00.923 DEBUG --- [      Thread-18] org.hibernate.SQL                        : 
    select
        sites0_.org_id as org_id3_21_0_,
        sites0_.id as id1_21_0_,
        sites0_.id as id1_21_1_,
        sites0_.version as version2_21_1_,
        sites0_.org_id as org_id3_21_1_,
        sites0_.name as name4_21_1_,
        sites0_.status as status5_21_1_ 
    from
        site sites0_ 
    where
        sites0_.org_id=?
2019-01-23 14:02:00.923 TRACE --- [      Thread-18] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [4]
[Site:(name : 1 Barkley Square) belonging to org: com.softwood.domain.OrgRoleInstance : 4, Site:(name : 10 South Close) belonging to org: com.softwood.domain.OrgRoleInstance : 4]

那么为什么这在单元测试中行不通呢？

我回到grails控制台，设法让这个标准查询工作。第1点-您必须导入org.hibernate.FetchMode，然后在Criteria闭包中的根级别的pechMode函数现在就可以工作了。最后，只需对集合执行空闭包，以强制执行急切的查询。

import com.softwood.domain.*
import org.hibernate.FetchMode

def orgs = OrgRoleInstance.withCriteria {
            fetchMode ("sites", FetchMode.SELECT)


            sites{}
        }

println orgs[0].sites

但是，这在单元测试中不起作用。单元测试中的相同查询如下所示

void "criteria query " () {
    given:

    OrgRoleInstance org

    org = OrgRoleInstance.withCriteria (uniqueResult: true) {
        fetchMode ("sites", FetchMode.SELECT)

        sites{}
    }

    expect:
    org.id == 3
    org.sites.size() == 2

}

失败与此错误

groovy.lang.MissingMethodException: No signature of method: grails.gorm.CriteriaBuilder.fetchMode() is applicable for argument types: (java.lang.String, org.hibernate.FetchMode) values: [sites, SELECT]

因此，我怀疑单元测试标准查询或使用新grailsDomainUnitTest的查询

您可能被迫将测试与真实数据库集成，以跨表测试查询。如果有人可以明确指出，新的单元测试特性不适用于可能对我有帮助的join/eager查询。




Answer 1

此调查的结果是，您不能对试图使用新的单元测试特性连接表的域模型查询使用单元测试。

如果你想查询测试，你必须把它作为集成测试来做。

当我这次将查询重新编码为集成测试时，允许在spock setup（）方法中的任何数据之前加载引导数据，然后查询开始工作，包括对数据的急切查询。

需要记住的一件事是，set（）方法不会进行回滚（这样数据就会在整个测试过程中保持不变），所以如果数据库没有被删除，您应该在最后进行手动清除。

因此，这不是编码为集成测试，似乎是可行的！

package com.softwood.domain

import grails.testing.mixin.integration.Integration
import grails.transaction.*
import org.hibernate.FetchMode
import org.hibernate.LazyInitializationException
import spock.lang.Shared
import spock.lang.Specification

@Integration
@Rollback
class OrgRoleInstanceIntegSpecSpec extends Specification {

    //operates on separate transaction thats not rolled back
    @Shared
    List<OrgRoleInstance> orgs = []

    @Shared
    List<OrgRoleInstance> sites = []

    @Shared
    NetworkDomain netDomain

    @Shared
    def bootstrapPreExistingOrgsCount, bootstrapPreExistingSitesCount

    //runs in transaction thats not rolled back for each test
    def setup() {
        def site

        bootstrapPreExistingOrgsCount = OrgRoleInstance.count()
        bootstrapPreExistingSitesCount = Site.count()
        //println "pre exist orgs:$boostrapPreExistingOrgsCount + pre exist sites: $boostrapPreExistingSitesCount"
        assert bootstrapPreExistingOrgsCount == 4
        assert bootstrapPreExistingSitesCount == 2

        ["A","B","C"].each {
            OrgRoleInstance org = new OrgRoleInstance(name:"test$it", role:OrgRoleInstance.OrgRoleType.Customer)
            org.addToSites(site = new Site( name: "test$it's Head Office", status:"open", org:org))
            orgs << org
            sites << site

        }
        orgs[2].addToSites (site = new Site( name: "${orgs[2].name}'s Branch Office", status:"open", org:orgs[2]))
        orgs[2].addToDomains(netDomain = new NetworkDomain (name:"corporate WAN", customer:[orgs[2]]))
        sites << site

        OrgRoleInstance.saveAll(orgs)
        assert orgs.size() == 3
        assert sites.size() ==4
        assert OrgRoleInstance.count() == 3 + bootstrapPreExistingOrgsCount
        assert Site.count() == 4 + bootstrapPreExistingSitesCount
        assert Site.get(4).org.id == orgs[1].id
        println "setup integration test data"

    }

    //manual cleanup of integration test data
    def cleanup() {

        orgs.each {OrgRoleInstance org ->
            org.sites.each {it.delete()}
            org.delete(flush:true)
            assert OrgRoleInstance.exists(org.id) == false
        }
        println "deleted integration test data"
    }

    void "Orgs list with eager fetch query"() {
        given :

        def orgs = OrgRoleInstance.list(fetch:[sites:"eager"])
        println "org ${orgs[6].name} sites : " + orgs[5].sites

        println "test site #2  has org as : " + (Site.list())[3].org

        expect :
        Site.count() == 4 + bootstrapPreExistingSitesCount
        orgs.size() == 3 + bootstrapPreExistingOrgsCount
        orgs[5].getName() == "testB"
        orgs[5].sites.size() == 1

    }

    void "orgs where query triggering eager site get"() {
        given :

        //where clause returns instance of DetachedCriteria, so have to trigger with a list/get etc
        def orgs = OrgRoleInstance.where {
            name =~ "%testB%" &&
                    sites{}
        }.list()

        expect :
        orgs.size() == 1
        orgs[0].name == "testB"
        orgs[0].sites.size() == 1

    }

    void "withCriteria query with eager site fetch (two selects)  " () {
        given:

        OrgRoleInstance org

        //with criteria runs the query for you unlike createCriteria() which returns  a detachedCriteria
        org = OrgRoleInstance.withCriteria (uniqueResult: true) {
            fetchMode ("sites", FetchMode.SELECT)
            idEq(7L)  //internally wont cast Integer to long, so set it explicitly
            sites{}
        }

        /*def orgs = OrgRoleInstance.withCriteria {
            //setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            eq 'name', "B"
            //fetchMode 'sites', FetchMode.SELECT
            sites{}
        }*/

        expect:
        org.id == 7
        org.sites.size() == 2

    }

    void "detached criteria (with distinct) with eager fetch " () {
        given:


        def orgs = OrgRoleInstance.createCriteria().listDistinct {
            //fetchMode 'sites', FetchMode.SELECT
            join 'sites'
            sites {
                org {
                    eq 'id', 6L
                }
            }
        }

        def site = orgs[0].sites[0]

        expect:
        orgs.size() == 1
        orgs[0].sites.size() == 1
        site.name == "testB's Head Office"

    }

    void "where query on id only without list (fetch eager) " () {
        given :

        def orgs = OrgRoleInstance.where {
            id == 7L
        }.list ()

        def branch = orgs[0].sites[0]

        when:

        println "branch "+ branch.name  //try and access branch

        then:
        /*
        -- seems to do an eager fetch on sites+domains, even though i didnt ask it to
         not quite sure why - separate exploration round that i think
         */
        //LazyInitializationException ex = thrown()
        orgs.size() == 1
        orgs[0].sites.size() == 2
        orgs[0].domains.size() == 1

    }

}

我希望这能让其他人免于因为你的测试不起作用而感到心痛。

还请注意，运行grails console将启动console脚本应用程序，但会引导gorm构建—因此，明智地使用include域包—您可以对gorm启动时加载的任何引导数据以交互方式尝试一些测试查询。

集成测试在运行时间上更加费劲和昂贵。

如果能够增强单元测试特性以支持查询测试，那将是一件好事（也是一件聪明的事情）。