Querying Records With SMQL
Examples
These examples require you to import the Database class:
import com.servicemax.core.Database;
When you query records, you can specify which fields to load. Keep in mind, however, that you can use only the fields you loaded after you run the query:
def records = Database.query("SELECT io_uuid, io_name FROM io_basic_object_for_test");
def record = records.get(0);
record.io_name // this is OK
record.io_updated_by // this will cause an exception, because the field was not loaded
def record = records.get(0);
record.io_name // this is OK
record.io_updated_by // this will cause an exception, because the field was not loaded
You can load all fields in queried records by using the * (wildcard) character:
def records = Database.query("SELECT * FROM io_basic_object_for_test");
def record = records.get(0);
record.<field> // all the fields will be loaded and available
def record = records.get(0);
record.<field> // all the fields will be loaded and available
You can also use the wildcard to load all fields in related records (supporting n-depth levels). To load all fields of the parent related record for every child record:
def records = Database.query("SELECT io_parent.* FROM io_child_smql_test_object");
def record = records.get(0);
record.io_parent.io_name // this will retrieve the parent's record name
def record = records.get(0);
record.io_parent.io_name // this will retrieve the parent's record name
To load all fields of the grandparent related record for every child record:
def records = Database.query("SELECT io_parent.io_grandparent.* FROM io_child_smql_test_object");
def record = records.get(0);
record.io_parent.io_grandparent.io_name // this will retrieve the grandparent's record name
def record = records.get(0);
record.io_parent.io_grandparent.io_name // this will retrieve the grandparent's record name
Object aliases are also supported in queries. To load all fields of evert child record and all the fields of its related parent and grandparent records:
def records = Database.query("SELECT child.*, child.io_parent.*, io_parent.io_grandparent.* FROM io_child_smql_test_object as child");
def record = records.get(0);
record.io_name
record.io_parent.io_name
record.io_parent.io_grandparent.io_name
def record = records.get(0);
record.io_name
record.io_parent.io_name
record.io_parent.io_grandparent.io_name
You can retrieve a single record with the querySingleResult method.
def wo = Database.querySingleResult("select svmx_component from svmx_work_order where io_uuid= :io_uuid", [io_uuid:woid]);
wo.svmx_component //retrieve the component from the queried work order
wo.svmx_component //retrieve the component from the queried work order
To filter the selected records, you can use named parameters.
def records = Database.query("SELECT io_uuid, io_name FROM io_basic_object_for_test where io_uuid = :io_uuid", [io_uuid:recordUUID])
You can query related data by using the identifier of the relationship field. For example, the following code sample illustrates a referential relationship between io_relational_child_object_for_test and io_relational_parent_object_for_test. The relationship field is io_relational_field_for_test.
def records = Database.query("SELECT io_uuid, io_relational_field_for_test FROM io_relational_child_object_for_test");
def record = records.get(0);
def record = records.get(0);
You can use the field full identifier to access the related record.
record.io_relational_field_for_test.io_name
To retrieve only the UUID of the related record, you can prefix the field full identifier with an underscore (_).
record._io_relational_field_for_test
You can also set filters on fields in related records.
def records = Database.query("SELECT io_uuid, io_name FROM io_relational_child_object_for_test WHERE io_relational_field_for_test.io_name = 'parent name'");
Additionally, you can eager-load related records of dynamic relationships and use them with filters in queries. In this case, given that a dynamic relationship can have multiple target objects, when you eager-load fields of the target data type, you must the specify the intended target object enclosed in square brackets ([ ]).
Select all documents related to pages created by the system user:
Database.query("select * from io_document where io_related_to[io_page].io_created_by.io_username = 'system'")
Select all documents related to pages whose URLs begin with /resource or that are related to themes whose names begin with ServiceMax, and retrieve all fields of records related to filtered records:
Database.query("select io_related_to[io_page].*, io_related_to[io_theme].* from io_document " +
"where io_related_to[io_page].io_url like '%/resources%' or io_related_to[io_theme].io_name like '%ServiceMax%'")
"where io_related_to[io_page].io_url like '%/resources%' or io_related_to[io_theme].io_name like '%ServiceMax%'")
 |
