Searching for objects – app.ducx Query Language
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”.
Options
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.
Properties
It is useful to define properties that are accessed later on because these properties 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.
Classes
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 = COO.1.1.1.445 |
Condition
Supplying values for properties restricts the results further. 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 - As object constants use the object addresses.
- 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
- Expression keywords can be used as values
- Example: .objowner = coouser
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 examples
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 SCOPE (#FSCLEGALHOLD@1.1001:LegalHoldQueryScope) LIMIT 100 SELECT objname FROM Object WHERE .FSCLEGALHOLD@1.1001:objlegalholds.objowner = coouser // Returns the biggest content size of all content objects coort.SearchValues(cootx, "SELECT MAX(.content.contsize) FROM ContentObject") |