More sophisticated ORDA queries with formulas

Automatically translated from English

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 = "England"

$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 = "England"

$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.

 

Avatar

• Product Owner •

Marie-Sophie Landrieu-Yvert has joined the 4D Product team as a Product Owner in 2017. As a Product Owner, she is in charge of writing the user stories then translating it to functional specifications. Her role is also to make sure that the feature implementation delivered is meeting the customer need.

Marie-Sophie graduated from the ESIGELEC Engineering School and began her career as an engineer at IBM in 1995. She participated on various projects (maintenance or build projects) and worked as a Cobol developer. Then she worked as an UML designer and Java developer. Lately her main roles were analyzing and writing functional requirements, coordinate business and development teams.