Dynamic Where sample (desktop) (Delphi)

The Dynamic Where sample demonstrates 3 different ways to use Dynamic Where to selectively retrieve data from a table in an instance of Relativity Server.

Getting Started

The sample is located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for Delphi\DynamicWhere.

To build it, you will need a Delphi RAD Studio and like all the samples provided with Data Abstract for Delphi you will need to be running Relativity Server with the DASamples Domain and the Simple Schema available.

Running the Sample

The UI is comprised of a table that displays the data, and a toolbar that has a combo box for the server address and another for choosing which of the three Dynamic Where clauses to use to retrieve the table data from Relativity Server. Clicking on one of the available clauses will cause the sample to retrieve the table data and update the UI.

The three Dynamic Where clauses available are:

  • "All Users" which will retrieve all users from the "Users" table.
  • "Simple Condition" will retrieve all rows where the Role field contains the word "Manager".
  • "Complex Condition" builds on the "Simple Condition" to return rows where the Role field contains "Manager" and the Name field begins with J, or any rows that match the Id value of 20,22 or 24.

When the sample is run, if an instance of Relativity Server is running on the local machine then the "All Users" Dynamic Where clause will be used to to retrieve all the "User" records from the "Users" table.

Examining the Code

The sample is comprised of two classes, ClientDataModule which handles the interaction with the schema and ClientForm that handles the UI interaction.

The "Users" schema table used in this sample is comprised of 8 fields and 25 rows, which will be filtered down to just 9 or 5 rows depending on the dynamic where condition. The "Users" table is in the "simple" schema which is part of the "DASamples" domain.

The Simple DynamicWhere condition

This section covers the building and executing a simple Dynamic Where expression which will retrieve all records from the "Users" table where the Role field contains the word "Manager".

To build the simple Dynamic Where expression we need to create a Binary Expression and assign that expression to the DynamicWhere property of the data table.

The TDAMemDataTable provides a handy helper class, an instance of TDAWhereBuilder, that can be used to build WHERE statements and is retrieved with the previously mentioned DynamicWhere property. We use its NewBinaryExpression function to create a Binary Expression. The function takes four arguments; a table name, the field name, a binary operator and the value we are looking for. The result is assigned to the Expression property of the TDAWhereBuilder object.

Here as we want a basic expression "Role is like 'Manager'", the table object already has a name so we pass an empty string as the first argument. The second argument is assigned the name of the field we are interested in which is "Role". The third argument is an enumeration provided by TDABinaryOperator, here we want dboLike. The last argument is the value we are looking for.

The Dynamic Where expression is executed and the data retrieved when the Open procedure is called.

// fClientForm.pas
lDynWhere := ClientDataModule.tbl_Users.DynamicWhere;

... snipped code ...

lDynWhere.Expression := lDynWhere.NewBinaryExpression('','Role',dboLike,'%manager%');

... snipped code ...

ClientDataModule.tbl_Users.Open;

Using a complex DynamicWhere condition

Building off the previous condition, this section covers creating a more complex Dynamic Where expression that will retrieve all records from the "Users" table where the Role field contains the word "Manager" and the Name field begins with a "J", or any records where the Id value is equal to 20, 22 or 24. To put it another way:

Condition: (Role CONTAINS "manager" AND Name BEGINSWITH "J") OR Id IN {20, 22, 24}

Building the actual Dynamic Where expression is a bit more complex. We actually need to create 5 Binary Expressions to cover the whole Dynamic Where expression. As before we use the TDAWhereBuilder helper class to create those binary expressions.

The first Binary Expression is a container for the other binary expressions. The first argument is the binary expression that retrieves those fields where the Role field contains "Manager" and the Name field begins with "J"; lets call this expression A. The second argument is the binary expression that searches for particular values in the id field, we'll call this expression B. The last argument supplied is an dboOR operator from the TDABinaryOperator enumerator which is used to indicate that the expression should evaluate to true if either the first or second argument returns a match.

Expression A is comprised of two more binary expressions and a dboAnd operator to indicate that both of the expressions must evaluate to true for expression A to be considered true. The first binary expression is exactly the same as the one created in Step 2. The second changes the second argument to use the Name field, and the last argument to "J%".

Looking closer at expression B uses an alternative form of the NewBinaryExpression procedure that takes two instances of TDAWhereExpression (which is the base class for any of the where expression classes) and a TDABinaryOperator operator. For the first argument of the expression we create an instance of TDAFieldExpression using the NewField function which takes the field name we are interested in as the second argument, as we don't need to set the table name the first argument is an empty string. The second argument of the expression is an instance of TDAListExpression which takes an array of TDAConstantExpression objects; one for each id value we want to match against. Finally as we want to test if the id value is in the list, we supply the dboIn TDABinaryOperator operator.

The Dynamic Where expression is executed and the data retrieved when the Open procedure is called.

// fClientForm.pas
lDynWhere := ClientDataModule.tbl_Users.DynamicWhere;

... snipped code ...

lDynWhere.Expression := lDynWhere.NewBinaryExpression(
      lDynWhere.NewBinaryExpression(
        lDynWhere.NewBinaryExpression('','Role',dboLike,'%manager%'),
        lDynWhere.NewBinaryExpression('','Name',dboLike,'J%'),
        dboAnd
      ),
    lDynWhere.NewBinaryExpression(
      lDynWhere.NewField('','id'),
      lDynWhere.NewList(
        [lDynWhere.NewConstant(20),
        lDynWhere.NewConstant(22),
        lDynWhere.NewConstant(24)]),
      dboIn),
    dboOR);

... snipped code ...

ClientDataModule.tbl_Users.Open;