SuiteQL in the N/query Module

Converting an Existing Query to SuiteQL

If you already have a query.Query object in your script (one that you created using query.create(options) or loaded using query.load(options)), you can convert the query to its SuiteQL representation. The Query.toSuiteQL() method converts a query.Query object to a query.SuiteQL object. The resulting query.SuiteQL object represents the same query as the original query.Query object and, when run, returns the same query results.

A query.SuiteQL object includes the following properties:

• SuiteQL.columns — The result columns to be returned from the query. This property is an array of query.Column objects. The value of this property is the same as the value of the Query.columns property in the original query.Query object.

• SuiteQL.params — The parameters for the query. In SuiteQL, query conditions are represented using the WHERE clause and a set of parameters.

• SuiteQL.query — The string representation of the SuiteQL query. This string can contain SQL clauses, record or table names, field names, operators, and more.

• SuiteQL.type — The type of the query. This property uses values from the query.Type enum. The value of this property is the same as the value of the Query.type property in the original query.Query object.

To run the SuiteQL query, use one of the following methods:

• Use SuiteQL.run() to run the query as a non-paged query. This method returns the results as a query.ResultSet object.

• Use SuiteQL.runPaged(options) to run the query as a paged query. This method returns the results as a query.PagedData object. The default page size is 50 results per page. The minimum page size is 5 results per page, and the maximum page size is 1000 results per page.

The following example shows you how to create a query as a query.Query object, convert the query to SuiteQL, and run the resulting SuiteQL query as a non-paged query:

            var myCustomerQuery = query.create({
    type: query.Type.CUSTOMER
});

myCustomerQuery.columns = [
    myCustomerQuery.createColumn({
        fieldId: 'entityid'
    }),
    myCustomerQuery.createColumn({
        fieldId: 'email'
    })
];

myCustomerQuery.condition = myCustomerQuery.createCondition({
    fieldId: 'isperson',
    operator: query.Operator.IS,
    values: [true]
});

var mySQLCustomerQuery = myCustomerQuery.toSuiteQL();

var results = mySQLCustomerQuery.run(); 

          

In the preceding example, the mySQLCustomerQuery variable contains the resulting query.SuiteQL object after the conversion. In this object, the SuiteQL.query property contains the string representation of the original query. In this example, this property contains the following string:

            SELECT customer.entityid AS entityidRAW /*{entityid#RAW}*/, customer.email AS emailRAW /*{email#RAW}*/ FROM customer WHERE customer.isperson = ? 

          

The SuiteQL.params property contains the parameter value true. When the SuiteQL query runs, the question mark (?) in the query string is replaced with the parameter value (true).

Running an Arbitrary SuiteQL Query

You can create your own SuiteQL queries and run them. The query.runSuiteQL(options) method lets you run an arbitrary SuiteQL query, and it returns query results as a query.ResultSet object. The query.runSuiteQLPaged(options) method lets you run a SuiteQL query as a paged query, and it returns query results as a query.PagedData object.

To specify the SuiteQL query to run, you can provide one of the following to query.runSuiteQL(options) or query.runSuiteQLPaged(options):

• A string representation of the SuiteQL query

                  var results = query.runSuiteQL({
      query: 'SELECT customer.entityid, customer.email FROM customer'
  }); 
  
                

• A query.SuiteQL object

                  // In this example, mySuiteQLCustomerQuery is an existing SuiteQL object
  var results = query.runSuiteQL(mySuiteQLCustomerQuery); 
  
                

• A JavaScript Object that contains a query property and, optionally, a params property:

                  var results = query.runSuiteQL({
      query: 'SELECT customer.entityid, customer.email FROM customer WHERE customer.isperson = ?',
      params: [true]
  });