Calculated column in grid

[tudor-malene]

Calculated column in grid

[johnsylora]

Hi Tudor, I was able to read the derived properties guide information. I created the averageScore derived property as follows:

class DomainName {

    ScoreEnum attribute1
    ScoreEnum attribute2
    Integer averageScore
    }

    static mapping = {
        averageScore formula: '(attribute1 + attribute2 )/2'
    }
    static constraints = {
        attribute1(nullable:true, blank:true)
        attribute2(nullable:true, blank:true)
    }
}

My ScoreEnum is as follow:

 enum ScoreEnum {
    GREAT(5), GOOD(4), OK(3), FAIR(2), POOR(1), NA(-1)

    final Integer value

    ScoreEnum(Integer value){this.value = value}

    //shows value in select drop down
    String toString() {value}

    //stores value in database
    String getId() {value}

    //returns Enum constant associated with value
    String getKey(){name()}
 }

I'm using Postgresql to store the data. I received an error from Postgres as follows:

Caused by PSQLException: ERROR: operator does not exist: character varying + character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 273

I looked in my Postgresql database using pgAdmin and I see all the attributes of type ScoreEnum are stored as 'character varying(255)'. Should I change the ScoreEnum class definition to one that causes postgresql to store ScoreEnum attributes as type 'integer' instead of 'character varying(255)'? If they were integers then I can do arithmetic calculations on them more easily. Thank you for your input on this.

[tudor-malene]

I haven't tried this, but you could try:

1. Changing the signature of getId to return int:
   int getId() {value}

2. use the 'to_number' postgres function in the formula

[johnsylora]

Hi Tudor, I tried your 1st suggestion, and it worked great!! I also tried type Integer, and it worked. The attributes which are of type ScoreEnum were stored as integers after the change.

In ScoreEnum I used:

Integer getId() {value}

This allowed my formula to work in my domain class as follows:

class DomainName{

    ScoreEnum attribute1
    ScoreEnum attribute2
    Float averageScore

    static mapping = {
        averageScore formula: '(attribute1 +attribute2)/2.0'
    }
  static constraints = {
        attribute1(nullable:true, blank:true)
        attribute2(nullable:true, blank:true)
    }
}

When I divided by 2, I only received an integer result with no decimal fraction. When I used 2.0 to divide, Postgresql showed the decimal fraction storing the averageScore as a numeric type. This worked very well.

However, there is an issue I have with calculating the averageScore. In my domain class, I allow nulls and in my ScoreEnum definition I allow -1 representing NA. When I calculate the average, I need to ignore attributes with value null or -1. I need to count the number of attributes with values greater than 0 and use this as a divisor when I calculate the average. What would be the best way to achieve this? Do you think I should create a postgresql function that does this and call it in the formula?

I have created a Grails service that does this called calculateAverageScore. I could use this service as follows:

Float averageScore = domainNameService.calculateAverageScore(this)

But now the averageScore would be stored as a standard attribute not a derived attribute.
Thanks for your input.

[tudor-malene]

Is this average score used just for display? or do you want to be able to filter the data based on it?

If it is just for display, then you can call that calculateAverageScore function in the value closure of that column. Basically this would display the average in the grid. In this case, of course, filtering based on the average won't work

If you want to be able to filter based on it, then you have 2 options:

1. the derived property
2. store the actual average as a persistent field

Each option has advantages and disadvantages.
I think, based on the fact that there is some business logic in the formula -, I would choose option 2.

[johnsylora]

Hi Tudor, your analysis sounds very good. If I took the "just for display" route, would I create an averageScore attribute and mark it as transient to make sure it is not persisted in the database at all as follows?

class DomainName{

    ScoreEnum attribute1
    ScoreEnum attribute2
    Float averageScore

    static transients = ['averageScore']

   // static mapping = {
   //     averageScore formula: '(attribute1 +attribute2)/2.0'
   // }

  static constraints = {
        attribute1(nullable:true, blank:true)
        attribute2(nullable:true, blank:true)
    }
}

Then in the controller definition use:

 def domainNameGrid = {
        dataSourceType 'gorm'
        domainClass DomainName
        gridImpl 'dataTables'
        fixedColumns false

        //sets which columns to display
        //column filters turned off because filters are done with filterForm fields
        columns {
            'attribute1.name{
                enableFilter false
            }
            'attribute2.name'{
                enableFilter false
            }

            averageScore{
                enableFilter false
                value {
                   domainNameService.calculateAverageScore(this)
                }
            }
        }
    }

If a field is displayed in the columns section, does it have to be declared in the domain as an attribute, either standard, derived, or transient? Thanks for clarifying the use of the columns section.

[tudor-malene]

A column is just that.
You can display whatever you want in there. So, you don't need to declare it in the domain object at all.

[tudor-malene]

Closing solved issues