mysql - Spring Data在OneToMany上创建内部联接

所以我正在使用spring数据，但在尝试为该休眠查询获取列表时遇到问题。

interface NewFooWithMetadataDAO : Repository<Foo, Int> {
    @Query("""SELECT NEW com.foo.persistence.sql.po.NewFooWithMetadataPO(
                    b.id,
                    b.accountId,
                    bi.profitCenterSegmentation,
                    b.fooContext
                )
                from
                    Foo b
                    left join b.fooIntent bi
                    left join b.fooContext bc
                where
                    account_id = ?1
                group by
                    b.id
    """)
    fun findByAccountId(accountId: Int): List<NewFooWithMetadataPO>
}

该查询将创建一个像这样的休眠查询。

select
    foo0_.id as col_0_0_,
    foo0_.account_id as col_1_0_,
    foo_buc1_.profit_center_segmentation as col_2_0_,
    . as col_3_0_
from
    foo foo0_
    left outer join foo_intent foo_buc1_ on foo0_.foo_intent_id=foo_buc1_.id
    left outer join foo_context_map foocont2_ on foo0_.id=foocont2_.foo_id
    inner join foo_context_map foocont3_ on foo0_.id=foocont3_.foo_id
where
    account_id=?
group by foo0_.id

这是一个不好的查询。

我还尝试了HQL中的bc而不是b.fooContext，但是后来我没有得到一个集合，而是得到了一个FooContextPO。

我的实体就像

@Entity
@Table(name = "foo")
data class Foo(
        @Id
        val id: Int,
        @Column(name= "account_id")
        val accountId: Int,

        @OneToOne
        @JoinColumn(name = "foo_intent_id", referencedColumnName = "id")
        @NotFound(action=NotFoundAction.IGNORE)
        val fooIntent: FooIntentPO,


        @OneToMany
        @JoinColumn(name = "foo_id", referencedColumnName = "id")
        @NotFound(action=NotFoundAction.IGNORE)
        val fooContext: Collection<FooContextPO>
){
        @Entity
        @Table(name = "foo_intent")
        data class FooIntentPO(
                @Id
                val id: Int,
                @Column(name = "profit_center_segmentation")
                val profitCenterSegmentation: String
        )
        @Entity
        @Table(name = "foo_context_map")
        data class FooContextPO(
                @Id
                val id: Int,
                @Column(name = "foo_id")
                val fooId: Int,
                @OneToOne
                @JoinColumn(name = "context_id", referencedColumnName = "id")
                val context: ContextPO
        )
        @Entity
        @Table(name = "context")
        data class ContextPO (
                @Id
                val id: Int,
                @ManyToOne
                @JoinColumn(name = "foo_id", referencedColumnName = "id")
                val foo: Foo,
                @Column(name = "context")
                val context: String
        )

}

data class NewFooWithMetadataPO(
        val id: Int,
        val accountId: Int?,
        val profitCenterSegmentation: String?,
        val context: Collection<Foo.FooContextPO>
)

有什么办法可以让左联接在这里正常工作而不产生内部联接？

最佳答案

由于在下面选择了映射属性b.fooContext，您肯定会获得内部联接

SELECT NEW com.foo.persistence.sql.po.NewFooWithMetadataPO(
                    b.id,
                    b.accountId,
                    bi.profitCenterSegmentation,
                    b.fooContext
                )...

您分别将外部连接作为FooContextPO作为bc，所以如果要避免内部连接，则不能引用映射的属性fooContext。因此，您需要选择bc。

由于实际的基础数据，您只能得到1条记录吗？选择bc而不是b.fooContext时生成的SQL看起来是否也不正确？如果是的话...您可以分享该SQL的样子吗？