> ## Documentation Index
> Fetch the complete documentation index at: https://docs.codenullapp.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Fetching data

> There are several methods that allow fetching data

There are several methods that allow fetching data

* Find a record by Id
* Find a list of records paginated
* Find a list of records paginated and get the total of items in all pages

## Methods

### Find by Id

This method allows you to get an item by ID, You will find this method always with the same name of the entity

```graphql theme={null}
Usuario(Id: "1"){
    Id
    Correo
}
```

### Find a list of records

This method allows you to get a list of items, You will find this method always with the same name of the database table

```graphql theme={null}
Usuarios {
    Id
    Correo
    Identificacion
}
```

### Find a list of items and count

This is similar to the previous, but this allows us to get a "`total`" of items in the list. The name of this method is also the same as the previous one but with the "AndCount" suffix.

```graphql theme={null}
UsuariosAndCount {
    items {
      Id
      Correo
      Identificacion
    }
    total
}
```

## Parameters

### Filtering (`$where`)

Find a list and find a list and count accept a `where` parameter. This is a JSON parameter that allows it to be built dynamically.

```json theme={null}
{
    //Get an item by PersonaFK
    "PersonaFK": "05E78CBF-0E69-4D16-AFF5-E4256C50CCD0",
}
```

#### The next example combines the `and, like` and the `or` operators.

```json theme={null}
//Return a list where 
// (IglesiaId = "..." AND (Nombre like "felipe" OR identificacion like "felipe"))

{
    "IglesiaId": "05E78CBF-0E69-4D16-AFF5-E4256C50CCD0",
    "or": {
        "NombreCompleto": {
            "like": "%Felipe%"
        },
        "Identificacion": {
            "like": "%Felipe%"
        }
    }
}
```

#### **Specifies the condition** [**where** ](#user-content-fn-1)[^1]**the value is greater than or less than X.**

**Name**: The variable's name that holds the value to be used. *(Should include the type of operator to be applied at the end.)*

* **gt**: Greater than.
* **gte**: Greater than or equal to.
* **lt**: Less than.
* **lte**: Less than or equal to.

**Type**: Specifies the context from which the value will be retrieved.

**Value:** Name of the property that contains the value to be used.

<figure>
  <img src="https://mintcdn.com/codenull/fUND-Qmj85v5Oam-/images/wvYallvmsFhKpHknviAw.png?fit=max&auto=format&n=fUND-Qmj85v5Oam-&q=85&s=0a86c33b9982b9af8f64d1fa2772d726" alt="" width="689" height="300" data-path="images/wvYallvmsFhKpHknviAw.png" />

  <figcaption />
</figure>

The next filter allows to filter in a related table **(Many to One)**

<Info>
  Notice the use of `$` to specify the property belongs to a different table
</Info>

```json theme={null}
Return a list where
// (IglesiaId = "..." 
// AND 
// (Concepto.Tipo like "Arriendo" OR Concepto.Descripcion = "Arriendo"))
{
    "IglesiaId": "05E78CBF-0E69-4D16-AFF5-E4256C50CCD0",
    "or": {
        "$Concepto.Tipo$": {
            "like": "%Arriendo%"
        },
        "$Concepto.Descripcion$": "%Arriendo%",
    }
}
```

#### Nested Relation Queries

#### When to use this?

When you need to filter data using fields from related tables. For example: search samples by client name.

```graphql theme={null}
query {
  YourModel(
    where: {
      "$Relation.SubRelation.Field$": "value"
    }
    queryOptions: {
      includeOptions: [
        {
          model: "Relation"
          required: true
          include: [
            { model: "SubRelation", required: true }
          ]
        }
      ]
    }
  ) {
    Field1
    Relation {
      SubRelation {
        Field
      }
    }
  }
}
```

#### Real Example

Search samples by client name:

```graphql theme={null}
query {
  Muestras(
    where: {
      "$MarcaPorCliente.Cliente.Nombre$": { like: "%User%" }
    }
    queryOptions: {
      includeOptions: [
        {
          model: "MarcaPorCliente"
          required: true
          include: [
            { model: "Cliente", required: true }
          ]
        }
      ]
    }
  ) {
    Id
    Nombre
    MarcaPorCliente {
      Cliente {
        Nombre
      }
    }
  }
}
```

Parameters

* **required: true** → Only records WITH the relation (INNER JOIN)
* **required: false** → Include records WITHOUT the relation (LEFT JOIN)

Important Rule

