Imprint
2020 August Release

Searching for objects – app.ducx Query LanguagePermanent link for this heading

The app.ducx Query Language can be used to search for objects in Fabasoft Folio. The search always refers to an object class (and by default derived object classes). To carry out a search the Fabasoft Folio Runtime methods SearchObjects and SearchObjectsAsync can be used. SearchObjects returns the search result array at once (10,000 objects at the maximum) whereas SearchObjectsAsync returns a searchresult, which can be used to step through the result (without limit). Additionaly, the Fabasoft Folio Runtime method SearchValues can be used. SearchValues returns an aggregated value using COUNT, SUM, MIN or MAX. Using SearchValues, the evaluation of the Fabasoft Folio Query conditions occurs only in the database. For security reasons this method is only available for privileged users.

The following example shows a Fabasoft app.ducx expression that illustrates how to search for orders at once and asynchronously.

Example

integer @bulksize = 150;
string @query = "SELECT objname FROM APPDUCXSAMPLE@200.200:Order";

// Performs a search with SearchObjects
Order[] @results = coort.SearchObjects(cootx, @query);
%%TRACE("Number of hits", count(@results));

// Performs an asynchronous search with SearchObjectsAsync()
searchresult @sr = coort.SearchObjectsAsync(cootx, @query);
Order[] @resultsAsync = null;

// Steps through the search result
while ((@resultsAsync = @sr.GetObjects(@bulksize)) != null) {

  %%TRACE("Fetched chunk of search results", @resultsAsync);

  for (Order @order : @resultsAsync) {
    %%TRACE("Result entry", @order.objaddress);
  }
}

// Count objects with SearchValues

@query = "SELECT COUNT(*) FROM APPDUCXSAMPLE@200.200:Order";

integer @objcnt = coort.SearchValues(cootx, @query);

A search query is built up by following parts:

  • Options (optional)
    Options can be used to restrict the search. A description of available options can be found afterwards in this chapter.
  • SELECT clause
    In the SELECT clause properties should be defined that are accessed later on because these properties of found objects are loaded in the cache. When accessing these objects no further server request is necessary to read the defined properties.
    SELECT * loads all properties in the cache and therefore should only be used if many properties are used further on.
  • FROM clause
    Defines the object classes that should be searched for. Per default derived object classes are also included in the search result. If derived object classes should not be found use the property objclass in the WHERE clause. In the following example only folders are found and not for instance synchronized folders. Example: SELECT objname FROM COODESK@1.1:Folder WHERE objclass = 'COODESK@1.1:Folder'
  • WHERE clause (optional)
    The WHERE clause is used to restrict the search result by defining conditions.

Syntax

{Options} SELECT Properties FROM Classes [WHERE Condition]

A complete reference of the grammar can be found in chapter “Grammar of the app.ducx Query Language”.

OptionsPermanent link for this heading

