Fetching data - Codenull

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

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

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.

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.

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

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

//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 ¹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. The next filter allows to filter in a related table (Many to One)

Notice the use of $ to specify the property belongs to a different table

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.

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:

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

Include Filter

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

// 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.

If you don’t define the limit variable, the default value will be 100

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

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. https://github.com/sequelize/sequelize/issues/10962 https://github.com/sequelize/sequelize/pull/11077 https://github.com/sequelize/sequelize/issues/6400#issuecomment-950544812

Ordering (`$order`)

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

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)

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:

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

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

# 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.

# 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:

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
  }
}

See operator reference below. ↩

​Methods

​Find by Id

​Find a list of records

​Find a list of items and count

​Parameters

​Filtering ($where)

​The next example combines the and, like and the or operators.

​Specifies the condition where 1the value is greater than or less than X.

​Nested Relation Queries

​When to use this?

​Real Example

​

​Include Filter

​Pagination ($limit, $offset)

​Ordering ($order)

​Ordering by Child Properties (Attributes)

​Query Options ($queryOptions)

​Query Options with multiple relations with the same table

Footnotes

Methods

Find by Id

Find a list of records

Find a list of items and count

Parameters

Filtering (`$where`)

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

Specifies the condition where ¹the value is greater than or less than X.

Nested Relation Queries

When to use this?

Real Example

Include Filter

Pagination (`$limit, $offset`)

Ordering (`$order`)

Ordering by Child Properties (Attributes)

Query Options (`$queryOptions`)

Query Options with multiple relations with the same table