⚠️ If you filter by `$Relation.Field$`, you **MUST** include that relation in [queryOptions](vscode-file://vscode-app/c:/Users/yojan/AppData/Local/Programs/Microsoft%20VS%20Code/resources/app/out/vs/code/electron-browser/workbench/workbench.html)

####

#### Include Filter

The next filter allows to filter in a related table **(One to Many)**

```json theme={null}
// when you need to pass a filter to One to Many relation, 
// You can use the reserved keyword "includeFilter"
// The next query returns a list of users where the role is Admin
{
  "includeFilter": {
    "RolesxUsuarios": { 
       "includeFilter": { 
          "Rol": { 
             "Nombre": "Admin" 
          } 
       } 
    }
  }
}
```

### Pagination (`$limit, $offset`)

All endpoints that return a list of items allow pagination by default. You only need to pass the $limit and $offset variables in the data source.

If you are configuring a data source for a remote drop-down or data grid component, you only need to pass the variables as follows. The component will handle the right values for these variables.

<Info>
  If you don't define the `limit` variable, the default value will be 100
</Info>

```graphql theme={null}
query Movimientos($where: JSON, $limit: Int, $offset: Int) {
  MovimientosAndCount(
    where: $where
    limit: $limit
    offset: $offset
  ) {
    items {
      Id
      Fecha
      Descripcion
      ConceptoId
    }
    total
  }
}
```

<Warning>
  If you pass `limit=null` explicitly then we will ignore the limit option completely. It will return all data from the database (Not recommended). This is causing some edge case issues when using nested column filter notation to details.&#x20;

  [https://github.com/sequelize/sequelize/issues/10962](https://github.com/sequelize/sequelize/issues/10962) [https://github.com/sequelize/sequelize/pull/11077](https://github.com/sequelize/sequelize/pull/11077) [https://github.com/sequelize/sequelize/issues/6400#issuecomment-950544812](https://github.com/sequelize/sequelize/issues/6400#issuecomment-950544812)
</Warning>

### Ordering (`$order`)

You can define a list of fields that you want to use for ordering

```graphql theme={null}
query Movimientos($where: JSON, $limit: Int, $offset: Int) {
  MovimientosAndCount(
    where: $where
    order: [["Fecha", "desc"]] //order desc by Date
    limit: $limit
    offset: $offset
  ) {
    items {
      Id
      Fecha
      Descripcion
    }
    total
  }
}

query Movimientos() {
  MovimientosAndCount(
    order: [["Fecha", "desc"], ["Descripcion", "asc"]] //order desc by Date and Description
  ) {
    items {
      Id
      Fecha
      Descripcion
    }
    total
  }
}
```

#### **Ordering by Child Properties (Attributes)**&#x20;

To order a query result by a child property (attribute) of an associated model, you can use the `order` option in Sequelize. This option takes an array of items, each representing a column to order by and its direction (ascending or descending). Here’s how you can do it:

```graphql theme={null}
query getUserDataApp {
  Usuarios(
    order: [["UsuariosXEmpresas", "EmpresaSeleccionada", "desc"]]
  ) {
    Id
    Nombre
    Correo
    Apellidos
    Foto
    Identificacion
    UsuariosXEmpresas {
      EmpresaFK
      EmpresaSeleccionada
      Empresa {
        Nombre
      }
    }
  }
}
```

### Query Options (`$queryOptions`)

By default, all join clauses in Codenull are "`LEFT OUTER JOIN`". However you can overwrite this behavior. This parameter is optional.

You can customize the following options in the `queryOptions` parameter:

| Option           |                                                                                                                                                  | Default Value |
| ---------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ | ------------- |
| `required`       | When `true` all joins will be handle as "`INNER JOIN`"                                                                                           | false         |
| `includeOptions` | Allows to set Inner or left join at table level. This is an array with `model and` `required` props like this `{ model: "Role", required: true}` | null          |

<Info>
  This will return different sets of data depending on how you configure, be aware of the configuration you are defining.
</Info>

```graphql theme={null}
# This will return all users where the role is Admin

query {
  UsuariosAndCount(
    queryOptions: {
      required: false
      includeOptions: [{ model: "Rol", required: true }]
    }
    where: {
      includeFilter: {
        RolesxUsuarios: { includeFilter: { Rol: { Nombre: "Admin" } } }
      }
    }
  ) {
    items {
      Nombre
      RolesxUsuarios {
        Rol {
          Nombre
        }
      }
    }
  }
}

#resulting query, pay attention to the left join and inner joins

# SELECT
#    [Usuarios].*,
#    [RolesxUsuarios].[Id] AS [RolesxUsuarios.Id],
#    [RolesxUsuarios->Rol].[Nombre] AS [RolesxUsuarios.Rol.Nombre],
#    [RolesxUsuarios->Rol].[Id] AS [RolesxUsuarios.Rol.Id]
# FROM
#    (
#        SELECT
#            [Usuarios].[Nombre],
#            [Usuarios].[Id]
#        FROM
#            [Usuarios] AS [Usuarios]
#        WHERE
#            (
#                SELECT
#                    [RolesxUsuarios].[UsuarioId]
#                FROM
#                    [RolesxUsuarios] AS [RolesxUsuarios]
#                    INNER JOIN [Roles] AS [Rol] ON [RolesxUsuarios].[RoleId] = [Rol].[Id]
#                    AND [Rol].[Nombre] = N'Admin'
#                WHERE
#                    ([RolesxUsuarios].[UsuarioId] = [Usuarios].[Id])
#                ORDER BY
#                    [RolesxUsuarios].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
#            ) IS NOT NULL
#        ORDER BY
#            [Usuarios].[Id] OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
#    ) AS [Usuarios]
#    LEFT OUTER JOIN (
#        [RolesxUsuarios] AS [RolesxUsuarios]
#        INNER JOIN [Roles] AS [RolesxUsuarios->Rol] ON [RolesxUsuarios].[RoleId] = [RolesxUsuarios->Rol].[Id]
#        AND [RolesxUsuarios->Rol].[Nombre] = N'Admin'
#    ) ON [Usuarios].[Id] = [RolesxUsuarios].[UsuarioId];
```

The next configuration produces a different result.

```graphql theme={null}
# This will return all users but only will join roles whose name is "Admin"
# If the role is not "Admin", it will still return the user but the role will be null
query {
  UsuariosAndCount(
    queryOptions: {
      required: true
      includeOptions: [{ model: "Rol", required: false }]
    }
    where: {
      includeFilter: {
        RolesxUsuarios: { includeFilter: { Rol: { Nombre: "Admin" } } }
      }
    }
  ) {
    items {
      Nombre
      RolesxUsuarios {
        Rol {
          Nombre
        }
      }
    }
  }
}

#resulting query, pay attention to the left join and inner joins

# SELECT
#    [Usuarios].*,
#    [RolesxUsuarios].[Id] AS [RolesxUsuarios.Id],
#    [RolesxUsuarios->Rol].[Nombre] AS [RolesxUsuarios.Rol.Nombre],
#    [RolesxUsuarios->Rol].[Id] AS [RolesxUsuarios.Rol.Id]
# FROM
#    (
#        SELECT
#            [Usuarios].[Nombre],
#            [Usuarios].[Id]
#        FROM
#            [Usuarios] AS [Usuarios]
#        WHERE
#            (
#                SELECT
#                    [UsuarioId]
#                FROM
#                    [RolesxUsuarios] AS [RolesxUsuarios]
#                WHERE
#                    ([RolesxUsuarios].[UsuarioId] = [Usuarios].[Id])
#                ORDER BY
#                    [RolesxUsuarios].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
#            ) IS NOT NULL
#        ORDER BY
#            [Usuarios].[Id] OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
#    ) AS [Usuarios]
#    INNER JOIN [RolesxUsuarios] AS [RolesxUsuarios] ON [Usuarios].[Id] = [RolesxUsuarios].[UsuarioId]
#    LEFT OUTER JOIN [Roles] AS [RolesxUsuarios->Rol] ON [RolesxUsuarios].[RoleId] = [RolesxUsuarios->Rol].[Id]
#    AND [RolesxUsuarios->Rol].[Nombre] = N'Admin'

```

#### Query Options with multiple relations with the same table

If I have a table with several ties with the same table I should define the model name in the query options with the same name as the auto-generated name by codenull. For instance:

```graphql theme={null}
query getOrdenes($offset: Int, $limit: Int, $where: JSON) {
  Ordenes: OrdenesAndCount(
    where: $where
    offset: $offset
    limit: $limit
    #The name of the model is the same as the name of the field to be returned "UnidadNegocio__UnidadNegocioFK"
    queryOptions: {includeOptions: [ {model: "UnidadNegocio__UnidadNegocioFK", required: true}]}
  ) {
    items {
      Id
      UnidadNegocioFK
      UnidadNegocio__UnidadNegocioFK {
        Nombre
      }
      UnidadNegocioActualFK
    }
    total
  }
}
```

[^1]: See operator reference below.