@domoinc/query

REST QUERY

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

Dependencies

Install domo.js. This is preinstalled on all DDX Bricks

Examples

const datasetAlias = 'sales';

// build query and fetch data
const response = await new Query()
  .select(['col1', 'col2'])
  .groupBy('col1')
  .fetch<{col1: string; col2: number}>(datasetAlias);

// Do something with the response data
...

/**
 * Returns the max date
 */
async function getMaxDate() {
  return new Query()
    .select(['date'])
    .orderBy('date', 'descending')
    .limit(1)
    .fetch<{date: string}>('datasetalias');
}

...

console.log(await getMaxDate());

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

  return domo.get(esBrands.query('datasetalias'));
}

...

getEngagementSegmentBrands('Loyals').then((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.

import { default as Query, DateGrain } from '@domoinc/query';

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

// define
query
  .select(['col1', 'col2', 'col3', 'col4', 'col5'])
  .where('col1')
  .greaterThan(2)
  .groupBy('col2')
  .dateGrain('col4', DateGrain.DAY)
  .periodToDate('col5', DateGrain.WEEK)
  .orderBy('col3', OrderByDirection.DESCENDING)
  .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.

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)

// Contains
.contains(value)

// Does not contain
.notContains(value)

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

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

Date Filtering

The lt, lte, gt, gte, between filter functions will filter dates when used on a column that is a date. Other functions will treat the date as a string.

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

Date Range Filtering

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

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

A DateGrain enum and a RollingDateGrain enum are provided with the valid interval options. These are:

// date grain
'day';
'week';
'month';
'quarter';
'year';

// rolling date grain
'days';
'weeks';
'months';
'quarters';
'years';

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', DateGrain.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', RollingDateGrain.MONTHS, 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', DateGrain.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 an Aggregation (i.e. 'count', 'sum', 'avg', 'min', 'max', or 'unique'). By default, columns are counted.

query.select(['color', 'shape', 'quantity']).groupBy('color').groupBy({
  shape: Aggregation.COUNT,
  quantity: Aggregation.SUM,
});

The grouping above would make this data:

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

Become aggregated like so:

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.

import { default as Query, DateGrain } from '@domoinc/query';

query.select(['salesdate']).dateGrain('salesdate', DateGrain.MONTH);

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: Aggregation.SUM });

Aggregation

You can use data aggregations to

1. Consolidate all rows of a column into a single value.
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: Aggregate.SUM, salesAmount: Aggregate.AVG });

Order By

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

An enum (OrderByDirection) is provided to define the order by direction. Valid orderings:

'ascending';
'descending';

query
  .select(['salesAmount', 'salesRepName'])
  .orderBy('salesAmount', OrderByDirection.ASCENDING)
  .orderBy('salesRepName', OrderByDirection.DESCENDING);

Limit

For improved performance and latency, you can paginate data using limit and offset.

// 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.

// you could request rows 11-20 like this.
query.select(['salesAmount', 'salesRepName']).limit(10).offset(10);

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();

Beast Modes

You can enable beast modes in the query by calling the useBeastMode. true

query.select(['salesAmount', 'salesRepName']).useBeastMode();