@domoinc/query

A helper library for generating Domo Apps' REST API queries.

Usage no npm install needed!

<script type="module">
  import domoincQuery from 'https://cdn.skypack.dev/@domoinc/query';
</script>

README

REST QUERY

This is a simple library to create data query URLs for getting Domo data. Use in conjunction with domo.js.

Dependencies

domo.js via Dev Studio CLI

Examples

var datasetAlias = 'sales';

// initialize query
var query = new Query();

// build query
query.select(['col1', 'col2'])
    .groupBy('col1')

// get the query url using the data/v1 endpoint
var queryUrl = query.query(datasetAlias); // data/v1/sales?fields=col1, col2&groupby=col1

// Use domo.get to fetch the data
domo.get(queryUrl).then(function(data){
    //Do something with the data
});

/**
 * Returns the max date
 */
function getMaxDate() {
  var dateGrainsQ = (new Query())
    .select([ 'date' ])
    .orderBy('date', 'descending')
    .limit(1);
  return domo.get(dateGrainsQ.query('base'));
}
///////////////////////////
getMaxDate().then(function (data) {
  console.log(data);
});

/**
 * Return the brands for a given Engagement Segment
 */
function getEngagementSegmentBrands(engagementSegmentValue)
{
  var esBrands = (new Query()).select([
    'engagementSegmentBrandName',
    'engagementSegmentBrandId'
  ])
    .where('segmentValue').equals(engagementSegmentValue)
    .groupBy([
        'engagementSegmentBrandName'
      , 'engagementSegmentBrandId'
    ]);

  return domo.get(esBrands.query('base'));
}
///////////////////////////
getEngagementSegmentBrands('Loyals').then(function(data){
  console.log(data);
});

API

Building a Query

This section will explain the options available to you for building queries. Here is an example of a complex query.

// initialize query
var query = new Query();

// define
query.select(['col1', 'col2', 'col3', 'col4', 'col5'])
  .where('col1').greaterThan(2)
  .groupBy('col2')
  .dateGrain('col4', 'day')
  .periodToDate('col5', 'week')
  .orderBy('col3', 'desc')
  .limit(10)
  .offset(2);

Selecting Columns

Use select to specify the column names for the columns you want returned.

Column names are the alias names specified in the manifest.json file.

// use an array
query.select(['col1','col2', 'col3']);

// or use multiple parameters
query.select('col1', 'col2', 'col3');

Data Filtering

Filter data by using the where(columName) function followed by the desired filter function.

query.select(['col1','col2', 'col3'])
  .where('col1').lessThan(10)
  .where('col2').contains('foobar');
Filter Functions
// Less than
.lt(value)
.lessThan(value)

// Less than or equal
.lte(value)
.lessThanOrEqual(value)

// Greater than
.gt(value)
.greaterThan(value)

// Greater than or equal
.gte(value)
.greaterThanOrEqual(value)

// Equals
.equals(value)

// Not equals
.notEquals(value)

// Between
.between(start, end)

// Like: The "%" sign is used to define wildcards. Example: '%s' includes values ending in the letter "s"
.like(value)

// Not Like: The "%" sign is used to define wildcards. Example: '%s' excludes values ending in the letter "s"
.notLike(value)

// Contains: same as .like('%'+value+'%')
.contains(value)

// Does not contain: same as .notLike('%'+value+'%)
.notContains(value)

// In: values is an array
.in(values)

// No in: values is an array
.notIn(values)

Date Filtering

The lt, lte, gt, gte filter function will filter dates when used on a column that is a date.

query.select(['date', 'amount'])
  .where('date').greaterThenOrEqual('2014-01-01T00:00:00')
  .where('date').lessThan('2014-08-01T00:00:00');

Date Range Filtering

Columns that are dates can be filtered using specific ranges: currentPeriod, previousPeriod, rollingPeriod, periodToDate.

NOTE: Only 1 date range filter may be used in a query.

Valid interval options for date range filter functions are

'day'
'week'
'month'
'quarter'
'year'
Current Period

NOT SUPPORTED YET: Data for this year, this month, etc can be requested by using the currentPeriod function. This is how you would get data from last year when the date column is named salesdate.

query.select('salesdate')
  .currentPeriod('salesdate', 'year');
