Understanding N/QUERY in NetSuite

The N/QUERY module in NetSuite SuiteScript is used to run SQL-like queries on NetSuite data. It provides an interface for you to execute complex queries similar to SQL, which can be useful when you need more flexibility or control than what’s offered by the N/SEARCH module.

1. Key Features of N/QUERY

SQL-like Syntax: N/QUERY allows you to write queries using SQL-like syntax, making it easier for developers who are familiar with SQL to get started.
Flexibility: Unlike N/SEARCH, which is restricted to NetSuite’s predefined search structure, N/QUERY gives you the flexibility to write custom queries that join multiple tables, use complex filters, and select specific columns.
Efficiency: It can be more efficient than using N/SEARCH for complex queries, particularly when you need to handle a large dataset or perform advanced filtering.

2. Common Methods in N/QUERY

create(options): Creates a new query object. You can define the record type, filters, and columns to retrieve.
run(): Executes the query and returns a result set.
runPaged(options): Similar to run(), but it returns the results in pages. This is useful for large data sets where you don’t want to load everything at once.
select(): Specifies which fields to retrieve (columns in SQL terms).
from(): Defines which record (or table) you want to query.
where(): Defines the filtering criteria (like SQL’s WHERE clause).
join(): Allows you to join different records (tables) together in a query.

define(['N/query'], 
function(query) { 
function runQuery() { 
var resultSet = query.create({ 
query: "SELECT entityid, email FROM customer WHERE status = 'ACTIVE'" }).run(); 
resultSet.each(function(result) { 
var entityId = result.getValue({ name: 'entityid' }); 
var email = result.getValue({ name: 'email' }); 
log.debug('Customer Info', 'ID: ' + entityId + ', Email: ' + email); 
return true; 
}); 
} 
return { execute: runQuery }; 
});

Handling Large Result Sets with `runPaged()`

If you’re working with a large number of records, you can use runPaged() to split the results into manageable pages.

define(['N/query'], function(query) {
    function runPagedQuery() {
        var pagedData = query.create({
            query: "SELECT entityid, email FROM customer WHERE status = 'ACTIVE'"
        }).runPaged({
            pageSize: 1000
        });


        pagedData.pageRanges.forEach(function(pageRange) {
            var page = pagedData.fetch({ index: pageRange.index });
            page.data.forEach(function(result) {
                var entityId = result.getValue({ name: 'entityid' });
                var email = result.getValue({ name: 'email' });
                log.debug('Customer Info', 'ID: ' + entityId + ', Email: ' + email);
            });
        });
    }
    return {
        execute: runPagedQuery
    };
});

Joining Multiple Records

With N/QUERY, you can join multiple record types (like SQL joins). Here’s an example where we join the sales order record with the customer record:

define(['N/query'], function(query) {
    function runJoinQuery() {
        var resultSet = query.create({
            query: "SELECT so.id, so.trandate, c.entityid FROM salesorder so JOIN customer c ON so.entity = c.id WHERE so.status = 'Pending'"
        }).run();
        
        resultSet.each(function(result) {
            var orderId = result.getValue({ name: 'id' });
            var orderDate = result.getValue({ name: 'trandate' });
            var customerId = result.getValue({ name: 'entityid' });
            log.debug('Order Info', 'Order ID: ' + orderId + ', Date: ' + orderDate + ', Customer ID: ' + customerId);
            return true;
        });
    }
    return {
        execute: runJoinQuery
    };
});

Performance Considerations

Paginated Queries: Use runPaged() when dealing with large result sets to avoid memory issues.
Efficient Filters: Always apply filters using the WHERE clause to limit the amount of data returned, reducing the load on your script.
Limit the Number of Columns: Only select the columns that are necessary to minimize the amount of data processed.

7. Benefits of Using N/QUERY

Custom Queries: You can write highly customized queries, which are especially useful for reporting or data extraction tasks.
SQL Familiarity: Developers who are comfortable with SQL can leverage their skills to create more complex queries and operations.

8. Limitations of N/QUERY

Performance: When used incorrectly (e.g., without proper filters or in large datasets), N/QUERY can have performance impacts.
Record Limitations: Not all NetSuite records are available through N/QUERY, as it relies on a simplified query language.