使用不同的别名多次加入关联

发布于 2024-12-08 12:43:33 字数 1007 浏览 0 评论 0原文

我认为,我遇到的问题是 Hibernate 中的一个错误(已跟踪 - https://hibernate .onjira.com/browse/HHH-879)。

我的问题是 - 是否有解决方法可以使用 grails 标准查询多次连接表?

SQL 很简单:

SELECT s FROM Store AS s   
    INNER JOIN s.Products AS prod1  
    INNER JOIN s.Products AS prod2  
WHERE 
    prod1.Type = 'Shoes'   
AND 
    prod2.Type = 'Shirts' 

当我在 grails 标准查询中使用“createAlias”(一个用于 prod1,一个用于 prod2)时,我收到以下错误:

org.hibernate.QueryException:重复的关联路径:studyTags ...

一种可能是使用 OR 进行查询(一个 JOIN 和 WHERE prod.Type = 'Shoes' OR 'Shirts'),然后过滤结果集。此解决方案的问题是,如果我指定条件查询的限制(最大结果),则实际结果(过滤后)的条目可能少于指定的条目。

任何帮助将不胜感激。

谢谢。

PS:我遇到这个问题的真实代码非常复杂。为了解决这个问题,我使用了 Store 和 Product 的这个例子......我认为查询看起来像

Store.withCriteria{
    createAlias('products', 'prod1')
    createAlias('products', 'prod2')

    and{
        eq('prod1.Type', 'Shoes')
        eq('prod2.Type', 'Shirts')
    }
}

I think, that the problem that i have is a bug in Hibernate (already tracked - https://hibernate.onjira.com/browse/HHH-879 ).

My question is - Is there a workaround to join a table multiple times with a grails criteria query?

The SQL would be straight forward:

SELECT s FROM Store AS s   
    INNER JOIN s.Products AS prod1  
    INNER JOIN s.Products AS prod2  
WHERE 
    prod1.Type = 'Shoes'   
AND 
    prod2.Type = 'Shirts' 

When i use "createAlias" in the grails criteria query (one for prod1 and one for prod2) i´am getting the following error:

org.hibernate.QueryException: duplicate association path: studyTags
...

One possibilty might by, to do the query with an OR (one single JOIN and WHERE prod.Type = 'Shoes' OR 'Shirts') and then filter the result set. The problem with this solution is, that if i specify a limit for the criteria query (max Results), the real result (after filtering) might have less entries than specified.

Any help would be appreciated.

Thanks.

PS: My real code, where i experienced this issue is pretty complex. To break the problem down i used this example with Store and Product... I think the query would look like

Store.withCriteria{
    createAlias('products', 'prod1')
    createAlias('products', 'prod2')

    and{
        eq('prod1.Type', 'Shoes')
        eq('prod2.Type', 'Shirts')
    }
}

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

冧九 2024-12-15 12:43:33

尽管这是一个老问题,但我在搜索解决方案时遇到了这个问题。所以我发现休眠中可能存在一个错误,导致无法使用重复别名。我使用的解决方法是使用纯 SQL 来查找有限的“id”列表,并检查其他查询要求是否与该列表匹配。在这个例子中,它可能是“

select x.id from XTable x join products prod1 on... join products prod1 on... where prod1.type= 'shoes' and prod.type = 'shirts';

然后检查

select x.id from XTable x where x.id in ("result of the query")

Although it is a old question I came acroos into it when searching my solution. So I figured out there is probably a bug in hibernate that make duplikacte aliases impossible. The work around I used is using pure SQL for finding a limited "id's" list and there check if other query requrements matched the list. In this example it could be"

select x.id from XTable x join products prod1 on... join products prod1 on... where prod1.type= 'shoes' and prod.type = 'shirts';

and then check

select x.id from XTable x where x.id in ("result of the query")
通知家属抬走 2024-12-15 12:43:33

您是否尝试过此操作 - 我认为这将返回包含“鞋子”和“衬衫”类型产品的所有商店

Store.withCriteria{
    createAlias('products', 'prod')

    and{
        eq('prod.Type', 'Shoes')
        eq('prod.Type', 'Shirts')
    }
}

Have you tried this - I think this will return all stores that contain both a product of type "Shoes" and "Shirts"

Store.withCriteria{
    createAlias('products', 'prod')

    and{
        eq('prod.Type', 'Shoes')
        eq('prod.Type', 'Shirts')
    }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文