82033

Grails 3 - return list in query result from HQL query

Question:

I have a domain object:

class Business { String name List subUnits static hasMany = [ subUnits : SubUnit, ] }

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

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

when using:

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

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().

Answer1:

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 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

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 ")

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.

then when you have your results

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] ]

you can now do

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

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 ")

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.

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.

Recommend

  • Return flat object from sequelize with association
  • How to flatten up embedded JSON into multiple documents
  • openoffice: duplicating rows of a table in writer
  • Get data from 2D tensor with tensorflow js
  • Using Google Api: Speech To Text on PC Version
  • inserting duplicate records with SQL
  • Replace rows by index
  • Tracking screen recorder in windows app
  • PHP multiple file uploads
  • Regex for Specific Tag
  • How to turn (A, B, C) into (AB, AC, BC) with Pig?
  • Javascript/Jquery runs fast in desktop browsers, but slow in mobile/smartphone browsers…should I spl
  • How can we prepend rows to a react native list-view?
  • How can I tell a form not to dispose a particular control when it closes?
  • Hash Code in SQL Server?
  • Can't remove headers after they are sent
  • Salesforce Different WSDL files and when to use
  • Intel-64 and ia32 atomic operations acquire-release semantics and GCC 5+
  • Read text file that is not in the main package in a runnable jar
  • Diff between two dataframes in pandas
  • presentShareDialogWithParams posts to FB wall, but callback handler results say error
  • What's the purpose of QString?
  • How can I sort a a table with VBA with given text condition?
  • Jackson Parser: ignore deserializing for type mismatch
  • Time complexity of a program which involves multiple variables
  • Is it possible to access block's scope in method?
  • Checking free space on FTP server
  • Jquery - Jquery Wysiwyg return html as a string
  • Arrays break string types in Julia
  • WPF Applying a trigger on binding failure
  • JTable with a ScrollPane misbehaving
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • Turn off referential integrity in Derby? is it possible?
  • Add sale price programmatically to product variations
  • Does armcc optimizes non-volatile variables with -O0?
  • UserPrincipal.Current returns apppool on IIS
  • Unable to use reactive element in my shiny app
  • How do I use LINQ to get all the Items that have a particular SubItem?