SMQL Statement Syntax
To work with the sample code in this section, import the following Groovy class:
import com.servicemax.core.Database;
WHERE Clause
def records = Database.query("select * from io_showcase where io_name = 'UI Unit Test Sample Record 2'")
Query With SMQL WHERE Condition on Eager-Loaded Field
// Select all the Showcase Details whose parent Showcase's Name is 'UI Unit Test Sample Record 2'.
// For each record filtered, eager-load its parent's Name and 'Created On' fields
def records = Database.query("SELECT io_showcase.io_name, io_showcase.io_created_on FROM io_showcase_detail"
+ " WHERE io_showcase.io_name = 'UI Unit Test Sample Record 2'")
Query With SMQL WHERE Condition on Eager-loaded Field (Second Level)
// Select all the Showcase Details whose parent Showcase was created by user 'system'.
// For each record filtered, eager-load its parent's Name and 'Created On' fields
def records = Database.query("SELECT io_showcase.io_name, io_showcase.io_created_on FROM io_showcase_detail"
+ " WHERE io_showcase.io_created_by.io_username = 'system'")
WHERE Condition With Date
def records = Database.query("SELECT * FROM io_showcase WHERE io_showcase_date = '2013-07-09'")
For more examples, see Date and Time-Based Functions and Arithmetic later in this topic.
LIKE Clause
def records = Database.query("SELECT * FROM io_showcase WHERE io_name LIKE 'UI Unit Test Sample Record%'")
IN Clause
def records = Database.query("SELECT * FROM io_showcase WHERE io_showcase_option_list IN ('Option 2', 'Option 1')")
IN Clause With Parameters
def names = [];
names << "UI Unit Test Sample Record";
names << "UI Unit Test Sample Record 2";
def records = Database.query("SELECT * FROM io_showcase WHERE io_name IN :list", [list:names])
IN Clause on Eager-Loaded Field
def users = ['system', 'developer'];
//Select all the Showcase records which owner is 'system' or 'developer'
def records = com.servicemax.core.Database.query("SELECT * FROM io_showcase WHERE io_owner.io_username IN :users", [users: users]);
Eager-Loaded Field for Dynamic Relationships
Because dynamic relationships can have multiple target objects, in SMQL, regardless of whether a field of this datatype is selected or used in a WHERE condition filter, you must specify target objects enclosed between square brackets ([ ]).
// Select all the Documents related to Pages which url begins with '/resource'
Database.query("select * from io_document where io_related_to[io_page].io_url like '/resources%'")
// Select all the Documents related to Pages which url begins with '/resource' or related to Themes whose name begins with ServiceMax.
// For each record filtered, retrieve all the fields for its related record.
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%'")
// Select all the Documents related to Pages created by 'system' user
Database.query("select * from io_document where io_related_to[io_page].io_created_by.io_username = 'system'")
 |
