yarn add @domoinc/query

import Query from '@domoinc/query';

<script src="https://cdn.jsdelivr.net/npm/@domoinc/query@3.0.0/dist/main.min.js"></script>

// Query is available globally after CDN script loads
const data = await new Query()
  .select(['region', 'sales'])
  .fetch('sales-dataset');

// Simple query
const data = await new Query()
  .select(['region', 'sales', 'date'])
  .fetch('sales-dataset');

// With filtering
const filtered = await new Query()
  .select(['region', 'product', 'sales'])
  .where('sales').greaterThan(1000)
  .where('region').in(['North', 'South'])
  .fetch('sales-dataset');

// With grouping and aggregation
const summary = await new Query()
  .select(['region', 'sales', 'quantity'])
  .groupBy('region')
  .groupBy({ sales: 'sum', quantity: 'avg' })
  .orderBy('sales', 'descending')
  .limit(10)
  .fetch('sales-dataset');

// Select specific columns
new Query().select(['col1', 'col2', 'col3'])

// Select all (omit select)
new Query().fetch('dataset')

// Comparison filters
.where('amount').lessThan(100)           // .lt(100)
.where('amount').lessThanOrEqual(100)    // .lte(100)
.where('amount').greaterThan(100)        // .gt(100)
.where('amount').greaterThanOrEqual(100) // .gte(100)
.where('amount').equals(100)
.where('amount').notEquals(100)
.where('amount').between(100, 500)

// String filters
.where('name').contains('test')
.where('name').notContains('test')

// List filters
.where('category').in(['A', 'B', 'C'])
.where('status').notIn(['deleted', 'archived'])

// Multiple conditions (AND)
.where('amount').greaterThan(100)
.where('status').equals('active')
.where('region').in(['North', 'South'])

// Group by single column
.groupBy('region')

// Group by multiple columns
.groupBy('region')
.groupBy('product')

// Group by column with aggregations (second parameter)
.groupBy('region', { 
  sales: 'sum',
  quantity: 'avg',
  price: 'max',
  orders: 'count',
  skus: 'unique'
})

// Multiple groupBy calls
.groupBy('region')
.groupBy('product', { sales: 'sum', orders: 'count' })

// ✅ CORRECT - Keys match actual field names
// If your dataset has fields: 'Sales_Amount', 'Order_Qty'
.groupBy('region', { 
  Sales_Amount: 'sum',   // Key matches dataset field
  Order_Qty: 'count'     // Key matches dataset field
})

// Results: [{ region: 'North', Sales_Amount: 50000, Order_Qty: 150 }]

// ❌ WRONG - Custom aliases as keys cause [object Object] errors
.groupBy('region', { 
  totalSales: 'sum',     // 'totalSales' is not a field name!
  orderCount: 'count'    // 'orderCount' is not a field name!
})

// ✅ If you need custom names, rename AFTER fetching:
const data = await new Query()
  .groupBy('region', { Sales_Amount: 'sum' })
  .fetch('sales');

const renamed = data.map(row => ({
  region: row.region,
  totalSales: row.Sales_Amount  // Rename here
}));

.orderBy('sales', 'descending')  // or 'desc'
.orderBy('date', 'ascending')    // or 'asc'

// Multiple sort columns
.orderBy('region', 'ascending')
.orderBy('sales', 'descending')

.limit(100)    // Max rows
.offset(50)     // Skip rows (for pagination)

// Pagination example
const page = 2;
const pageSize = 25;
new Query()
  .limit(pageSize)
  .offset((page - 1) * pageSize)
  .fetch('dataset');

// Group by month
.dateGrain('order_date', 'month')

// Group by month with aggregations
// NOTE: Aggregation keys must be actual field names!
.dateGrain('order_date', 'month', { Revenue: 'sum', Order_Count: 'count' })

// Available grains: 'day', 'week', 'month', 'quarter', 'year'
.dateGrain('date', 'day')
.dateGrain('date', 'week')
.dateGrain('date', 'month')
.dateGrain('date', 'quarter')
.dateGrain('date', 'year')

// ✅ CORRECT - field names as keys
.dateGrain('order_date', 'month', { Sales_Amount: 'sum' })

// ❌ WRONG - custom aliases cause errors
.dateGrain('order_date', 'month', { totalSales: 'sum' })

// Year to date
.periodToDate('date', 'year')

// Month to date
.periodToDate('date', 'month')

// Quarter to date
.periodToDate('date', 'quarter')

// Full example
const ytdSales = await new Query()
  .select(['date', 'sales'])
  .periodToDate('date', 'year')
  .groupBy({ sales: 'sum' })
  .fetch('sales');

// Last year
.previousPeriod('date', 'year')

// Last month
.previousPeriod('date', 'month')

// Last quarter
.previousPeriod('date', 'quarter')

// Last 30 days
.rollingPeriod('date', 'days', 30)

// Last 12 weeks
.rollingPeriod('date', 'weeks', 12)

// Last 6 months
.rollingPeriod('date', 'months', 6)

// Last 4 quarters
.rollingPeriod('date', 'quarters', 4)

// Last 3 years
.rollingPeriod('date', 'years', 3)

// Use fiscal calendar
.useFiscalCalendar(true)

// Enable beast modes (calculated fields)
.useBeastModes()

// ⚠️ WARNING: .aggregate() does NOT work in practice
// It causes error: "DA0057: An alias list was provided but it could not be parsed"
// Use .groupBy() with a grouping column instead, or .select() + client-side aggregation
// ❌ .aggregate({ total: 'sum', average: 'avg' })  // DOES NOT WORK

// Sales dashboard query
const salesByRegion = await new Query()
  .select(['region', 'product_category', 'sales', 'quantity', 'date'])
  .where('sales').greaterThan(0)
  .where('date').greaterThanOrEqual('2024-01-01')
  .dateGrain('date', 'month')
  .groupBy('region')
  .groupBy('product_category')
  .groupBy({ sales: 'sum', quantity: 'sum', orders: 'count' })
  .orderBy('sales', 'descending')
  .limit(100)
  .fetch('sales-dataset');

// YoY comparison
const thisYear = await new Query()
  .select(['month', 'revenue'])
  .periodToDate('date', 'year')
  .dateGrain('date', 'month', { revenue: 'sum' })
  .fetch('revenue');

const lastYear = await new Query()
  .select(['month', 'revenue'])
  .previousPeriod('date', 'year')
  .dateGrain('date', 'month', { revenue: 'sum' })
  .fetch('revenue');

// Trend analysis - last 90 days
const trend = await new Query()
  .select(['date', 'sales', 'orders'])
  .rollingPeriod('date', 'days', 90)
  .dateGrain('date', 'day', { sales: 'sum', orders: 'count' })
  .orderBy('date', 'ascending')
  .fetch('sales');