In the previous example, io_related_to is a dynamic relationship field that is related to multiple targets (such as Page, Theme, and so on). The full identifier of the intended target object is enclosed in square brackets ([ ]).
|
Results
By default, results are returned as a collection that can be iterated but should not be modified. You can get results as the following other collections as well:
• As a Set, with the record’s io_uuid field used to identify it:
def records = Database.queryAsSet("SELECT io_uuid, io_name FROM io_basic_object_for_test");
• As a Map, with the record’s io_uuid field used as the map key:
def records = Database.queryAsMap("SELECT io_uuid, io_name FROM io_basic_object_for_test");
• As a List, which is similar to the query method except that the result list can be modified:
def records = Database.queryAsList("SELECT io_uuid, io_name FROM io_basic_object_for_test");
• As a SortedList, where the result is a sorted list and newly added records are inserted in as specified in the ORDER BY clause:
def records = Database.queryAsSortedList("SELECT io_uuid, io_name FROM io_basic_object_for_test ORDER BY io_name");
Clauses
You can sort records by using the ORDER BY clause (ASC and DESC). LIMIT and OFFSET are also supported.
LIKE and ILIKE
//LIKE clause is case sensitive.
def records = Database.query("SELECT * FROM io_showcase WHERE io_name LIKE '%GE%'")
//ILIKE clause is case insensitive.
def records = Database.query("SELECT * FROM io_showcase WHERE io_name ILIKE '%GE%'")
def records = Database.query("SELECT * FROM io_showcase WHERE io_name LIKE '%GE%'")
//ILIKE clause is case insensitive.
def records = Database.query("SELECT * FROM io_showcase WHERE io_name ILIKE '%GE%'")
LIMIT
def records = Database.queryAsSortedList("SELECT io_uuid, io_name FROM io_basic_object_for_test ORDER BY io_name LIMIT 2");
assertEquals 2, records.size() //the LIMIT clause will return only 2 records
assertEquals 2, records.size() //the LIMIT clause will return only 2 records
OFFSET
def records = Database.queryAsSortedList("SELECT io_uuid, io_name FROM io_basic_object_for_test ORDER BY io_name OFFSET 5");
//the OFFSET clause will cause skipping the first 5 records before beginning to return records
//the OFFSET clause will cause skipping the first 5 records before beginning to return records
Combine LIMIT and OFFSET
def records = Database.queryAsSortedList("SELECT io_uuid, io_name FROM io_basic_object_for_test ORDER BY io_name LIMIT 10 OFFSET 5");
assertEquals 10, records.size() //the LIMIT clause will return only 10 records and the OFFSET will cause skipping the first 5 records before beginning to return records
assertEquals 10, records.size() //the LIMIT clause will return only 10 records and the OFFSET will cause skipping the first 5 records before beginning to return records
Aggregate Function COUNT
def numOfRecords = Database.queryObject("SELECT COUNT() FROM io_child_smql_test_object");
Large Record Sets
For large sets of records, you can use the following alternative query syntax, which does not load all data into memory at the same time:
Database.queryInBatches("SELECT io_uuid, io_name FROM io_basic_object_for_test", [:], recordsInMemoryAtTheSameTime,
{ record -> record.io_name //do something with the record here }
);
{ record -> record.io_name //do something with the record here }
);
Records are read in batches of recordsInMemoryAtTheSameTime size and are processed in the closure passed as the last parameter of the method.
Translation Field Filtering
You can filter fields with the String Localized or Text Localized data types by using the translation_contains function, which checks whether fields contain a specified language, referenced by the ISO 693-1 language code.
def records = Database.query("SELECT * FROM io_showcase WHERE translation_contains(io_showcase_string_localized, 'en', 'Hello'")
For more information: