Query Builder

Introduction to Query Builder

The Query Builder is a core component of the semantic layer, providing a flexible and powerful interface for constructing complex queries based on your data models. It abstracts the intricacies of SQL, allowing you to focus on the business logic of your data analysis.

Creating a Query Builder

To create a Query Builder, you need a defined repository and a specified dialect. Here's a basic example:

import * as semanticLayer from "@verybigthings/semantic-layer";

// Assume models (customersModel, invoicesModel, invoiceLinesModel) are defined here

const repository = semanticLayer
  .repository()
  .withModel(customersModel)
  .withModel(invoicesModel)
  .withModel(invoiceLinesModel);
// Assume joins are defined here

const queryBuilder = repository.build("postgresql");

Constructing Queries

The Query Builder generates SQL from semantic layer queries. A basic query structure looks like this:

const query = queryBuilder.buildQuery({
  members: ["customers.customer_id", "invoices.total"],
  order: { "customers.customer_id": "asc" },
  limit: 10,
});

This query selects customer_id and total from the customers and invoices models, orders by customer_id ascending, and limits to 10 results.

Query Structure and Type Inference

The query object returned by buildQuery has the following structure:

{
  sql: string;
  bindings: unknown[];
}

To infer the return type of your query, use the InferSqlQueryResultType type:

type QueryResult = semanticLayer.InferSqlQueryResultType<typeof query>;

For inferring the return type from a query object without calling buildQuery, use QueryReturnType:

const queryObject = {
  members: ["customers.customer_id", "customers.first_name"],
  order: [{ member: "customers.customer_id", direction: "asc" }],
  filters: [
    { operator: "equals", member: "customers.customer_id", value: [1] },
  ],
  limit: 10,
} satisfies semanticLayer.QueryBuilderQuery<typeof queryBuilder>;

type QueryResult = semanticLayer.InferSqlQueryResultTypeFromQuery<
  typeof queryBuilder,
  typeof queryObject
>;

Query Properties

The Query Builder supports several properties to define your query:

members: An array of strings representing the dimensions and metrics to include.
order: Specifies the ordering of results.
limit: Sets the maximum number of results to return.
offset: Determines the number of results to skip.
filters: An array of filter objects to apply to the query.

Filter Types

The Query Builder supports a wide range of filter operators, including:

Logical operators: and, or
Equality operators: equals, notEquals, in, notIn
Null checks: set, notSet
String operators: contains, notContains, startsWith, notStartsWith, endsWith, notEndsWith
Numeric comparisons: gt, gte, lt, lte
Date range operators: inDateRange, notInDateRange, beforeDate, afterDate
Subquery operators: inQuery, notInQuery

Example of a filter:

{
  operator: "equals",
  member: "albums.name",
  value: ["Thriller"]
}

Filter Evaluation

It's important to note that dimension filters are evaluated before data grouping and aggregation, while metric filters are evaluated after. This means metric and dimension filters cannot be used in the same and or or connective.

Filter Types in Detail

The Query Builder supports a wide range of filter operators to refine your queries. Here's a detailed breakdown of each filter type:

Logical Operators

and: Connects two or more filters with a logical AND.

{
  operator: "and",
  filters: [
    { operator: "equals", member: "albums.name", value: ["Thriller"] },
    { operator: "gt", member: "tracks.duration", value: [300] }
  ]
}

or: Connects two or more filters with a logical OR.

{
  operator: "or",
  filters: [
    { operator: "equals", member: "albums.name", value: ["Thriller"] },
    { operator: "equals", member: "albums.name", value: ["Bad"] }
  ]
}

Equality Operators

equals: Filters values that are equal to the given value.

{
  operator: "equals",
  member: "albums.name",
  value: ["Thriller"]
}

notEquals: Filters values that are not equal to the given value.

{
  operator: "notEquals",
  member: "albums.name",
  value: ["Thriller"]
}

in: Filters values that are in the given array of values.

{
  operator: "in",
  member: "albums.name",
  value: ["Thriller", "Bad", "Dangerous"]
}

notIn: Filters values that are not in the given array of values.

{
  operator: "notIn",
  member: "albums.name",
  value: ["Thriller", "Bad", "Dangerous"]
}

Null Checks

set: Filters values that are set (not null).

{
  operator: "set",
  member: "albums.release_date"
}

notSet: Filters values that are not set (null).

{
  operator: "notSet",
  member: "albums.release_date"
}

String Operators

contains: Filters values that contain the given string.

{
  operator: "contains",
  member: "tracks.name",
  value: ["Love"]
}

notContains: Filters values that do not contain the given string.

{
  operator: "notContains",
  member: "tracks.name",
  value: ["Love"]
}

