Scripting with the N/query Module

The N/query module lets you create and run queries using the SuiteAnalytics Workbook query engine. Before you start creating your queries, you should be familiar with the module objects and how to use them, as well as some of the terminology used in the N/query module. You can also take a look at a script walkthrough that explains how to create queries using different approaches.

• N/query Module Objects

• N/query Module Terminology

N/query Module Objects

The N/query module includes the following objects:

• Query and Component Objects

• Condition Object

• RelativeDate Object

• Column Object

• Sort Object

• ResultSet and Result Objects

• Page, PagedData, and PageRange Objects

Query and Component Objects

The query.Query object and the query.Component object are the primary building blocks for a query created with the N/query module. Each query creates one query.Query object and one or more query.Component objects. The query.Query object encapsulates the query definition, and the query.Component object encapsulates one component of the query definition.

To create a query with the N/query module:

1. Use the query.create(options) method to create your initial query definition (the query.Query object). The initial query definition uses one search type. For available search types, see query.Type.

2. After you create the initial query definition, use Query.autoJoin(options), Query.joinFrom(options), or Query.joinTo(options) to create your first join.

3. Use any of the following methods to create subsequent joins:

   • Query.autoJoin(options)

   • Query.joinFrom(options)

   • Query.joinTo(options)

   • Component.autoJoin(options)

   • Component.joinFrom(options)

   • Component.joinTo(options)

The query definition always contains at least one query.Component object. Each new component is created as a child of the previous component, and all components exist as children of the query definition. You can think of a component as a building block; each new component builds on the previous component created. The last component created encapsulates the relationship between it and all of its parent components.

Queries with joins contain multiple components. The query definition contains a child query.Component object for each of the following:

• The initial query definition: The initial query.Component object is called the root component. It encapsulates the initial search type passed to query.create(options). The root component is automatically created with the initial query definition and is a child to the query.Query object. The Query.root property contains a reference to the root component.

• The first join: The second query.Component object is created with Query.autoJoin(options), Query.joinFrom(options), or Query.joinTo(options). It encapsulates the relationship between the initial query definition and the second search type. This relationship is determined by the join ID passed to these methods, as well as whether Query.joinFrom(options) or Query.joinTo(options) was used to create an explicit directional join. The second query.Component object is a child to the root component.

• Each subsequent join: The third query.Component object is created with Component.autoJoin(options), Component.joinFrom(options), or Component.joinTo(options). All subsequent joins are also created using these methods. Each of these query.Component objects encapsulates the relationship between all previous search types and the new search type. This relationship is determined by the join ID passed to these methods, as well as whether Component.joinFrom(options) or Component.joinTo(options) was used to create an explicit directional join.

Condition Object

A condition narrows the query results. The query.Condition object performs the same function as the search.Filter object in the N/search Module. The primary difference is that query.Condition objects can contain other query.Condition objects.

To create conditions:

• Use Query.createCondition(options) to create conditions for the initial query definition created with query.create(options).

• Use Component.createCondition(options) to create conditions for the join relationships created with Query.autoJoin(options), Query.joinFrom(options)/Query.joinTo(options), Component.autoJoin(options), or Component.joinFrom(options)/Component.joinTo(options).

• If you have multiple conditions, use Query.and(conditions), Query.or(conditions), and Query.not(condition) to create a new nested condition.

• If you want to use a formula to define your conditions, assign the formula to Condition.formula.

• Assign your simple or nested conditions as array values to Query.condition.

RelativeDate Object

The query.RelativeDate object represents a date that is relative to the current date. You can use relative dates when you create query conditions.

To create relative dates:

• Use query.createRelativeDate(options) to create a query.RelativeDate object. When you call query.createRelativeDate(options), use the values in the query.DateId enum to specify a date that is relative to the current date.

• Use Query.createCondition(options) or Component.createCondition(options) to create a condition using the query.RelativeDate object. Alternatively, you can create a condition using values in the query.RelativeDateRange enum.

• If you have multiple conditions, use Query.and(conditions), Query.or(conditions), and Query.not(condition) to create a new nested condition.

• Assign your simple or nested conditions as array values to Query.condition.

Column Object

The query.Column object is the equivalent of the search.Column object in the N/search Module. The query.Column object describes the field types (columns) that are displayed from the query results.

To create columns:

• Use Query.createColumn(options) to create a column on the initial query definition created with query.create(options).

