# VQL

VQL (VARIO Query Language) is a SQL-like language for querying data across the entire VARIO Cloud ERP. It is the essential way to select and read data in apps and via the API. VQL lets you filter, sort, and paginate any entity — articles, accounts, documents, CRM activities, and beyond — with a single statement.

VQL is case-insensitive. All keywords — `SELECT`, `select`, `Select` — work identically.

***

## Statement Structure

```sql
SELECT [DISTINCT] columns
FROM group.template
[JOIN (subquery) AS alias ON (condition)]
[WHERE expression]
[ORDER BY column [ASC|DESC], ...]
[LIMIT n]
[OFFSET n]
```

{% hint style="info" %}
VQL supports `JOIN` but not `LEFT`, `RIGHT`, `INNER`, or `OUTER` variants. There is no `GROUP BY`, `HAVING`, or `UNION`.
{% endhint %}

\## Columns

Select specific fields, use `*` for standard fields, or apply functions:

```sql
SELECT id, name, documentDate FROM document.querySalesDocuments
```

```sql
SELECT * FROM account.query
```

```sql
SELECT count(id) FROM article.query
```

### Column Aliases

Use the `--v:result{}` annotation to control column behavior in the response:

```sql
SELECT
  --v:result{visible=false}
  id,
  --v:result{displayname='name'}
  default_address.name1
FROM account.query
```

**Options:**

| Option        | Type      | Description                                                                                      |
| ------------- | --------- | ------------------------------------------------------------------------------------------------ |
| `displayname` | `string`  | Key name in the returned data objects. Without it, the raw attribute path is used.               |
| `visible`     | `boolean` | Whether the column appears in UI renderings. Set to `false` to fetch data without displaying it. |

| Function       | Description    |
| -------------- | -------------- |
| `COUNT(field)` | Count          |
| `SUM(field)`   | Sum            |
| `AVG(field)`   | Average        |
| `MIN(field)`   | Minimum        |
| `MAX(field)`   | Maximum        |
| `ABS(field)`   | Absolute value |
| `LIST(field)`  | List values    |

Functions can be nested: `COUNT(SUM(field))`.

### Arithmetic

Full arithmetic is supported in `SELECT`, `WHERE`, and `ORDER BY`:

```sql
select (quantity * unitPrice) from statistic.statisticSalesFact
```

Operators: `+`, `-`, `*`, `/`

***

## FROM Clause

Every query targets a **group** and **template** in the format `group.template`:

```sql
SELECT * FROM article.query
```

```sql
SELECT * FROM document.querySalesDocuments
```

```sql
SELECT * FROM account.query
```

The group identifies the entity domain, the template defines which view of that data you query. Available groups and templates can be discovered via the API:

| Endpoint                                              | Returns              |
| ----------------------------------------------------- | -------------------- |
| `GET /cmn/computed-queries`                           | All groups           |
| `GET /cmn/computed-queries/{group}`                   | Templates in a group |
| `GET /cmn/computed-queries/{group}/{template}/fields` | Available fields     |
| `POST /cmn/computed-queries/execute`                  | Execute a VQL query  |

***

## WHERE Clause

### Operators

| Operator                | Example                                      |
| ----------------------- | -------------------------------------------- |
| `=`                     | `WHERE status = 'ACTIVE'`                    |
| `!=`                    | `WHERE status != 'CLOSED'`                   |
| `>` `>=` `<` `<=`       | `WHERE documentDate >= '2024-01-01'`         |
| `LIKE`                  | `WHERE name LIKE '%GmbH%'`                   |
| `NOT LIKE`              | `WHERE name NOT LIKE '%test%'`               |
| `IN`                    | `WHERE id IN ('a1', 'a2', 'a3')`             |
| `NOT IN`                | `WHERE status NOT IN ('DRAFT', 'CANCELLED')` |
| `BETWEEN`               | `WHERE amount BETWEEN 100 AND 500`           |
| `NOTNULL` / `NOT NULL`  | `WHERE externalId NOTNULL`                   |
| `ISNULL` / `IS NULL`    | `WHERE deletedAt ISNULL`                     |
| `EXISTS (subquery)`     | `WHERE EXISTS (SELECT id FROM ...)`          |
| `NOT EXISTS (subquery)` | `WHERE NOT EXISTS (SELECT id FROM ...)`      |
| `IN (subquery)`         | `WHERE id IN (SELECT ... FROM ...)`          |

Combine with `AND`, `OR`, and parentheses `()` for grouping.

### Field-to-Field Comparison

```sql
WHERE modifiedDate > createdDate
```

***

## Special Value Expressions

### Relative Date — rd{}

Filter by dates relative to today. Format: `rd{DAY.MONTH.YEAR}` — each segment accepts `*` (keep current), an absolute value, or a relative offset (`+N` / `-N`).

**Examples:**

```sql
WHERE createdAt >= 'rd{*.-1.*}'
```

```sql
WHERE documentDate < 'rd{-14.*.*}'
```

**Reference:**

| Expression    | Meaning                                          |
| ------------- | ------------------------------------------------ |
| `rd{*.*.*}`   | Today                                            |
| `rd{-14.*.*}` | 14 days ago                                      |
| `rd{*.-1.*}`  | One month ago                                    |
| `rd{*.*.-1}`  | One year ago                                     |
| `rd{1.*.*}`   | First of the current month                       |
| `rd{E.*.*}`   | First day of the current month                   |
| `rd{L.*.*}`   | Last day of the current month                    |
| `rd{EW.*.*}`  | First working day (Mon–Fri) of the current month |
| `rd{LW.*.*}`  | Last working day (Mon–Fri) of the current month  |
| `rd{TODAY}`   | Today (alias)                                    |

