View source | Discuss this page | Page history | Printable version   

How to create a Computed Column

Contents

Introduction

In MP13 the new concept: computed column, was introduced. A computed column is a column of a table which is computed using a sql expression and which does not exist explicitly in the database schema.

Computed columns have these characteristics:

Example Module

This howto is supported by an example module which shows example of the code shown and discussed in this howto.

The code of the example module can be downloaded from this mercurial repository: https://code.openbravo.com/erp/mods/org.openbravo.client.application.examples/

The example module is available through the Central Repository (See 'Client Application Examples'), for more information see the Examples Client Application project page.

Bulbgraph.png   The example module also contains implementations of other howtos.

Defining a computed column

Defining a computed column is quite easy. Goto the 'Tables and Columns' window to the table in which you want to add the computed column.

Then in the column child tab create a new column and set the sqllogic field.


Howto-computed-column-defining.png


The sqlLogic field must be a sql expression:

This is an example of the total line quantity column shown above:

(SELECT sum(ol.qtyOrdered) FROM c_orderline ol WHERE ol.c_order_id=c_order_id)

What you can see is that the from clause uses an aliased table, the c_order_id at the end is the non-aliased column of the main table.

Note:

Use case: show totals on a header

In the 'Defining a computed column' section an example sql expression was shown. The above example computes the sum of the quantities of a line. You can add a computed column as a field to a tab/window:


Howto-computed-column-using-field-definition.png


and display it in the grid:


Howto-computed-column-using-grid.png


or the form:


Howto-computed-column-using-form.png

Performance implications

Filtering and sorting

When computed columns are used to filter or sort the grid, their value need to be computed for all existing rows before any pagination limit can be applied, this can have a very important impact in terms of performance.

In general, when defining computed columns, they should be made not filterable nor sortable. The only exception to this rule would be when it is guaranteed the number of records in the table they are created for is going to be always reduced or it will always be displayed as a subtab where the number of rows per parent record cannot be big.

Lazy evaluation

Bulbgraph.png   Lazy evaluation of computed columns is introduced starting in 3.0MP27

Computed columns are lazily evaluated. This means their query is not executed when the entity they are defined in is retrieved but when one of them is accessed.

For example, Delivery Status is a computed column of Order entity:

   // load one order
   OBCriteria<Order> qOrder = OBDal.getInstance().createCriteria(Order.class);
   qOrder.setMaxResults(1);
   Order order = qOrder.list().get(0);

The code above loads an order, at this stage Delivery Status is not already computed meaning the SQL has not been executed in database.

If afterwards we have this code:

  // load computed columns
  System.out.println(order.getDeliveryStatus());

is at this point when the computed column query is executed in database and Delivery Status property takes value.

Note that in case there are many computed columns in the same entity, they are evaluated all together when the first one is calculated.

Limitations

In order to make computed columns lazy, they are mapped in their entity as a many-to-one property (named _computedColumns) linked to a virtual entity, is this virtual entity where the actual computed columns are located as hibernate formula properties.

ComputedColumns.png

This extra complexity is transparent when working with generated DAL Java classes, as seen before, order.getDeliveryStatus() populates the value of the computed column, though behind the scenes it is retrieving it from the virtual entity.

But this model imposes also some limitations to be taken into account when manipulating computed columns in filters or sorting.

Bulbgraph.png   Note that when using computed columns in HQL to do filtering or sorting, they are evaluated. This needs to be done carefully (or even avoided if possible) because it might have performance implications.

When working with HQL (i.e. OBQuery) computed columns cannot be directly accessed as they are not properties of the entity.

So this valid query in versions prior to MP27:

 
-- NOTE: this code is not valid after 3.0MP027
FROM ORDER o WHERE o.deliveryStatus = 100

doesn't work anymore starting from MP27, its equivalent would be:

 
FROM ORDER o WHERE o._computedColumns.deliveryStatus = 100
Warn-icon.png   Starting from 3.0MP27, computed columns cannot be used anymore in OBCriteria, if needed OBQuery should be used instead.

Retrieved from "http://wiki.openbravo.com/wiki/How_to_create_a_Computed_Column"

This page has been accessed 24,975 times. This page was last modified on 10 January 2019, at 10:06. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.