Previous Period

Data for last year, last month, etc can be requested by using the previousPeriod function. This is how you would get data from last year when the date column is named salesdate.

query.select('salesdate')
  .previousPeriod('salesdate', 'year');
Rolling Period

A rolling period can be requested by using the rollingPeriod function. For example, this is how you would get all data from the last 6 months when your date column is named salesdate.

query.select('salesdate')
  .rollingPeriod('salesdate', 'month', 6);
Period to Date

Period-to-date filtering is done using the periodToDate function. For example, this is how you would get year to date data when the date column is named salesdate

query.select('salesdate')
  .periodToDate('sales date', 'year');

Group By

Data can be transformed to a group-by operation using the groupBy function. Aggregations for columns can be specified in an object where the key is the column name, and the value is 'count', 'sum', 'avg', 'min', 'max', or 'unique'. By default, columns are counted.

query.select('color', 'shape', 'quantity')
  .groupBy('color', {'shape':'count', 'quantity':'sum'});

The grouping above would behave like so:

color,  shape,    quantity
red,    square,   3
green,  square,   14
blue,   square,   4
purple, square,   9
orange, circle,   3
red,    circle,   14
green,  circle,   4
blue,   circle,   9
purple, triangle, 3
orange, triangle, 14
red,    triangle, 4
green,  triangle, 9
blue,   square,   3
purple, square,   14
orange, square,   4
red,    square,   9

           |
           V

color,  shape,    quantity
red,    4,        30
orange, 3,        21
purple, 3,        26
green,  3,        27
blue,   3,        16

Date Grain

Data can be "grained" by date by using the dateGrain function. This is a special type of "group by".

NOTE: Only 1 column may be date grained in a query.

query.select('salesdate')
  .dateGrain('salesdate', 'month');

Valid intervals are

'day'
'week'
'month'
'quarter'
'year'

This query would group together all data in the data source by month. Each row in the returned data would represent a summary of that data for the month." Like groupBy column aggregations may be specified.

query.select('salesdate', 'sales')
  .dateGrain('salesdate', 'month', {'sales':'sum'})

Aggregation

You can use data aggregations to

  1. Consolidate all rows of a column into a single vale.
  2. Specify the aggregation type for date grain and group by queries.

This is done with the aggregate function. For example, to specify the aggregations for fields salesTotal and salesAmount as sum and average respectively:

query.select('salesTotal', 'salesAmount')
  .aggregate('salesTotal', 'sum')
  .aggregate('salesAmount', 'avg');
  
  //or
  
query.select('salesTotal', 'salesAmount')
  .aggregate({'salesTotal':'sum', 'salesAmount':'avg'})

Order By

Rows can be ordered by any column in 'ascending' or 'descending' order using the orderBy function.

Valid orderings

'asc'
'ascending'
'desc'
'descending'

query.select('salesAmount', 'salesRepName')
  .orderBy('salesAmount', 'ascending')
  .orderBy('salesRepName', 'descending');

Limit

For improved performance and latency, you can paginate data using limit and offset.
Use limit(10) to only receive the first 10 rows.

query.select('salesAmount', 'salesRepName');
  .limit(10)

Offset

To offset the data you get by a certain number, use offset. For example, you could request rows 11-20 like this.

query.select('salesAmount', 'salesRepName')
  .limit(10)
  .offset(10);
  //.skip(10) <- Alternate syntax

Fiscal Calendar

You can specify to use the instances fiscal calendar for date-related operations such as previousPeriod or dateGrain with useFiscalCalendar(true). The standard calendar is used by default.

query.select('salesAmount', 'salesRepName')
  .useFiscalCalendar(true);

Using Functions

Functions can be used in place of literals. These functions will not be evaluated until query() is called. This is beneficial if you want to set up a query once and then modify the variables used in the function closures.

var reusableQuery = new Query();
var limit = 10;

reusableQuery.select('col1')
  .where('col1').greaterThan(1)
  .limit(function () {return limit;})
  .offset(10)
  .query('alias'); 
//This will query with a limit of 10.
.... 

limit = 12;
reusableQuery.query('alias');
//This will query with a limit of 12.

Contribute

We'd love feeback. Submit issues and pull request.

  • write tests
  • mimic the "code style"
  • write good commit messages