Extends `rd{}` with a time component. Format: `rdt{DAY.MONTH.YEAR HOUR:MINUTE}` — the time part supports the same `*` / `+N` / `-N` syntax plus the keywords `start`, `end`, and `now`.

{% hint style="info" %}
`rd{}` can also be used on DateTime fields — the system resolves it to a date and handles the conversion automatically. Use `rdt{}` only when you need explicit control over the time component.
{% endhint %}

**Examples:**

```sql
WHERE startDateTime > 'rdt{-14.*.* start}'
```

```sql
WHERE updatedAt >= 'rdt{*.-1.* 00:00}'
```

**Reference:**

| Expression          | Meaning                          |
| ------------------- | -------------------------------- |
| `rdt{*.*.* now}`    | Current date and time            |
| `rdt{*.*.* start}`  | Start of today (`00:00:00`)      |
| `rdt{*.*.* end}`    | End of today (`23:59:59`)        |
| `rdt{-7.*.* 08:00}` | 7 days ago at 08:00              |
| `rdt{*.*.* *:*}`    | Current date and time (keep all) |

An optional timezone suffix converts the result to UTC: `rdt{*.*.* start;tz=Europe/Berlin}`

### Current User — env{me}

Resolves to the ID of the authenticated user. Use it to scope queries to the current user's data:

```sql
WHERE assignedUser.id = 'env{me}'
```

***

## Custom Fields

Custom Fields (EAV attributes) are accessed via dot notation:

```sql
SELECT id, custom.myapp.externalId
FROM article.query
WHERE custom.myapp.externalId NOTNULL
```

Path pattern: `custom.{groupKey}.{attributeKey}`

***

## Subqueries and JOINs

### Subqueries

As a column (correlated — `^$` references the outer query):

```sql
SELECT (SELECT displayName FROM account.query WHERE id = ^$.addresses.account.id) AS accountName
FROM document.querySalesDocuments
```

As a FROM source:

```sql
SELECT t$.name FROM (SELECT name FROM account.query) AS t
```

In WHERE:

```sql
SELECT * FROM crm.tasks
WHERE NOT EXISTS (
  SELECT id FROM crm.task-activities
  WHERE ^$.id = relations.crmTask.id
    AND startDateTime > 'rd{-14.*.*}'
)
```

`^$` references the parent query. `alias$` references a named subquery or join.

### JOIN

Joins use a subquery as the right-hand side:

```sql
SELECT
  id,
  number,
  docs$.totalNetPrice
FROM account.query
JOIN (
  SELECT sum(totalNetPrice), addresses.account.id, addresses.type
  FROM document.querySalesDocuments
) AS docs ON (
  docs$.addresses.account.id = id
  AND docs$.addresses.type = 'DEFAULT_ADDRESS'
  AND docs$.totalNetPrice > 0
)
WHERE docs$.addresses.account.id = id
LIMIT 1
```

### Complex Example

This query finds all CRM tasks that had no activity in the past 14 days, are not finished, are older than 14 days, are in a specific state, and are assigned to the current user:

```sql
SELECT * FROM crm.tasks
WHERE NOT EXISTS (
  SELECT id FROM crm.task-activities
  WHERE ^$.id = relations.crmTask.id
    AND startDateTime > 'rd{-14.*.*}'
)
AND state.finishState = 'false'
AND createdAt < 'rd{-14.*.*}'
AND state.sortOrder = '55'
AND assignedUser.id = 'env{me}'
AND (assignedGroup.label != 'Group-Name' OR assignedGroup.id ISNULL)
```

***

## App Framework Usage

VQL is the essential mechanism for selecting and reading data from the ERP within apps. Whether you need to look up a single record, fetch a filtered list, or aggregate values — `app.erp.vql()` is how your app queries the ERP:

```javascript
const { data, moreElements, nextOffset } = await app.erp.vql({
  statement: `
    SELECT
      --v:result{displayname='id'}
      id,
      --v:result{displayname='name'}
      default_address.name1
    FROM account.query
    WHERE default_address.country.isoAlpha2 = 'DE'
    ORDER BY default_address.name1 ASC
  `,
  limit: 100,
  offset: 0,
});
```

### Parameters

| Parameter   | Type           | Default | Description        |
| ----------- | -------------- | ------- | ------------------ |
| `statement` | `string`       | —       | The VQL query      |
| `limit`     | `number\|null` | `null`  | Max rows to return |
| `offset`    | `number\|null` | `null`  | Rows to skip       |

### Response

The response body contains:

| Field        | Type    | Description                                           |
| ------------ | ------- | ----------------------------------------------------- |
| `data`       | `array` | Result rows — keys match your `displayname` aliases   |
| `definition` | `array` | Column metadata (data types, visibility, sortability) |

### Pagination

Pagination information is delivered via **response headers**. The framework reads them and exposes them on the response object:

| Response Header         | Framework Field | Type     | Description                                                      |
| ----------------------- | --------------- | -------- | ---------------------------------------------------------------- |
| `x-query-more-elements` | `moreElements`  | `string` | `'true'` if additional rows exist beyond the current page        |
| `x-query-next-offset`   | `nextOffset`    | `string` | Pass this as `offset` in the next request to fetch the next page |

Use `limit` and `offset` to page through results. When `moreElements` is `'true'`, pass `nextOffset` as the `offset` for the next call to continue.

{% hint style="warning" %}
`moreElements` and `nextOffset` are **strings**, not booleans or numbers. Always compare with `=== 'true'` or `=== 'false'`.
{% endhint %}

{% hint style="info" %}
For the full Computed Query API specification, see the [API Docs](https://developer.vario-software.de/api-reference/computed-queries).
{% endhint %}