Grails 3-HQL查询的查询结果中的返回列表 [英] Grails 3 - return list in query result from HQL query

问题描述

我有一个域对象:

class Business {
    String name
    List subUnits

    static hasMany = [
            subUnits : SubUnit,
    ]
}

我想使用HQL获取名称和子单元，但出现错误

I want to get name and subUnits using HQL, but I get an error

Exception: org.springframework.orm.hibernate4.HibernateQueryException: not an entity

使用时:

List businesses = Business.executeQuery("select business.name, business.subUnits from Business as business")

是否可以使用HQL获取结果查询结果中返回的子单元作为列表的方法?当我使用左联接时，查询结果是一个与名称重复的扁平列表.实际的查询更为复杂-这是一个简化的版本，所以我不能只使用Business.list().

Is there a way I can get subUnits returned in the result query result as a List using HQL? When I use a left join, the query result is a flattened List that duplicates name. The actual query is more complicated - this is a simplified version, so I can't just use Business.list().

推荐答案

我认为我应该将其添加为答案，因为一段时间以来我一直在做这种事情，并且可以与他人分享很多知识:

I thought I should add it as an answer, since I been doing this sort of thing for a while and a lot of knowledge that I can share with others:

根据上述雅里亚什的建议:

As per suggestion from Yariash above:

这是向前浏览域对象，而不是将信息作为平面列表(地图)获取.当拥有一个完整的对象然后要求它遍历并返回许多关系时，与将其全部包含在一个包含的列表中相比，这会涉及费用

This is forward walking through a domain object vs grabbing info as a flat list (map). There is expense involved when having an entire object then asking it to loop through and return many relations vs having it all in one contained list

@ anonymous1在左联接中听起来很正确-您可以查看添加到查询末尾的按名称分组".另外，当您获得所有结果时，可以使用business.groupBy {it.name}(这是一个很酷的功能)，看看groupBy的输出以了解它对

@anonymous1 that sounds correct with left join - you can take a look at 'group by name' added to end of your query. Alternatively when you have all the results you can use businesses.groupBy{it.name} (this is a cool groovy feature} take a look at the output of the groupBy to understand what it has done to the

但是，如果您试图抓住整个对象并将其映射回去，那么实际上成本仍然很高，并且可能与Yariash的建议一样昂贵，甚至可能更糟.

But If you are attempting to grab the entire object and map it back then actually the cost is still very hefty and is probably as costly as the suggestion by Yariash and possibly worse.

List businesses = Business.executeQuery("select new map(business.name as name, su.field1 as field1, su.field2 as field2) from Business b left join b.subUnits su ")

以上实际上是您应该尝试做的事情，先左移然后抓住hasMany的每个内部元素，作为该列表中返回的所有地图的一部分.

The above is really what you should be trying to do, left joining then grabbing each of the inner elements of the hasMany as part of your over all map you are returning within that list.

然后当您获得结果

def groupedBusinesses = businesses.groupBy {it.name}，其中name是来自具有hasMany关系的主类的主要对象.

def groupedBusinesses=businesses.groupBy{it.name} where name was the main object from the main class that has the hasMany relation.

如果再看一下，您会看到每个名称都有其自己的列表

If you then look at you will see each name has its own list

groupedBusinesses: [name1: [ [field1,field2,field3], [field1,field2,field3] ]

您现在可以做

groupedBusinesses.get(name) to get entire list for that hasMany relation.

为上述hql查询启用SQL日志记录，然后将其与

Enable SQL logging for above hql query then compare it to

List businesses = Business.executeQuery("select new map(b.name as name, su as subUnits) from Business b left join b.subUnits su ")

您将看到，第二个查询将生成巨大的SQL查询来获取数据，因为它尝试按行映射整个条目.

What you will see is that the 2nd query will generate huge SQL queries to get the data since it attempts to map entire entry per row.

我已经验证了这一理论，与从第一个示例创建的几行查询相比，如果不是从HQL内部创建的SQL查询的多个页面，它总是倾向于围绕整个查询页面.

I have tested this theory and it always tends to be around an entire page full of query if not maybe multiple pages of SQL query created from within HQL compared to a few lines of query created by first example.

这篇关于Grails 3-HQL查询的查询结果中的返回列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！