Query your database with an object-oriented approach

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!