Grails GORM Set Transient Variables from SQL Query

Question

In Grails I would like a transient variable to be set based on a SQL query. I do have to use SQL, not HQL.

(This is a simplified example. Using Grails 3.0.11.)

Do a SQL query
As part of the SQL query calculate a value for a column
Have that value set in the Entity class added to the SQL Query
See ofLegalAge / of_legal_age below

Lets say I have the following Domain class

 class Person {
    String firstName
    String lastName
    Integer age

    static mapping = {table "person"} 
 }

Then I have a view on a Person Domain class as follows

class PersonView {
    Long id
    String firstName
    String lastName
    // ofLegalAge is dynamically set based on query
    Boolean ofLegalAge
    // Other things based on joins

    static transients = [ "ofLegalAge" ]

    static mapping = {table "person_view"} 
}

What I would like to do is

 String sql = """select id, 
                        first_name, 
                        last_name, 
                        -- This is the source for of_legal_age
                        case when age >= :ageForThisArea 
                             then true 
                             else false 
                             end as of_legal_age
                 from view_person
                 where age >= :ageForThisArea"""

  def session = sessionFactory.getCurrentSession()
  def query = session.createSQLQuery(sql)
  query.setParameter("ageForThisArea", age)
  query.addEntity(PersonView)
  // This should result in a List of PersonView with ofLegalAge set correctly
  List<PersonView> theList = query.list()

Basically at the end I need ofLegalAge to be set based on the query, however at the end (after query.list() is called) it is not being set in PersonView.

@Vahid Thank you but (a) the PersonView class is existing and used for many other things, and (b) as I mentioned, I need SQL for other parts of the query separate from this question — Ask613, Mar 26 '19 at 15:22
Wonder if you changed it to string if it would work ? you could try CAST(1 AS BIT) ELSE CAST(0 AS BIT) END as per https://stackoverflow.com/questions/10377781/return-boolean-value-on-sql-select-statement — V H, Mar 26 '19 at 17:07

score 0 · Answer 1 · answered Mar 27 '19 at 00:45

0

Transient means that it is not stored in the database anywhere and is calculated on the fly in the domain model. So why not just do something like this in code?

class PersonView {
    Long id
    String firstName
    String lastName

    // ofLegalAge is dynamically set based on query
    Boolean getOfLegalAge() { 
       //match the relevant record with Person here... your query doesn't do this?
       def matchedPerson = Person.findWhere(firstName: this.firstName, lastName: this.lastName)
       if (matchedPerson.age >= System.properties['ageForThisArea']) {
          return true
       } else { 
          return false
       }
     }

    static transients = ['ofLegalAge']

    static mapping = {table "person_view"} 
}

this way it will always be looked up on the fly. If you want to pre-populate the view with data you should look to no longer make it transient and store it somewhere instead.

answered Mar 27 '19 at 00:45

dawogfather

604
5
10

I understand what transient is. Note that my question is a (much) simplified version of the actual problem in order to get a solution. I cannot do what you suggest because the real problem is not about age. The actual problem is how to data-bind a query result into a Grails object – Ask613 Mar 27 '19 at 12:21
Sorry wasn't meaning to be rude. I don't think i fully understood the question. Still not sure I do. Could you not use something like the above but in the getter use sql to do your lookups? Like shown in https://stackoverflow.com/a/19165211/708998 or even straight sql if your datasource is different entirely as shown in http://mrhaki.blogspot.com/2014/03/grails-goodness-using-groovy-sql.html?m=1. If you're wanting to save and use that field then perhaps make it a field & set it in the beforeinsert or onLoad event http://gorm.grails.org/latest/hibernate/manual/index.html#eventsAutoTimestamping – dawogfather Mar 27 '19 at 12:57
My apologies too. I looked at your suggestions but could not find anything that worked – Ask613 Mar 27 '19 at 18:45

score 0 · Answer 2 · answered Mar 27 '19 at 18:53

This is a solution I found that worked, but I definitely consider it sub-optimal and would still be interested in a better solution. Basically I get the results as a Map instead of using the Grails object, and then manually convert the Map into the Grails object.

I changed the query to

String sql = """select id, 
                       first_name, 
                       last_name, 
                       -- This is the source for of_legal_age
                       case when age >= :ageForThisArea 
                         then true 
                         else false 
                         end as of_legal_age
                from view_person
                where age >= :ageForThisArea"""

 def session = sessionFactory.getCurrentSession()
 def query = session.createSQLQuery(sql)
 query.setParameter("ageForThisArea", age)

 // Setting the Entity does not work
 // query.addEntity(PersonView)

 query.setResultTransformer(AliasToEntityLinkedMapResultTransformer.INSTANCE);
 List<Map> intermediateList = query.list()
 List<PersonView> theList = []

 for (Map oneRow : intermediateList) {
     PersonView pv = new PersonView(
       id : oneRow.id,
       firstName: oneRow.first_name,
       lastName: oneRow.last_name,
       ofLegalAge: oneRow.of_legal_age,
     )
     theList.add(pv)
 }

 return theList

Grails GORM Set Transient Variables from SQL Query

2 Answers2