Functions and Examples of Generated Columns

One of the new features we added in Backendless is called “Generated columns”. If you are familiar with Excel and have ever used Excel cell functions, you will understand Backendless Generated columns in no time. In short, it is a special column you can declare in a data table that has a “formula”, or our lingo, an expression.

The expression is responsible for generating a value for the columns. What can you do in an expression? A LOT! And that’s what makes Backendless Generated Columns super powerful! Read this article for an overview of the new feature.

In this post, we will go over some examples so you can see how awesome this functionality is and can start using it for your application. Let’s start with something very basic.

Basic Math

Suppose we have a table called Math. The table has two columns operand1 and operand2, both are of the INT type.  Here’s the schema for the columns:

Let’s say we want to be able to calculate the following values:

• the sum (operand1 + operand2)
• the difference (operand - operand2)
• the product (operand1 * operand2)
• the dividend (operand1 / operand2)
• operand1 raised to the power of operand2, which is basically (operand1)operand2

And not only we want to calculate these values, but also have dedicated columns that contain each of the results. Generated columns to the rescue!

Here’s what a declaration of the column containing the sum would look like:

As you can see all it takes is to select the Generated Column checkbox and then enter the Expression. Make sure to select the Type you expect the expression to produce. In our case, the sum of two integers will be an integer. The Expression field is the most important part. It is where you put the formula to calculate the column value. Once the column is created, let’s see it in action:

As you can see the generated columns have a special visualization in Backendless Console – there is a light yellow background, which is meant to indicate that this is a special column. Indeed, you cannot enter values into the cells of that column and you cannot update that column’s values through the API. However, as you can see the result is right there – the column displays the value according to the provided expression – it is the sum of the operand1 and the operand2 values.

Let’s add more columns for other math operations. The subtraction, multiplication, and division are going to be easy since Backendless supports all basic math operations. But how to go about calculating the power of operand1 to operand2? This is where expression functions come in. And we support a lot of them, including one to calculate the power (and Sine, Cosine, Tangent, Log, Square root, Random number generator to name a few). See below for the examples of what the expression should look like when you configure the generated columns the example we’re reviewing:

• difference – operand - operand2
•  product – operand1 * operand2
• dividend – operand1 / operand2
• power – POWER( operand1, operand2 )

This is what my data table looks like after I added the columns with the expressions shown above:

Of course, being able to define a column that calculates the value according to your own formula/expression is fantastic, but what makes this infinitely more useful is the API handles the columns just like any other ones. What this means is:

• The where clause to retrieve data objects can reference generated columns;
• A generated column and its value is returned by the server as any other column;
• A generate column expression can reference other generated column;

Below is a list of some common mathematical functions you can use in your expressions. Where it says value it can be:

1. a scalar value
2. name of a column in the same table
3. another function whose result will be used as the input

• ABS( value ) – returns absolute value. Example:
  Example: ABS( operand1 )

• MOD( value1, value2 ) – Modulo operation. Returns the remainder of value1 divided by value2.
  Example: MOD( operand1, operand2 )

• FLOOR( value ) -Returns the largest integer value not greater than value.
  Example: FLOOR( operand1 )

• RAND() -Returns a random floating-point value in the range 0 <= value < 1.0.To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). For example, to obtain a random integer in the range 7 <= R < 12, use the following expression:
  Example: FLOOR(7 + (RAND() * 5))

Working with String Values

Suppose there is a table with two columns of the STRING type. The column names are firstName and lastName:

Here are some examples of what you can do with generated columns and these string values:

• CONCAT( value1, value2, …, valueN ) – concatenate values.
  Example: CONCAT( firstName, ' ', lastName ) produces "Spong Bob"
• CONCAT_WS( separator, value1 value2, …, valueN ) – concatenate values with separator.
  Example: CONCAT_WS( ', ', firstName, lastName ) produces "Sponge, Bob"
• TO_BASE64( value ) – encodes value using the base-64 encoding.
  Example: TO_BASE64( firstName ) produces U3Bvbmdl
• FROM_BASE64( value ) – decodes a base-64 encoded value.
  Example: FROM_BASE64( TO_BASE64( firstName ) ) produces "Sponge"
• LENGTH( value ) – returns the length of value measured in bytes.
  Example: LENGTH( firstName ) produces 6
• LOWER( value ) – returns value in lower case.
  Example: LOWER( firstName ) produces "sponge"
• UPPER( value ) – returns value in upper case.
  Example: UPPER( CONCAT( firstName, ' ', lastName ) ) produces "SPONGE BOB"

There are many more date/time functions supported by Backendless. For a complete list, see the MySQL documentation.

Working with Dates

Suppose there is a table with a column of type DATETIME.  The column name is deliveryDate:

Here are some examples of what you can do with generated columns and this DATETIME column:

• ADDDATE( value, days ) – adds the specified number of days to the value.
  Example: ADDDATE( deliveryDate, 15 ) returns 05/15/2020 00:00:00
• DATEDIFF( value1, value2 ) – returns the difference between value1 and value2 where both values are of the DATETIME type.
  Example: DATEDIFF( created, deliveryDate ) – returns the number of days between the date when the order had been created and the delivery date.
• DAYNAME( value ) – returns the name of the weekday for value.
  Example: CONCAT( 'Your delivery will be on ', DAYNAME(deliveryDate) ) produces "Your delivery will be on Thursday"
• DAYOFMONTH( value ) – returns the day of the month for date, in the range 1 to 31.
  Example: DAYOFMONTH( deliveryDate ) produces 30
• DAYOFWEEK( value ) – returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday)
  Example: DAYOFWEEK( deliveryDate ) produces 5
• DAYOFYEAR( value ) – returns the day of the year for date, in the range 1 to 366.
  Example: DAYOFYEAR( value ) produces 121
• HOUR( value ), MINUTE( value ), SECOND( value ) – return hour, minute and second elements of the datetime value. All elements are zero-based.

There are many more date/time functions supported by Backendless. For a complete list, see the MySQL documentation.

Conditional Values (IF/THEN/ELSE)

It is possible to create a generated column with an expression that includes some conditional logic. Consider the following example, it is a table called Order.  Suppose we need to determine the priority of delivery depending on the day of the week of the delivery date. If the delivery date is between Monday through Friday, the priority is "HIGH" and if it is on the weekend (Saturday or Sunday), the priority is "MEDIUM". Here’s what the expression for the generated column could look like:

CASE
  WHEN DAYOFWEEK( deliveryDate ) IN (2,3,4,5,6)   
    THEN 'HIGH'
  WHEN DAYOFWEEK( deliveryDate ) IN (1,7)  
    THEN 'MEDIUM'
END

When creating a generated column, the screen will look as shown below:

And the magic happens automatically:

"HIGH" priority – the date lands on a weekday:

"MEDIUM" priority – the date lands on the weekend:

As you can see this can get extremely powerful and be very useful for your applications. If you have any questions or need help with composing your generated column expressions, please contact us on our support forum.

Cheers and Happy Coding!