Because io_related_to is a dynamic relationship field with multiple targets, you must specify the full identifier of the intended target object enclosed between square brackets ([ ]).
|
Get COUNT of Records by Using Database.queryObject
def records = Database.queryObject("SELECT COUNT() FROM io_showcase")
Query Object for Count With WHERE Condition
def records = Database.queryObject("SELECT COUNT() FROM io_showcase WHERE io_name = 'UI Unit Test Sample Record'");
def records = Database.queryObject("SELECT COUNT() FROM io_showcase WHERE io_name LIKE 'UI Unit Test Sample Record%'");
Query With SMQL WHERE Condition and Several IN Clauses
def list1 = [];
list1 << "Option 1";
list1 << "Option 2";
def list2 = [];
list2 << "UI Unit Test Sample Record";
list2 << "UI Unit Test Sample Record 2";
def records = Database.query("SELECT * FROM io_showcase"
+ " WHERE io_showcase_option_list IN :LIST1 AND io_name IN :list2", [LIST1: list1, list2: list2]);
Query With SMQL WHERE Condition Having Several Parameters
def params = [:];
params["date_param"] = "2012-12-01";
params["integer_param"] = 30;
params["text_param"] = "UI Unit Test Sample Record";
def records = Database.query("SELECT * FROM io_showcase"
+ " WHERE io_showcase_date >= :date_param"
+ " AND (io_name = :text_param OR io_showcase_integer > :integer_param)", params);
Order By DESC
def records = Database.query("SELECT * FROM io_showcase ORDER BY io_name DESC");
Order by DESC on Multiple Columns
def records = Database.query("SELECT * FROM io_showcase_detail ORDER BY io_showcase.io_showcase_integer DESC, io_amount DESC");
def records = Database.query("SELECT * FROM io_showcase ORDER BY io_showcase_integer ASC LIMIT 3 OFFSET 2");
Order by ASC and NULL Values Last
def records = Database.query("SELECT * FROM io_showcase ORDER BY io_showcase_interval ASC NULLS LAST");
LIMIT Clause
def records = Database.query("SELECT * FROM io_showcase ORDER BY io_showcase_integer LIMIT 2");
LIMIT ALL
def records = Database.query("SELECT * FROM io_showcase ORDER BY io_name ASC LIMIT ALL");
OFFSET Clause
def records = Database.query("SELECT * FROM io_showcase ORDER BY io_showcase_integer LIMIT 3 OFFSET 2");
SMQL Statement is Case Insensitive
def records = Database.query("select * from io_showcase");
Type Function
You can use the Type function to determine the object of a specified related record associated by a dynamic relationship.
This function is supported only for use with WHERE conditions, and can be used to compare field Type values in expressions with the following operators:
• =
• !=
• IN
• NOT IN
• IS NULL
• IS NOT NULL
Retrieve all events related to User records:
def records = Database.query("select * from io_event where Type(io_related_to) = 'io_user'")
Retrieve all events NOT related to Account records:
def records = Database.query("select * from io_event where Type(io_related_to) != 'io_account'")
Retrieve all events related to User or Role records:
def records = Database.query("select * from io_event where Type(io_related_to) IN ('io_user', 'io_role')")
Retrieve all events NOT related to Account or Task records:
def records = Database.query("select * from io_event where Type(io_related_to) NOT IN ('io_account', 'io_task')")
Retrieve all events NOT related to any records:
def records = Database.query("select * from io_event where Type(io_related_to) IS NULL")
Retrieve all events related to records:
def records = Database.query("select * from io_event where Type(io_related_to) IS NOT NULL")
Parameters are supported as usual by SMQL expressions:
def objects = ['io_user', 'io_role']
def records = Database.query("select * from io_event where Type(io_related_to) IN :list", [list: objects])
|
|
To compare against the Type function result, use the object’s full identifier or UUID.
|
Indexing for Fields with Data Type Dimension Greater Than Zero (Array)
For fields with data type dimension greater than zero (arrays of values), SMQL supports indexing to access individual array elements. You can use indexing in WHERE conditions, with all supported operators, and in ORDER BY clauses.
Retrieve all Showcase records whose City value in the Address field is Pleasanton:
def records = Database.query("select * from io_showcase where io_showcase_address[3] = 'Pleasanton'")
Retrieve all Showcase records whose City value in the Address field is Pleasanton or London:
def records = Database.query("select * from io_showcase where io_showcase_address[3] IN ('Pleasanton', 'London')")
Retrieve all Showcase records whose City value in the Address field is not NULL:
def records = Database.query("select * from io_showcase where io_showcase_address[3] IS NOT NULL")
Retrieve all Showcase records whose Route value in the Address field includes Hopyard:
def records = Database.query("select * from io_showcase where io_showcase_address[2] like '%Hopyard%'")
Retrieve all Showcase records ordered by the Country value in the Address field:
def records = Database.query("select * from io_showcase ORDER BY io_showcase_address[9]")
Retrieve all Showcase records ordered by the City value in the Address field, and return records with NULL field values first:
def records = Database.query("select * from io_showcase ORDER BY io_showcase_address[9] NULLS FIRST")
Retrieve Event records ordered by the object of the related records:
def records = Database.query("select * from io_event order by io_related_to[1]")
|
|
Index values start at 0.
|
Date and Time-Based Functions and Arithmetic
SMQL provides the following date and time functions to support queries with date and timestamp fields. By default, these functions use the time zone configured for the current user in User Parameters. If needed, you can specify a valid time zone.
• now(): Current date with timestamp.
• today(): Current date.
• beginning_of_week(): io_calendar_week_start_day in System Settings records.
• beginning_of_month(): First day of the current month, for example, 1 August 00:00.
• beginning_of_year(): First day of the current year, for example, 1/1/2020 00:00.
Examples
Records created today:
def records = com.servicemax.core.Database.query("select * from io_showcase where io_created_on >= today()")
Records updated during the current week:
records = com.servicemax.core.Database.query("select * from io_showcase where io_updated_on >= beginning_of_week()")
Records created during the current month:
records = com.servicemax.core.Database.query("select * from io_showcase where io_created_on >= beginning_of_month()")
Records created during the current year:
records = com.servicemax.core.Database.query("select * from io_showcase where io_created_on >= beginning_of_year()")
Combining Functions
You can also combine these functions in arithmetic expressions and add or subtract dates with various calendar expressions (hours, days, weeks, months, years).
Records created more than two years ago:
records = com.servicemax.core.Database.query("select * from io_showcase where io_created_on < beginning_of_year() - '2 years'")
Showcase records with showcase_date values older than two months:
records = com.servicemax.core.Database.query("select * from io_showcase where io_showcase_date <= beginning_of_month() - '2 months'")
Showcase records with showcase_date values of the previous day:
records = com.servicemax.core.Database.query("select * from io_showcase where io_showcase_date < today() and io_showcase_date >= today() - '1 day'")
IS_RELATED_TO Function
You can use the is_related_to Boolean function in WHERE conditions to indicate whether the record is related to a specific record UUID (or an array or list of record UUIDs) for a specified relationship. You can specify the relationship by using its full identifier or UUID. All platform relationship types are currently supported. The relationship direction is determined by the main object query. For hierarchical relationships, only the target-to-source direction is currently supported.
You can combine this function with the NOT operator to retrieve records not related to a specific record or list of records.
Examples
For a multiple relationship (source to target), retrieve all objects related to the Date Format Op field:
def field = Database.query("select io_uuid from io_field where io_identifier = 'date_format_op'").get(0)
def objects = Database.query("select io_identifier from io_object where is_related_to('io_fields',:id)", [id:field.io_uuid])
For a multiple relationship (source to target), retrieve all objects not related to the Date Format Op field:
def field = Database.query("select io_uuid from io_field where io_identifier = 'date_format_op'").get(0)
def objects = Database.query("select io_identifier from io_object where not(is_related_to('io_fields',:id))", [id:field.io_uuid])
For a multiple relationship (target to source), retrieve all fields related to the Showcase object:
def obj = Database.query("select io_uuid from io_object where io_identifier = 'showcase'").get(0)
def fields = Database.query("select io_identifier from io_field where is_related_to('io_fields',:id)", [id:obj.io_uuid])
For a referential relationship (source to target), retrieve all objects related to the Max application:
def app = Database.query("select io_uuid from io_application where io_identifier = 'Max'").get(0)
def records = Database.query("select * from io_object where is_related_to('io_object_application',:id)", [id:app.io_uuid])
For a referential relationship (source to target), retrieve all objects not related to the Max application:
def app = Database.query("select io_uuid from io_application where io_identifier = 'Max'").get(0)
def records = Database.query("select * from io_object where not(is_related_to('io_object_application',:id))", [id:app.io_uuid])
For a master/detail relationship (target to source), retrieve Showcase records related to the specified list of Showcase details:
def showcase_details = Database.query("select io_uuid from io_showcase_detail").collect{it.io_uuid}
def records = Database.query("select * from io_showcase where is_related_to('io_showcase_detail_showcase',:ids)", [ids:showcase_details])
For a hierarchical relationship (target to source), retrieve all pages that are parents of pages that contain Documentation in their titles:
def docPages = Database.query("select io_uuid from io_page where io_title like '%Documentation%' and io_parent_page is not null").collect{it.io_uuid}
def parents = Database.query("select * from io_page where is_related_to('io_page_parent_page',:ids)", [ids:docPages])
Query by the relationship UUID instead of the full identifier to retrieve all Showcase Details related to the UI Unit Test Sample Record Showcase:
def showcase = Database.query("select io_uuid from io_showcase where io_name = 'UI Unit Test Sample Record'").get(0)
def relUUID = UUID.fromString('294f54f3-440b-47d6-b81e-4556f959cb64')
def records = Database.query("select * from io_showcase_detail where is_related_to(:relUUID,:id)", [relUUID:relUUID,id:showcase.io_uuid])
Using Operations as SMQL Functions
You can use the operation SMQL function to use operations within queries. This is useful when you need to use an SMQL function that is not available, or that is specific to an application or implementation. To call this function, you must specify the full identifier for the operation along with its optional parameters and their values.
operation('operation_full_identifier', 'parameter1', value1, 'parameter2', value2, ...)
For example, if the my function is not available in SMQL, you can create a custom io_my operation that receives a field full identifier in the field parameter and retrieves the corresponding value from the record and user parameters for the currently logged-in user. You can then use the following code to get all jobs from the same country as the currently logged-in user:
Database.query("select * from svmx_job where svmx_country = operation('io_my', 'field', 'core_user_country')")
For more information: