When writing queries, you often need to make decisions or choose between multiple values based on a condition.
In CQL, you can use conditionals to express this kind of logic. And as with other expressions, there are two styles you can choose from: one that resembles everyday language and another that is more concise, similar to programming syntax.
This style provides a more human-friendly way to express conditionals in CQL. It is similar to how you would write a conditional in English, making the expressions feel more natural.
The syntax for expressing conditionals in natural style is as follows:
/*<consequence>*/ if /*<condition>*/ else /*<alternate>*/
Alternatively, you can use the otherwise keyword instead of else:
/*<consequence>*/ if /*<condition>*/ otherwise /*<alternate>*/
You can also omit the else or otherwise keyword if you do not need an alternate value, in which case the alternate value will be null:
/*<consequence>*/ if /*<condition>*/
Here's an example that conditionally chooses between two strings based on whether the user is new or not:
"Eligible" if user is returning otherwise "Ineligible"
Because conditionals are chainable, you can use multiple else keywords to add as many cases as you need:
let orders = user's stats' orders;20% if orders >= 10 else 10% if orders >= 5 otherwise 5%
In this example, we map the number of orders to a discount percentage. Frequent customers get 20% off, while customers with fewer orders get 10% or 5% off, depending on the number of orders.
To improve clarity, we recommend using parentheses for nested conditionals:
20% if orders >= 10 else (10% if orders >= 5 else 5%)
You can also use line breaks to make the expression more readable:
20% if orders >= 10else 10% if orders >= 5otherwise 5%
The above example is especially beneficial for long conditionals, as it improves readability and makes the expression easier to maintain. This allows you to easily add or remove some cases without affecting others.
The syntax for shorthand conditionals is as follows:
/*<condition>*/ ? /*<consequence>*/ : /*<alternate>*/
You can also omit the /*<consequence>*/ to denote you want to use the value of the condition as the consequence:
/*<condition>*/ ?: /*<alternate>*/
The above example is equivalent to the following:
/*<condition>*/ ? /*<condition>*/ : /*<alternate>*/
The following example shows how to use shorthand conditionals to choose between two strings based on whether the user is new or not:
user is new ? "Eligible" : "Ineligible"
As with natural conditions, you can chain multiple shorthand conditionals to create more complex expressions:
let orders = user's stats' orders;orders >= 10 ? 20% : (orders >= 5 ? 10% : 5%)
When chaining conditionals, it is a good practice to use parentheses for clarity and to avoid precedence issues:
orders >= 10 ? 20% : (orders >= 5 ? 10% : 5%)
When using conditionals, it is important to remember that the condition must be a boolean value. Otherwise, you will get an evaluation error.
Another aspect to consider is that the conditional expression is "greedy", so anything that follows it will be considered part of the alternate branch.
For example, consider the following expression:
90% if user is returning otherwise 10% * price
There are two possible interpretations for this expression: either the alternate branch is 10% * price or the result of the conditional is multiplied by price. As the conditional is greedy, the first interpretation is the correct one.
To fix this, you can add parentheses around the conditional expression:
(90% if user is returning otherwise 10%) * price
The expression now performs the intended task. It first evaluates the conditional expression to determine the discount percentage, then it multiplies the result by the price to get the discounted price.