Defining Computed Fields

Computed fields are not editable fields, as they have dynamic values computed by the database during fetching. They are annotated in the JDL file using the @Formula annotation.

The underlying implementation of JDL computed fields uses the Hibernate Formula. The expressions must comply with the Hibernate Formula syntax and be supported by Postgres; please refer to https://docs.jboss.org/hibernate/orm/current/javadocs/org/hibernate/annotations/Formula.html.

The @Formula annotation takes as a parameter an expression to compute the field value. The expression can be a simple expression or a complex SQL query.

The following example shows a simple expression:

entity Employee {
     // DOM [primary.keys] : [id]
     id String required,

     firstName String,
     lastName String,

     @Description("Example of computed field with firstname and lastname")
     @Formula("concat(first_name, ' ', last_name)")
     fullName String
}

When the expression is a complex SQL query, one must ensure that the query filters out data using the current Scenario ID, which is always present in column db_gene_internal_scenario_id. Relations are implemented using a single column foreign key to the db_gene_internal_id column of the target table.

In the following example, the computed field averageAge of entity Company represents the average value of the field age of entity Employee.

Note that the Hibernate Formula SQL expressions use snake-case table and column names (https://en.wikipedia.org/wiki/Snake_case). If you are uncertain of the column and table names, check the generated JPA entities under the gene-model/gene-model-jpa/build/generated module of your project.

entity Company {
     // DOM [primary.keys] : [id]
     id String required,

     @Description("Employees average age")
     @Formula("""
     (SELECT AVG(e.age)
       FROM employee e 
       WHERE e.company = db_gene_internal_id
         AND e.db_gene_internal_scenario_id = db_gene_internal_scenario_id)
     """)
     averageAge Double
}

entity Employee {
     // DOM [primary.keys] : [id]
     id String required,

     firstName String,
     lastName String,
     age Integer
}

relationship ManyToOne {
  // DOM [affects.primary.key] : [true]
  Employee{company} to Company{employees}
}

The e.company = db_gene_internal_id clause implements the join between tables company and employee, in which db_gene_internal_id represents the primary key identifier of the current table company and e.company represents the foreign key column in the table employee referencing the company table primary key.

The e.db_gene_internal_scenario_id = db_gene_internal_scenario_id clause restricts data from the joined table employee to the current scenario.