By now you’ve surely noticed that ORDA queries are not only light and readable, they also make it easy to navigate through the entire data model using object-oriented concepts! In this blog post, we showed you how to write powerful and easy-to-maintain queries. One of the methods recommended was providing the query and values separately via placeholders. 4D v17 R5 takes placeholders a step further by allowing you to write generic ORDA queries: say hello to named placeholders for values!
HDI: Example of named placeholders for values in ORDA queries
These new placeholders are provided as object parameters in the query settings. Since an object is a key / value map, it’s very easy to use them in your queries.
An example is worth a thousand words
Here’s a query to get a client named Charlie with Loyal client as a comment. Does this ring any bells? If not, you should check out this post to refresh your memory.
C_OBJECT($clients)
$clients:=ds.Clients.query("name = :1 and comment = :2";"Charlie@";"Loyal client")
The query can also be written as:
C_OBJECT($settings;$clients)
$settings:=New object
$settings.parameters:=New object("givenName";"Charlie@";"givenComment";"Loyal client")
$clients:=ds.Clients.query("name = :givenName and comment = :givenComment";$settings)
Just use the placeholder with its name prefixed by “:” .
Your code is easily readable and maintainable, however, an even greater advantage is being able to write generic queries whose value parameters may come from different sources (a user’s interface or a request).
What about a pattern like this?
You can also provide your users a query interface where they can choose the search criteria and the values to apply.
The code below is for an interface that allows a salesperson to browse a list of their clients. It returns filtered content according to the salesperson’s ID.
C_OBJECT($formData;$settings;$clients)
C_TEXT($queryString)
$formData:=New object
DIALOG("QueryEditor";$formData)
CLOSE WINDOW
if (OK=1)
//The logged sales person can only browse their clients
$queryString:="salesPersonUserId = :givenUserId"
//The $formData object comes from the user's interface with search criteria filled
//It contains the sales person's user id and some additional search criterias (name and city)
$settings:=New object
$settings.parameters:=$formData
If ($settings.parameters.givenName#Null)
$queryString:=$queryString+" and name = :givenName"
End if
If ($settings.parameters.givenCity#Null)
$queryString:=$queryString+" and city.name = :givenCity"
End if
$clients:=ds.Clients.query($queryString;$settings)
end if
The examples above are querying a dataClass, but if you check the documentation, you’ll see it’s appliable on collections too!
As you can see, building queries dynamically is a piece of cake. Download and run the HDI to learn more!