More sophisticated ORDA queries with formulas

In 4D v17 R6, ORDA queries are becoming increasingly more powerful and concise. This blog post is for those of you who need more sophisticated search criteria than just a simple syntax like “name = Smith‘”. With this R-release, you can use any project method or 4D expression in the query() member method by using formulas.

What better way to explain this feature than with an example? Let’s dig into the details.

HDI: Example of ORDA queries using formulas

WRITE A QUERY USING a FORMULA

The query() member method has been updated to support a single Formula object as a parameter.

Example

In the Students dataClass, we search for students with a score in English higher than their overall average score.

Note that each entity is accessed by the formula with the This command.

C_OBJECT($formula;$students)
// In the Students dataClass, grades is an object field containing students' grades for each subject
// Example of JSON representation of the grades object field:
// {math:50,english:90,history:85}
// The studentAverage project method computes the student's overall average in all subjects
$formula:=Formula(This.grades.english>studentAverage (This))
$students:=ds.Students.query($formula)


use a Formula with other search criteria

Formulas can easily be combined with other search criteria. Below is an example to show you how.

Example

In the Students dataClass, we continue to search for students with a score in English higher than their overall average AND whose country is England.

The $formula object is used as a placeholder value, like any other one.

C_OBJECT($formula;$students)
C_TEXT($country) // $country = "France"

$formula:=Formula(This.grades.english>studentAverage (This))
$students:=ds.Students.query(":1 and country=:2";$formula;$country)

use a formula in the query string

A formula can also be inserted in the query string with an eval() statement.

While this can be convenient for quickly coding a query, you’ll miss the advantages from using a formula object (such as autocompletion, colors in the code editor, and search callers).

C_OBJECT($students)
C_TEXT($country) // $country = "France"

$students:=ds.Students.query("eval(This.grades.english > studentAverage (This)) and country=:1";$country)


NOTE: The query() member method on the entity selection object has also been updated to support formulas. Check the documentation to learn more.