• Use Component.createColumn(options) to create a column on a join relationship created with Query.autoJoin(options), Query.joinFrom(options)/Query.joinTo(options), Component.autoJoin(options), or Component.joinFrom(options)/Component.joinTo(options).

• If you want to use a formula to define your columns, assign the formula to Column.formula.

• Assign all created columns as array values to Query.columns.

Sort Object

The query.Sort object describes how query results are sorted (for example, ascending or descending, case sensitive or case insensitive, and so on).

To create a sort:

• Use Query.createSort(options) to create a sort on the initial query definition created with query.create(options).

• Use Component.createSort(options) to create a sort based on a join relationship created with Query.autoJoin(options), Query.joinFrom(options)/Query.joinTo(options), Component.autoJoin(options), or Component.joinFrom(options)/Component.joinTo(options).

• Assign all created sorts as array values to Query.sort.

ResultSet and Result Objects

When you are ready to execute your query, call Query.run(). This method returns a query.ResultSet object, which encapsulates the metadata for the set of results returned by the query.

To access your query results, iterate through the ResultSet.results array. Each member of the ResultSet.results array is a query.Result object. The query.Result object encapsulates a single row of the result set.

Page, PagedData, and PageRange Objects

You also can execute your query by calling Query.runPaged(). This method returns a query.PagedData object, which encapsulates a set of paged query results.

To access your query results, iterate through the paged query results using PagedData.iterator(). You can access each page of the query results, which are represented by query.Page objects. The query.PageRange object encapsulates the range of query results for a page.

N/query Module Terminology

Term	Definition	For More Information
Aggregate function	An aggregate function performs a calculation on a column of values and returns a single value. You can add aggregate functions to conditions and query results columns.	query.Aggregate Component.createColumn(options) Component.createCondition(options) Query.createColumn(options) Query.createCondition(options)
Column	A column describes the field types (columns) that are displayed from the query results. A column is also known as a query results column.	query.Column
Component	When you script queries with the N/query module, your query is made up of one or more components, which are represented as query.Component objects. You can think of a component as a building block; each new component builds on the previous component created. The first component created represents the initial search type and is a child of query.Query. Each subsequent component created is a child of the previous component. The last component created encapsulates the join relationship between it and all of its parent components. A query always contains at least one component: the root component. When you create the initial query definition using query.create(options), the root component is created automatically. Queries with joins contain multiple components. A new component is created each time you create a join using one of the following methods: Query.autoJoin(options), Query.joinFrom(options), or Query.joinTo(options) Component.autoJoin(options), Component.joinFrom(options), or Component.joinTo(options)	query.Component
Condition	A condition narrows the query results.	query.Condition
Formula	Formulas can be used to create conditions and columns.	Formulas in Search SQL Expressions
Group	You can summarize your query results into unique groups of column values.	Column.groupBy
Join	A join lets you create a query based on a field type that is shared between two record types. You can use Query.autoJoin(options) and Component.autoJoin(options) to create a join relationship automatically based on a field that you specify. You can use Query.joinFrom(options)/Query.joinTo(options) and Component.joinFrom(options)/Component.joinTo(options) to create explicit directional join relationships from one component to another.	query.Query query.Component
Page	A page represents one page from a set of paged query results. When you create a query with the N/query module, you can return the results as one result set or a set of paged results.	Query.runPaged() query.PagedData query.PageRange query.Page
Paged data	Paged data represents a set of paged query results.	Query.runPaged() query.PagedData query.PageRange query.Page
Page range	A page range is a set of pages from a set of paged query results.	Query.runPaged() query.PagedData query.PageRange query.Page
Relative date	A relative date is a date that is relative to the current date. You can use relative dates when you create query conditions.	query.RelativeDate query.createRelativeDate(options) query.RelativeDateRange
Result	A result is a single row from a result set.	Query.run() query.ResultSet query.Result
Result set	A result set is a set of query results.	Query.run() query.ResultSet query.Result
Query definition	The query definition is the initial search type you define, plus any subsequent joins you define. The initial query definition is created with query.create(options).	query.Query
Query type	The query type is the initial query type of your query definition. It represents the record type you want to query for. It is set with the query.Type enum during the execution of query.create(options). For example, if you want to query for customer records, specify query.Type.CUSTOMER as the query type when you call query.create(options).	query.Query query.Type
Sort	A sort is placed on a query results column to describe how the query results are sorted (for example, ascending or descending, case sensitive or case insensitive, and so on).	query.Sort Query.createSort(options) Component.createSort(options)

General Notices