In most cases, no options will be required.

  • LIMIT
    Restricts the search result to the defined number of objects. This setting can only be used with SearchObjects. The maximum value is 10,000.
  • PRELOAD
    In case of an asynchronous search the PRELOAD value defines how many objects are fetched in advance when stepping through the search result.
  • TIMEOUT
    Restricts the search time to the specified value (seconds).
    Example: TIMEOUT 3
  • NOCHECK
    By default it is checked whether the defined properties in the SELECT clause belong to the object classes in the FROM clause. This option disables the check.
  • NOEXEC
    Only a syntax check of the search query takes place, but the search itself gets not executed.
  • NOHITPROPERTIES
    In case of a full-text search several hit properties (hit rank, hit count, hit display) may be displayed in the search result. With this option no hit properties are returned.
    Note: A full-text search is triggered when using CONTAINS or LIKE '%%something' in the WHERE clause.
  • HITPROPERTIES
    In case of a full-text search hit properties (COOSYSTEM@1.1:contenthitrank, COOSYSTEM@1.1:contenthitcount, COOSYSTEM@1.1:contenthitdisplay) can be displayed in the search result. This option can be used to define which hit properties are returned.
    Example: HITPROPERTIES(COOSYSTEM@1.1:contenthitrank)
  • IGNORECASE
    A case-insensitive search is carried out, even if the search is configured as case-sensitive in the domain and database.
  • Location
    If no location is specified the search is carried out in the COO stores of the user’s local domain.
    • LOCAL
      Restricts the search to the COO stores of the user’s local domain.
    • GLOBAL
      The search is carried out in all known domains.
    • DOMAINS
      Restricts the search to the defined domains (list of addresses of the domain objects).
      Example: DOMAINS('COO.200.200.1.1','COO.200.200.1.7')
    • CACHE
      Restricts the search to the kernel cache.
    • TRANSACTION
      Restricts the search to objects belonging to the current transaction.
    • SCOPE
      The scope allows to define a query scope object (reference or object address) that defines the location the search is carried out.
      Example: SCOPE(#COOSYSTEM@1.1:LoginQuery), SCOPE('COO.1.1.1.2686')
    • SERVICES
      Restricts the search to the defined COO services.
    • STORES
      Restricts the search to the defined COO stores.
    • ARCHIVES
      Restricts the search to the defined archive stores.

PropertiesPermanent link for this heading

It is useful to define properties that are accessed later on because these properties of found objects are loaded in the cache. When accessing these objects no further server request is necessary to read the defined properties.
SELECT * loads all properties in the cache and therefore should only be used if many properties are used further on.

ClassesPermanent link for this heading

Objects of the defined object classes (and derived object classes) are searched. If derived object classes should not be found use the property objclass in the WHERE clause.

Example

SELECT objname FROM COOSYSTEM@1.1:User WHERE objclass = 'COOSYSTEM@1.1:User'

ConditionPermanent link for this heading

To restrict the search specify values for properties of the object class defined in the FROM clause. Following general rules apply:

  • Fully qualified references are used to define the properties. COOSYSTEM@1.1 may be omitted for properties belonging to this software component. It is good practice to start the reference with a period to make clear that the property belongs directly to the object and is not part of a compound type.
  • Compound types can be accessed using a property path.
    Example: .COOMAPI@1.1:emailinformation.COOMAPI@1.1:emailaddress
  • String constants are defined with double quotes " or single quotes '. Special characters like " and ' can be escaped with a backslash \.
  • Dates have to be provided this way: yyyy-mm-dd hh:mm:ss

Example

WHERE userlogname LIKE 'A%' AND userroles.userrolepos = 'COO.200.200.1.7'

Following keywords can be used to specify a condition:

  • NOT
    The expression yields the value true if the operand evaluates to false, and yields the value false if the operand evaluates to true.
  • AND
    Indicates whether both operands are true.
  • OR
    Indicates whether either operand is true.
  • <, <=, >, >=, =, <>
    Compares two operands: less, less equal, greater, greater equal, equal, not equal
  • [SOUNDS] [NOT] LIKE
    LIKE determines whether the left string matches the right string. The %, *, ?, and _ wildcards can be used in the right string operand. The LIKE operator can be preceded by the SOUNDS keyword for a phonetic comparison.
    Example: WHERE COOMAPI@1.1:emailinformation.COOMAPI@1.1:emailaddress LIKE "*fabasoft.com"
  • [NOT] CONTAINS
    Triggers a full text search.
    Example: WHERE COOSYSTEM@1.1:contcontent CONTAINS 'Workflow'
  • [NOT] IN
    Determines whether the value is in the defined list.
  • [NOT] INCLUDES
    Determines whether the value of the right operand is an element of the list provided in the left operand.
  • [NOT] BETWEEN … AND …
    Determines whether the value is between the specified boundaries.
  • IS [NOT] NULL
    Determines whether the property has a value.
  • UPPER
    Converts all characters of a property to upper case (string data type).
  • LOWER
    Converts all characters of a property to lower case (string data type).
  • SUM
    Calculates the sum of all property values (numeric data type).
  • AVG
    Calculates the average of all property values (numeric data type).
  • COUNT
    Calculates the number of elements of a property (any data type).
  • MIN
    Calculates the smallest value of all property values (numeric, string, date data type).
  • MAX
    Calculates the largest value of all property values (numeric, string, date data type).

Search query examplesPermanent link for this heading

The following example shows a variety of possibilities to define search queries.

Example

// Returns all Note objects
SELECT objname FROM NOTE@1.1:NoteObject

// Returns contact persons with "Jefferson" in COOSYSTEM@1.1:usersurname
SELECT objname FROM FSCFOLIO@1.1001:ContactPerson WHERE .usersurname = 'Jefferson'

// The settings in the query scope object restrict the search
// Account objects are returned that reference the current user as owner
SCOPE (#FSCFOLIOCRM@1.1001:CRMQueryScope) SELECT * FROM FSCFOLIOCRM@1.1001:Account WHERE .objowner = coouser

// The search is restricted to the domain with object address COO.1.1900.1.1
DOMAINS ('COO.1.1900.1.1') SELECT .objname FROM COOSYSTEM@1.1:CurrentDomain

// Returns users that are created between the last hour and last half-hour
SELECT objname FROM COOSYSTEM@1.1:User WHERE
(.objcreatedat >= coonow-60*60)) AND
(.objcreatedat < coonow-30*60))

// Returns users with a task in the task list
SELECT objname FROM COOSYSTEM@1.1:User WHERE .COOAT@1.1001:attasklist IS NOT NULL

// A query scope object is used and the search is restricted to 100 result entries
coort.SearchObjects(cootx, "SCOPE (#FSCLEGALHOLD@1.1001:LegalHoldQueryScope) LIMIT 100 SELECT objname FROM Object WHERE .FSCLEGALHOLD@1.1001:objlegalholds = \"" + cooobj.objaddress + "\"");

// Returns the biggest content size of all content objects
coort.SearchValues(cootx, "SELECT MAX(.content.contsize) FROM ContentObject")

Download PDF

Download PDF