# Gelly 

Gelly is Gadget's data access language, powering advanced features in Gadget like [filtered model permissions](https://docs.gadget.dev/guides/access-control), [computed fields](https://docs.gadget.dev/guides/data-access/computed-fields), and [computed views](https://docs.gadget.dev/guides/data-access/computed-views).

Gelly offloads a lot of the traditional performance burdens of managing complex queries (via SQL) off of your plate and onto ours.

This guide complements the [Gelly reference](https://docs.gadget.dev/reference/gelly) which provides specific details about Gelly types, operators, and functions.

## Query structure 

A Gelly _query_ is a declarative way to specify the data you want to retrieve from your models.

Similar to GraphQL queries or SQL `SELECT` statements, Gelly queries allow you to specify exactly what data you want, and returns it in a predictable shape.

Queries can specify fields on models and built-in operators can be used to transform and aggregate the returned data.

### Starting a query 

Currently, Gelly can be executed in one of two ways: [computed fields](https://docs.gadget.dev/guides/data-access/computed-fields) or [computed views](https://docs.gadget.dev/guides/data-access/computed-views).

When creating a Gelly query for computed fields, start the query with `field on <model>`. When working with `computed views`, you start the query with `view`.

Most examples in this guide will use the `view` syntax. You can learn more about [defining computed fields here](https://docs.gadget.dev/guides/data-access/computed-fields).

### Selecting fields 

Queries can select fields from the available models by listing each field between curly brackets, also known as a _selection set_.

For example, this query selects the `title` field from a `blog` model:

```gelly
// in select the title field from a blog model
view {
  blogs {
    title
  }
}
```

This on it's own is not exactly useful, as we already have access to the `title` field through `blog`. However, we can create more complex expressions here, which can extract more meaningful information.

For example, from the `customer` model, we could save the `fullName`:

```gelly
// in concatenate the customer's first and last name
view {
  customers {
    fullName: concat([firstName, " ", lastName])
  }
}
```

Fields in a selection must be separated by at least one whitespace character like a space, a tab, or a newline. Model fields are selected using the API Identifier for that field.

The fields you select are entirely up to you: none are mandatory and none require other fields to be selected in order to be selected themselves.

### Selecting relationships 

Gelly queries can also be made across relationships between models. To select related data, add an inner set of curly brackets, or a nested selection set, to a root selection.

For example, a `blog` model has many `posts`. If the `post` model has `title` and `id` fields, which can be read with the following Gelly query:

```gelly
// in select the id and title field from a post model in a blog
view {
  blogs {
    name
    posts {
      id
      title
    }
  }
}
```

This query will return an array of `blogs`, each with a `posts` array containing the selected fields for each post:

```json
// in the result of selection across a relationship
{
  "blogs": [
    {
      "posts": [
        { "id": 1, "title": "Hello World" },
        { "id": 2, "title": "Thoughts on Software Development" }
      ]
    }
  ]
}
```

Relationships can be navigated through multiple levels too. For instance, if `blog` has many `post` and `post` has many `comments`, then you may access fields of both `post` and `comments` simultaneously.

This query selects the `id` and `title` of the first page of `posts` related to the current `blog`, as well as the `id` and `body` of the first page of comments for each of the posts:

```gelly
// in Gelly can select across multiple nested relationships
view {
  blogs {
    name
    posts {
      title
      comments {
        id
        body
      }
    }
  }
}
```

This query returns an array. Each element will represent a `post` object, which itself will store an array of `comment` objects:

```json
// in the result of a selection across multiple nested relationships
{
  "blogs": [
    {
      "name": "my blog",
      "posts": [
        {
          "title": "Hello World",
          "comments": [
            { "id": 1, "body": "Nice post!" },
            { "id": 2, "body": "Cool blog!" }
          ]
        },
        {
          "title": "Thoughts on Software Development",
          "comments": []
        }
      ]
    }
  ]
}
```

### Expressions 

Gelly is an expression language: Gelly queries are made up of expressions that evaluate to a single value.

These expressions can be literals or they can apply transformations to records. Transformations can be simple string operations or math, complicated expressions with boolean logic, or aggregations that summarize a whole set of records into one value. Gelly contains a rich set of built-in functions to help with these transformations.

For example, we can select the full name for each record of a `customer` model by adding the `firstName` and `lastName` fields together.

```gelly
// in transform data using built-in Gelly functions, like concat
view {
  customers {
    concat([firstName, " ", lastName])
  }
}
```

Expressions must be separated from each other with whitespace characters. They can refer to other fields of the record in question, as well as use literals like strings and numbers to do useful work.

For example, an expression can be a simple literal and return the literal's value:

```gelly
// in a literal expression for a computed field
field on model {
  1
}
```

```json
// in the result of a literal expression
{
  "fieldName": 1
}
```

Expressions can also be complicated, and call functions or use boolean logic:

```gelly
// in a complex Gelly expression that combines multiple functions and boolean logic
view {
  comments {
    id
    isSpam: author.markedAsSpammer || contains(lower(title), item: "spam") || author.spamScore < (endsWith(author.email, "@gmail.com") ? 10 : 5)
  }
}
```

Read [the Gelly reference](https://docs.gadget.dev/reference/gelly#Functions) to learn about the built-in Gelly functions that can be used to build expressions.

#### Aliasing fields and expressions 

You can name an expression or rename a field by prefixing the selection with a name and a colon.

For example, to select the `title` field from a `post` but have the resulting JSON blob use the key `coolName` to store the field's value, you can run this query:

```gelly
view {
  posts {
    coolName: title
  }
}
```

This would produce an array result like:

```json
{
  "posts": [{ "coolName": "Hello World!" }, { "coolName": "Thoughts on Software Development" }]
}
```

Aliasing works on expressions as well. To output an uppercase version of the `title` field as `screamingTitle`, the expression can be aliased by prefixing the selection with an alias identifier and a colon.

```gelly
view {
  posts {
    screamingTitle: upper(title)
  }
}
```

This would produce a array result like the one below.

```json
{
  "posts": [{ "screamingTitle": "HELLO WORLD!" }, { "screamingTitle": "THOUGHTS ON SOFTWARE DEVELOPMENT" }]
}
```

If you don't alias an expression, the expression's source Gelly snippet is used as the output field name.

For example, querying for `upper(title)` will return the uppercase title under a field named `upper(title)`

```gelly
view {
  posts {
    upper(title)
  }
}
```

```json
{
  "posts": [{ "upper(title)": "HELLO WORLD!" }, { "upper(title)": "THOUGHTS ON SOFTWARE DEVELOPMENT" }]
}
```

You cannot alias a computed field that does not access data across a relationship field. This is because the computed field already has a name, and to give it an alias would be redundant. For example, if we have a computed field called `fullName` on author, we cannot alias it like this:

```gelly
// in alias will not work, not accessing related model data
field on author {
  newName: concat([firstName, " ", lastName"])
}
```

### Ternary expressions 

Gelly supports if-then-else statements using the `?` ternary expression, similar to JavaScript. Put a boolean expression first followed by a `?` symbol, and then two expressions separated by a `:`. When the boolean expression is true, the first expression is selected, otherwise, the second expression is selected.

For a simple example, we can select the string "yes" if 10 is greater than 5:

```gelly
view {
  greaterThan10: 10 > 5 ? "yes" : "no"
}
```

Which would produce a single value result, such as `greaterThan10: "yes"`.

Ternary expressions can also be used within selections of relations alongside other selections just fine.

```gelly
view {
  blogs {
    posts {
      id
      title
      recent: createdAt < (now() - interval("60 days")) ? createdAt : "Old"
    }
  }
}
```

Unlike SQL, ternary expressions don't need to return the same data type from the truthy and falsy expressions on either side of the colon.

## Relational commands 

The list of records passed through selection sets in Gelly can be changed using relational commands like , ,  and . Relational commands are always enclosed in square brackets within a selection set and only affect the returned records for that level of the selection.

For example, we can limit the list of records returned with the `[limit 5]` relational command:

```gelly
view {
  posts {
    id
    title
    [limit 5]
  }
}
```

Relational commands change the data returned by the query, but don't change the data stored in the database. Relational commands are able to use fields from the model being queried. For example, if the `post` model had a `published` boolean field on it, then we can use the `where` relational command to filter the list of returned posts down to only those that are published.

```gelly
view {
  posts {
    id
    title
    [where published]
  }
}
```

We can also use expressions to filter returned records. For example, we could return only posts with a score above a certain threshold that are also published:

```gelly
view {
  posts {
    id
    title
    [where published && score > 10]
  }
}
```

Relational commands can be used within inner, nested selection sets without affecting the outer selection set. For example, we can select the top three comments for each post sorted by comment score:

```gelly
field on blog {
  posts {
    id
    title
    comments {
      id
      body
      [order by score desc limit 3]
    }
  }
}
```

We can also issue multiple relational commands to inner and outer selection sets simultaneously. For example, we can select the three most recent posts which have been published, and select the top three comments of each post:

```gelly
view {
  posts {
    id
    title
    [where published limit 3 order by createdDate desc]

    comments {
      id
      body
      [order by score desc limit 3]
    }
  }
}
```

The order of relational commands within the `[...]` stanza doesn't matter and the position of the command list among all the fields in a selection set also doesn't matter.

Relational commands are very similar (and in fact powered by) the SQL query clauses of the same name, and tend to work the same way as their SQL counterparts. **If you are familiar with SQL, you can use the relational commands like you would use the same keywords in SQL!**

### `limit` 

The `limit` relational command ensures a maximum number of results are returned for the selection set. A selection set containing `[limit 10]` will return a maximum of 10 records. If the underlying model has fewer records than the `limit`, or if a `where` command filters them out, _up to_ the limit records will be returned.

```markdown
[limit <number>]
```

For example, we could select up to 5 posts with the `limit` command.

```gelly
// in limiting to a count
view {
  posts {
    title
    [limit 5]
  }
}
```

The `limit` command can be used more than once, on both an outer selection and an inner selection. For example, we could select 5 posts and 3 comments for each post with two `limit` commands.

```gelly
// in limiting at different nesting
view {
  posts {
    title
    [limit 5]
    comments {
      body
      [limit 3]
    }
  }
}
```

The `limit` command is often combined with the `order by` command in order to get the top records by some criteria. For example, we can get the top 10 scoring posts ever by combining the two commands.

```gelly
// in combining limit and order by
view {
  posts {
    title
    score
    [order by score desc limit 10]
  }
}
```

The `limit` command supports the optional `offset` parameter. It takes one fixed integer that denotes a position in the list of results to start from. The query will return up to `limit` number of results starting from the `offset` position. This can be used to "page" through a long list of results, the `limit` reflects the size of the page and the `offset` reflects the position where the page should start.

```gelly
// in getting second page of 5 posts
view {
  posts {
    title
    [limit 5 offset 5]
  }
}
```

### `order by` 

The `order by` relational command sorts the returned records by the specified `expression` in either ascending or descending order. The default sort direction is ascending, where the record with the lowest value will be first, and the record with the highest value will be last.

```markdown
[order by <expression> <asc|desc>]
```

For example, `order by` can ensure the most recent records for a table are returned by using `[order by createdDate desc]`.

```gelly
// in ordering by a date field
view {
  posts {
    title
    [order by createdDate desc]
  }
}
```

The `order by` expression is not limited to simple queries. It can also perform rich computations on any available data:

```gelly
// in ordering by an expression
view {
  posts {
    title
    [order by author.scoreWeight * post.score]
  }
}
```

### `where` 

The `where` command filters the stored records down based on some conditions. `where` expects to be passed a boolean expression and will include records where the expression evaluates to `true`.

```markdown
[where <expression>]
```

For example, you can find only the posts that have a score greater than 0 with the `where` command within the `posts` selection set:

```gelly
// in a basic where expression example
view {
  posts {
    title
    [where score > 0]
  }
}
```

`where` command expressions can be arbitrarily complicated. For example, you can query for posts that have a score over 50 by a trusted author, or posts which have a score over 100 by a spammy author.

```gelly
// in a more complex where expression
view {
  posts {
    title
    score
    author.isSpam
    [where (!author.isSpam && score > 50) || (author.isSpam && score > 100)]
  }
}
```

Like all relational commands, the `where` expression can be combined with other relational queries. For example, we can select the top 10 highest scoring posts where the author isn't spammy by using `where`, `limit`, and `order by`.

```gelly
// in combine where with other relational commands
view {
  posts {
    title
    score
    [where !author.isSpam order by score desc limit 10]
  }
}
```

### `group by` 

The `group by` command is very similar to the `GROUP BY` query in SQL. This command will group records into smaller buckets, and then solve aggregations based on these groupings.

```markdown
[group by <expression> <, ...expression> <, ...expression>]
```

`group by` expects to be given expressions and fields to group the incoming records. The records will first be sorted into 'buckets' using these fields and expressions. Once the groups have been made, then any aggregate functions in the selection set are evaluated for each group.

For example, we could group post records by their author id to count the number of posts each author has made. This query would return one row per unique `authorId` value, holding the `authorId` and the number of posts, as counted by `count(id)`. Be sure to explicitly select the `authorId` field to know which count is for which `authorId` in the results.

```gelly
// in post count, grouped by author
view {
  posts {
    count(id)
    authorId
    [group by authorId]
  }
}
```

`group by` is not limited to fields, and can also be used on expressions on fields. For instance, we could alias a field `scoreBucket`, which rounds each post score to a multiple of 10.

This temporary field could then be used in a group by expression:

```gelly
// in post count by score bucket
view {
  posts {
    count(id)
    scoreBucket: score % 10
    [group by scoreBucket]
  }
}
```

Like all relational commands, `group by` can be combined with other related commands. `limit` and `order by` affect the group results, and queries are able to order by fields produced by the grouping.

For example, we could order the post-counts-by-author results by the resulting post count:

```gelly
// in ordering aggregate results
view {
  posts {
    count(id)
    authorId
    [group by authorId order by count(id) desc]
  }
}
```

`group by` can be combined with `where` commands as well. Gelly groups records **after** they have been filtered by a `where` command. This allows you to group only records you are interested in.

For example, we can compute the count of posts by author where the post was not marked as spam:

```gelly
// in spam count, grouped by author
view {
  posts {
    count(id)
    authorId
    [
      group by authorId
      where !isSpam
    ]
  }
}
```

Like `GROUP BY` in SQL, the `group by` command will cause an error if you try to select fields that aren't part of the group or aggregation expressions. This is because the output rows from a `group by` must be aggregated, and Gelly doesn't know what to do with fields that you don't specify an aggregate function for.

For example, if we group posts by `authorId`, and then try to select the count of posts, and each post's score, it is unclear what to do with the score of each post to form the group:

```gelly
// in ungrouped fields cause errors
view {
  posts {
    count(id)
    # will throw an error because the field is not being aggregated
    score
    [group by authorId]
  }
}
```

#### Collecting unique values 

The `group by` command can be used to collect unique values of a given field.

```gelly
// in collecting unique post authors
view {
  postAuthors: posts {
    authorId
    authorName: author.name
    [group by authorId, authorName]
  }
}
```

Since group by can be used in nested sub-selections as well, it is also possible to collect unique values from related models. And since each subselection can be grouped independently, you can collect multiple unique value sets from related models in the same query.

```gelly
// in collecting unique comment authors for each post
view {
  posts {
    title
    commentAuthors: comments {
      authorId
      authorName: author.name
      [group by authorId, authorName]
    }
  }
}
```

If you are only interested in counts of unique values, the [`countDistinct()` function](https://docs.gadget.dev/reference/gelly#countdistinct-value-any-where-boolean-integer) provides more efficient way to obtain those.

#### Alternatives to `group by` 

`group by` in Gelly tends to be used less than in SQL. There's nothing wrong with using it, but in Gelly, there's often a simpler way of executing the same query without using group by that most consider clearer, specifically when grouping the related records of a parent record. **If you're grouping a set of records by the same key of a relationship, you can potentially reconfigure your query to use a related model aggregation instead!**

For example, a query that counts the number of posts per author could be rewritten using an aggregation function. This switches the response to be oriented around the `author` model rather than the post, which allows for getting things like the author's name:

```gelly
// in an alternative to group by: authors with count of posts
view {
  authors {
    id
    name
    count(posts.id)
  }
}
```

## Aggregating data 

Gelly has built-in [aggregation functions](https://docs.gadget.dev/reference/gelly#aggregation-functions) that can be used to perform computations across a number of records to extract meaningful data and trends.

Most aggregate functions accept a `where` argument, which is a boolean expression that can be used to filter existing records before applying the aggregation.

For example, you could count the number of `posts` that have titles beginning with "Software":

```gelly
// in a simple count aggregation
view {
  posts {
    count(id, where: startsWith(title, prefix: "Software"))
  }
}
```

### Average 

Averages can be calculated with the `avg` aggregation. This function accepts a numerical field, such as `likes` on `post`.

For example, we could calculate the average cost of items bought per `order`:

```gelly
// in calculating an average
view {
  orders {
    avg(items.price)
  }
}
```

We could expand this query to include the `where` argument, and calculate the average price of items bought per order where more than one of the item was purchased:

```gelly
// in calculating an average with a "where" relational command
view {
  orders {
    avg(items.price, where: items.count>1)
  }
}
```

### Every 

`every` can be used to determine if _all_ values in a field meet specified condition. This function accepts boolean values or functions that return boolean values, and will return `true` if all values evaluate to `true`, and `false` otherwise.

The simplest example of this would be to check if all orders have been fulfilled for a store, where `fulfilled` will be a boolean value. If all orders have been fulfilled, then the function will return true:

```gelly
// in check if ALL orders have been fulfilled
view { allFulfilled: every(orders.fulfilled) }
```

A more complex example would involve an expression. For instance, we could check if all orders are being shipped to Ontario:

```gelly
// in check if ALL orders are being shipped to Ontario
view {
  orders {
    allOntario: every(province=="Ontario")
  }
}
```

### Some 

`some` will return `true` if _any_ values in a field meet the specified condition. `false` will be returned if no values meet the condition.

For example, we could check if any orders have been fulfilled for a store, where `fulfilled` is a boolean value. If any order has been fulfilled, then the function will return `true`:

```gelly
// in check if ANY orders have been fulfilled
view {
  orders {
    someFulfilled: some(fulfilled)
  }
}
```

### Count 

`count` can be used to count the number of non-null values across an aggregated set. This function, unlike other aggregate functions, accepts any field type.

For example, we could count the number of orders that have been fulfilled:

```gelly
// in count fulfilled orders
view {
  orders {
    fulfilledCount: count(id, where: fulfilled)
  }
}
```

### Maximum 

`max` can be used to find the maximum value of a `number` field.

For example, we can determine the largest price of an item in each order:

```gelly
// in find the highest item price in each order
view {
  orders {
    maxPrice: max(items.price)
  }
}
```

### Minimum 

`min` can be used to find the minimum value of a `number` field.

For example, we could determine the smallest price of an item in each order:

```gelly
// in find the lowest item price in each order
view {
  orders {
    minPrice: min(items.price)
  }
}
```

### Sum 

`sum` can be used to add the values of `number` fields across records.

For example, we could determine the total price of items ordered to be shipped to Ontario:

```gelly
// in sum the price of items shipped to Ontario
view {
  orders {
    total: sum(items.price, where: province=="Ontario")
  }
}
```

## Examples of Gelly queries 

For the following examples, we will use an employee management system database that has two models: `employee` and `department`. `department` has many `employees`, which is captured in the `employeeList` field on `department`.

To fetch the id of employees in a department, we would use this query:

```gelly
field on department {
  employee {
    id
  }
}
```

To fetch the employees with a last name beginning with A, we could use this query:

```gelly
field on department {
  employeeList {
    id
    [where startsWith(lastName, prefix: "A")]
  }
}
```

To ensure that capitalization does not matter when finding employees with a last name beginning with A, we could use the query:

```gelly
field on department {
  employeeList {
    id
    [where startsWith(upper(lastName), prefix: "A")]
  }
}
```

To fetch the top three employees with the highest salary, we could use this query:

```gelly
field on department {
  employeeList {
    firstName
    lastName
    [limit 3 order by salary desc]
  }
}
```

To fetch employees that are managers or regular employees who earn above $100,000, we can use this query:

```gelly
field on department {
  employeeList {
    firstName
    lastName
    [where (role=="manager") || (role!="manager" && salary >100000)]
  }
}
```

To count the total number of employees by role, we can use `group by` like so:

```gelly
field on department {
  employeeList {
    role
    numberOfEmployees: count(id)
    [group by role]
  }
}
```

### Why Gelly? 

While JavaScript is fantastic for implementing business logic and behavior in your app, it's not designed for complex data computations. For applications that require complex queries and aggregations, Gelly is the optimal choice.

Gelly is specifically built for:

*   **Efficient computations** on large datasets.
*   **Declarative expressions** that allow Gadget to optimize and cache results for better performance.
*   **High scalability**, making it ideal for use cases like reporting, dashboards, and data-driven decision-making.

Under the hood, Gelly is compiled into high-performance SQL queries, allowing operations to be executed inside the database. A custom caching layer exists on top which helps to further speed up Gelly operations.

Gadget does this in order to power features like conditional permissions that decide if a row is visible using data from related rows, an operation that is not possible at scale if the computation is defined in JavaScript because Gadget would have to run the computation for every row in the database to know which ones to return.

### Gelly vs SQL 

Gelly draws inspiration from **SQL**, but it introduces several improvements:

*   **Simplified syntax**: Gelly has a more readable and developer-friendly syntax compared to SQL.
*   **Automatic joins**: Relationship traversal is built into Gelly so you don't have to manually specify JOIN conditions.
*   **Less boilerplate**: You can retrieve related data with minimal effort, eliminating the need for complex SQL statements like `json_agg` or managing multiple queries.

While SQL is still essential for relational databases, Gelly abstracts the complexity and makes it easier to work with large datasets without sacrificing performance.

### Gelly vs GraphQL 

Gelly and **GraphQL** share similarities but serve different purposes:

*   **Gelly** allows you to perform **complex aggregations**, **filtering**, and **computations** on the server without requiring additional API endpoints for each query.
*   **GraphQL** is more suited for querying data in a flexible way but lacks built-in support for complex computations like aggregations or mathematical operations. Gelly handles these efficiently, directly within the query.

#### Key differences: 

*   **Arbitrary Expressions**: Gelly allows you to include mathematical and relational expressions within queries.
*   **Relational Commands**: Gelly provides built-in support for relational commands like `[where]`, `[order by]`, and `[group by]`, which are available on every relation, making it faster than GraphQL for complex data manipulations.
*   **No Mutations or Subscriptions**: Unlike GraphQL, Gelly doesn't handle mutations or live subscriptions. For those, you should continue using GraphQL.

### Understanding Gelly terminology 

Gelly is built around **relational algebra** and shares many of the same concepts as SQL. Here are the key terms you need to know:

*   **Views**: A Gelly query that performs operations like filtering, aggregation, or transformation of data.
*   **Fields**: Attributes in your models that you can work with in Gelly queries.
*   **Models**: The primary data structures in your app, similar to tables in SQL.

These concepts allow you to express complex data transformations concisely and efficiently.

### Accessing data with Gelly 

You can extend your app's API by using **computed fields** and **computed views** to perform complex data operations.

*   **Computed Fields**: These are read-only fields that store predefined Gelly queries, allowing you to efficiently calculate values across your data.
*   **Computed Views**: High-level Gelly queries that allow for complex aggregation, filtering, and transformations of your data.

#### Inline Gelly 

Inline Gelly queries allow you to define ad-hoc computations directly in your API calls. This is useful for dynamic queries where you don't need to define a reusable view in advance.

Example of an inline Gelly query:

```typescript
const result = await api.view(`{ count(customers) }`);
```

Inline Gelly queries are ideal for situations where you need to run a custom query based on user input or dynamically generated criteria.