startsWith: Filters values that start with the given string.

{
  operator: "startsWith",
  member: "tracks.name",
  value: ["The"]
}

notStartsWith: Filters values that do not start with the given string.

{
  operator: "notStartsWith",
  member: "tracks.name",
  value: ["The"]
}

endsWith: Filters values that end with the given string.

{
  operator: "endsWith",
  member: "tracks.name",
  value: ["Song"]
}

notEndsWith: Filters values that do not end with the given string.

{
  operator: "notEndsWith",
  member: "tracks.name",
  value: ["Song"]
}

Numeric Comparisons

gt: Filters values that are greater than the given value.

{
  operator: "gt",
  member: "tracks.duration",
  value: [300]
}

gte: Filters values that are greater than or equal to the given value.

{
  operator: "gte",
  member: "tracks.duration",
  value: [300]
}

lt: Filters values that are less than the given value.

{
  operator: "lt",
  member: "tracks.duration",
  value: [300]
}

lte: Filters values that are less than or equal to the given value.

{
  operator: "lte",
  member: "tracks.duration",
  value: [300]
}

Date Range Operators

inDateRange: Filters dates that are in the given range.

{
  operator: "inDateRange",
  member: "albums.release_date",
  value: { startDate: "2022-01-01", endDate: "2022-12-31" }
}

notInDateRange: Filters dates that are not in the given range.

{
  operator: "notInDateRange",
  member: "albums.release_date",
  value: { startDate: "2022-01-01", endDate: "2022-12-31" }
}

beforeDate: Filters dates that are before the given date.

{
  operator: "beforeDate",
  member: "albums.release_date",
  value: "2022-01-01"
}

afterDate: Filters dates that are after the given date.

{
  operator: "afterDate",
  member: "albums.release_date",
  value: "2022-01-01"
}

Subquery Operators

inQuery: Filters values that are in the result of the given query.

{
  operator: "inQuery",
  member: "customer.customer_id",
  value: {
    members: ["customer.customer_id"],
    filters: [
      {
        operator: "equals",
        member: "invoices.invoice_id",
        value: ["1"]
      }
    ]
  }
}

notInQuery: Filters values that are not in the result of the given query.

{
  operator: "notInQuery",
  member: "customer.customer_id",
  value: {
    members: ["customer.customer_id"],
    filters: [
      {
        operator: "equals",
        member: "invoices.invoice_id",
        value: ["1"]
      }
    ]
  }
}

Important Notes on Filter Usage

For operators that accept multiple values (like in, notIn), the value should be an array.
Date values should always be in ISO 8601 format for consistency.
The inQuery and notInQuery operators accept a full query object as their value, allowing for complex nested queries.
Remember that dimension filters are evaluated before data grouping and aggregation, while metric filters are evaluated after. This means metric and dimension filters cannot be used in the same and or or connective.

By leveraging these filter types effectively, you can create highly specific and powerful queries to extract precisely the data you need from your semantic layer.

Query Examples

Querying multiple dimensions:

const query = queryBuilder.buildQuery({
  members: ["albums.name", "customers.full_name"],
  order: { "albums.name": "asc" },
});

Querying a metric sliced across two dimensions:

const query = queryBuilder.buildQuery({
  members: ["customers.name", "genres.name", "tracks.count"],
  order: { "customers.name": "asc" },
});

Filtering results by another query's result:

const query = queryBuilder.buildQuery({
  members: ["customer.customer_id", "invoices.invoice_count"],
  filters: [
    {
      operator: "inQuery",
      member: "customer.customer_id",
      value: {
        members: ["customer.customer_id"],
        filters: [
          {
            operator: "equals",
            member: "invoices.invoice_id",
            value: ["1"],
          },
        ],
      },
    },
  ],
});

Best Practices

Use meaningful names for your dimensions and metrics to make queries more readable.
Leverage type inference to catch potential errors early in development.
Be mindful of the difference between dimension and metric filters when constructing complex queries.
Use the appropriate filter operators to optimize query performance.
When working with date ranges, ensure your dates are in ISO 8601 format for consistency.

By mastering the Query Builder, you can efficiently extract valuable insights from your data while maintaining a clean separation between your business logic and the underlying SQL complexity.

Introduction to Query Builder​

Creating a Query Builder​

Constructing Queries​

Query Structure and Type Inference​

Query Properties​

Filter Types​

Filter Evaluation​

Filter Types in Detail​

Logical Operators​

Equality Operators​

Null Checks​

String Operators​

Numeric Comparisons​

Date Range Operators​

Subquery Operators​

Important Notes on Filter Usage​

Query Examples​

Best Practices​