Query your database with an object-oriented approach

Automatically translated from English

After learning how you can create, update, and delete data, here is a new entry in the ORDA series. In this post, you will see how you can query your datastore in an object-oriented mode. With ORDA, queries are lighter, more readable, and you can navigate through the whole data model using object-oriented concepts !

On top of that, ORDA makes it possible to very easily query several related tables, manage recursive relations on the same table, and use several logical operators in a single query. Plus, you don’t have to worry about setting relations between tables … manually or automatically !

Query with DataStore example 

GET STARTED WITH ORDA QUERYING

As when you would run a query on a table, querying with ORDA is done on a dataclass (which corresponds to a table).

There are several ways to query a dataclass. The following provides a quick overview. For more in-depth information about the query() method, check out the documentation.

1- Provide the query as a single String

Here is a query to get all employees whose first name starts with ‘S’:

C_OBJECT($employees)
$employees:=ds.Employee.query("firstName='S@'") // Query on Employee table

2- Provide the query and values separately using placeholders

Now, let’s say you want to get all employees whose first name starts with ‘D’ or ‘E’. Please note that values to apply are offset in the query and they are referred to with :1, :2, … :n, as shown below:

C_OBJECT($employees)
$employees:=ds.Employee.query("firstName=:1 or firstName=:2";"D@";"E@") // Query on Employee table

Variation: You can also use placeholders through a collection to provide the values. As the example below demonstrates:

C_OBJECT($employees;$params)
// You can use a collection to provide values to apply in the query
$params:=New object
$params.parameters:=New collection("D@";"E@")
$employees:=ds.Employee.query("firstName=:1 or firstName=:2";$params) // Query on Employee table

some Perks of the new way of querying

EASILY USE A RECURSIVE LINK ON A SINGLE TABLE

Consider this recursive link on the “Employee” table:

Using the classic way

This code requests all employees whose first name starts with the letter ‘A’, with a manager having a last name that begins with the letter ‘S’:

ARRAY LONGINT(_managersIDs;0)
QUERY([Employee];[Employee]firstName;"=";"A@")
CREATE SET([Employee];"a")
QUERY([Employee];[Employee]lastName;"=";"S@")
SELECTION TO ARRAY([Employee]ID;_managersIDs)
QUERY WITH ARRAY([Employee]managerID;_managersIDs)
CREATE SET([Employee];"s")
INTERSECTION("a";"s";"result")
USE SET("result")
CLEAR SET("a")
CLEAR SET("s")

using orda

This query is much lighter…

C_OBJECT($entitySelection)
$entitySelection := ds.Employee.query("firstName=:1 and manager.lastName=:2";"A@";"S@")

Don’t wait … start using ORDA now for shorter, cleaner, and more readable code!

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