Construtor de consultas

O construtor de consultas do banco de dados é usado para construir consultas SQL SELECT, UPDATE e DELETE. Para inserir novas linhas, você deve usar o insert query builder e usar o raw query builder para executar consultas SQL brutas.

Você pode obter uma instância do construtor de consultas do banco de dados usando um dos seguintes métodos.

import Database from '@ioc:Adonis/Lucid/Database'

Database.query()

// selecionar tabela retorna a instância do construtor de consultas também
Database.from('users')

Métodos/propriedades

A seguir está a lista de métodos/propriedades disponíveis na instância do construtor de consultas.

`select`

O método select permite selecionar colunas da tabela do banco de dados. Você pode passar uma matriz de colunas ou passá-las como vários argumentos.

Database
  .from('users')
  .select('id', 'username', 'email')

Aliases de coluna

Você pode definir aliases para as colunas usando a expressão as ou passando um objeto de par chave-valor.

Database
  .from('users')
  .select('id', 'email as userEmail')

Database
  .from('users')
  .select({
    id: 'id',

    // A chave é o nome do alias
    userEmail: 'email'
  })

Usando subconsultas

Além disso, você pode usar subconsultas e consultas brutas para gerar colunas em tempo de execução, por exemplo, selecionando o último endereço IP de login para um usuário na tabela user_logins.

Database
  .from('users')
  .select(
    Database
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id')
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip') // 👈 Isso é importante
  )

Usando consultas brutas

Semelhante a uma subconsulta, você também pode passar uma instância da consulta bruta.

Database
  .from('users')
  .select(
    Database
      .raw(`
        (select ip_address from user_logins where users.id = user_logins.user_id limit 1) as last_login_ip
      `)
  )

`from`

O método from é usado para definir a tabela de banco de dados para a consulta.

Database.from('users')

O construtor de consultas também permite usar tabelas derivadas passando uma subconsulta ou um fechamento (que atua como uma subconsulta).

Database.from((subquery) => {
  subquery
    .from('user_exams')
    .sum('marks as total')
    .groupBy('user_id')
    .as('total_marks')
}).avg('total_marks.total')

`where`

O método where é usado para definir a cláusula where em suas consultas SQL. O construtor de consultas aceita uma ampla variedade de tipos de argumentos para permitir que você aproveite todo o poder do SQL.

O exemplo a seguir aceita o nome da coluna como o primeiro argumento e seu valor como o segundo argumento.

Database
  .from('users')
  .where('username', 'virk')

Você também pode definir operadores SQL, conforme mostrado abaixo.

Database
  .from('users')
  .where('created_at', '>', '2020-09-09')

// Usando luxon para fazer a data
Database
  .from('users')
  .where('created_at', '>', DateTime.local().toSQLDate())

// Usando operador like
Database
  .from('posts')
  .where('title', 'like', '%Adonis 101%')

Grupos where

Você pode criar grupos where passando um retorno de chamada para o método where. Por exemplo:

Database
  .from('users')
  .where((query) => {
    query
      .where('username', 'virk')
      .whereNull('deleted_at')
  })
  .orWhere((query) => {
    query
      .where('email', 'virk@adonisjs.com')
      .whereNull('deleted_at')
  })

SQL gerado

sql

SELECT * FROM "users"
  WHERE (
    "username" = ? AND "deleted_at" IS NULL
  )
  or (
    "email" = ? AND "deleted_at" IS NULL
  )

Usando subconsultas

O valor do método where também pode ser uma subconsulta.

Database
  .from('user_groups')
  .where(
    'user_id',
    Database
      .from('users')
      .select('user_id')
      .where('users.user_id', 1)
  )

Usando consultas brutas

Da mesma forma, você também pode definir uma consulta bruta.

Database
  .from('user_groups')
  .where(
    'user_id',
    Database
      .raw(`select "user_id" from "users" where "users"."user_id" = ?`, [1])
      .wrap('(', ')')
  )

Variantes do método `where`

A seguir está a lista das variações do método where e compartilha a mesma API.

Método	Descrição
`andWhere`	Alias para o método `where`
`orWhere`	Adiciona uma cláusula or where
`whereNot`	Adiciona uma cláusula where not
`orWhereNot`	Adiciona uma cláusula or where not
`andWhereNot`	Alias para `whereNot`

`whereColumn`

O método whereColumn permite que você defina uma coluna como o valor para a cláusula where. O método geralmente é útil com consultas e junções. Por exemplo:

Database
  .from('users')
  .whereColumn('updated_at', '>', 'created_at')

Database
  .from('users')
  .select(
    Database
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id') // 👈
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip')
  )

Variantes do método `whereColumn`

A seguir está a lista de variações do método whereColumn e compartilha a mesma API.

Método	Descrição
`andWhereColumn`	Alias para o método `whereColumn`
`orWhereColumn`	Adiciona uma cláusula or where
`whereNotColumn`	Adiciona uma cláusula where not
`orWhereNotColumn`	Adiciona uma cláusula or where not
`andWhereNotColumn`	Alias para `whereNotColumn`

`whereLike`

Adiciona uma cláusula where com comparação de substring sensível a maiúsculas e minúsculas em uma coluna específica com um valor específico.

Database
  .from('posts')
  .whereLike('title', '%Adonis 101%')

`whereILike`

Adiciona uma cláusula where com comparação de substring insensível a maiúsculas e minúsculas em uma coluna específica com um valor específico. O método gera um ligeiramente diferente para cada dialeto para obter a comparação insensível a maiúsculas e minúsculas.

Database
  .from('posts')
  .whereILike('title', '%Adonis 101%')

`whereIn`

O método whereIn é usado para definir a cláusula SQL wherein. O método aceita o nome da coluna como o primeiro argumento e uma matriz de valores como o segundo argumento.

Database
  .from('users')
  .whereIn('id', [1, 2, 3])

Os valores também podem ser definidos para mais de uma coluna. Por exemplo:

Database
  .from('users')
  .whereIn(['id', 'email'], [
    [1, 'virk@adonisjs.com']
  ])

// SQL: select * from "users" where ("id", "email") in ((?, ?))

Usando subconsultas

Você também pode calcular os valores whereIn usando uma subconsulta.

Database
  .from('users')
  .whereIn(
    'id',
    Database
      .from('user_logins')
      .select('user_id')
      .where('created_at', '<', '2020-09-09')
  )

Para várias colunas

Database
  .from('users')
  .whereIn(
    ['id', 'email'],
    Database
      .from('accounts')
      .select('user_id', 'email')
  )

O método whereIn também aceita um retorno de chamada como o 2º argumento. O retorno de chamada recebe uma instância da subconsulta que você pode usar para calcular valores como tempo de execução.

Database
  .from('users')
  .whereIn(
    'id',
    (query) => query.from('user_logins').select('user_id')
  )

Variantes do método `whereIn`

A seguir está a lista das variações do método whereIn e compartilha a mesma API.

Método	Descrição
`andWhereIn`	Alias para o método `whereIn`
`orWhereIn`	Adiciona uma cláusula or where in
`whereNotIn`	Adiciona uma cláusula where not in
`orWhereNotIn`	Adiciona uma cláusula or where not in
`andWhereNotIn`	Alias para `whereNotIn`

`whereNull`

O método whereNull adiciona uma cláusula where null à consulta.

Database
  .from('users')
  .whereNull('deleted_at')

Variantes do método `whereNull`

A seguir está a lista de variações do método whereNull e compartilha a mesma API.

Método	Descrição
`andWhereNull`	Alias para o método `whereNull`
`orWhereNull`	Adiciona uma cláusula or where null
`whereNotNull`	Adiciona uma cláusula where not null
`orWhereNotNull`	Adiciona uma cláusula or where not null
`andWhereNotNull`	Alias para `whereNotNull`

`whereExists`

O método whereExists permite adicionar restrições where verificando a existência de resultados em uma subconsulta. Por exemplo: Selecione todos os usuários que fizeram login pelo menos uma vez.

Database
  .from('users')
  .whereExists((query) => {
    query
      .from('user_logins')
      .whereColumn('users.id', 'user_logins.user_id')
      .limit(1)
  })

Você também pode passar uma subconsulta ou uma consulta bruta como o primeiro argumento.

Database
  .from('users')
  .whereExists(
    Database
      .from('user_logins')
      .whereColumn('users.id', 'user_logins.user_id')
      .limit(1)
  )

Database
  .from('users')
  .whereExists(
    Database.raw(
      'select * from user_logins where users.id = user_logins.user_id'
    )
  )

Variantes do método `whereExists`

A seguir está a lista das variações do método whereExists e compartilha a mesma API.

Método	Descrição
`andWhereExists`	Alias para o método `whereExists`
`orWhereExists`	Adiciona uma cláusula or where exists
`whereNotExists`	Adiciona uma cláusula where not exists
`orWhereNotExists`	Adiciona uma cláusula or where not exists
`andWhereNotExists`	Alias para o método `whereNotExists`

`whereBetween`

O método whereBetween adiciona a cláusula where between. Ele aceita o nome da coluna como o primeiro argumento e uma matriz de valores como o segundo argumento.

Database
  .from('users')
  .whereBetween('age', [18, 60])

Usando subconsultas

Você também pode usar subconsultas para derivar os valores de uma tabela de banco de dados diferente.

Database
  .from('users')
  .whereBetween('age', [
    Database.from('participation_rules').select('min_age'),
    Database.from('participation_rules').select('max_age'),
  ])

Usando consultas brutas

Você também pode usar consultas brutas para derivar valores de outra tabela de banco de dados.

Database
  .from('users')
  .whereBetween('age', [
    Database.raw('(select min_age from participation_rules)'),
    Database.raw('(select max_age from participation_rules)'),
  ])

Variantes do método `whereBetween`

A seguir está a lista das variações do método whereBetween e compartilha a mesma API.

Método	Descrição
`andWhereBetween`	Alias para o método `whereBetween`
`orWhereBetween`	Adiciona uma cláusula or where between
`whereNotBetween`	Adiciona uma cláusula where not between
`orWhereNotBetween`	Adiciona uma cláusula or where not between
`andWhereNotBetween`	Alias para o método `whereNotBetween`

`whereRaw`

Você pode usar o método whereRaw para expressar condições não cobertas pelos métodos existentes do construtor de consultas.

ATENÇÃO

Sempre certifique-se de vincular parâmetros e não codificar a entrada do usuário diretamente na consulta bruta.

❌ Codificando valores do usuário diretamente

Database
  .from('users')
  .whereRaw(`username = ${username}`)

✅ Usando parâmetros de vinculação

Database
  .from('users')
  .whereRaw('username = ?', [username])

Você também pode definir os nomes das colunas dinamicamente usando ??.

Database
  .from('users')
  .whereRaw('?? = ?', ['users.username', username])

Variantes do método `whereRaw`

A seguir está a lista das variações do método whereRaw e compartilha a mesma API.

Método	Descrição
`andWhereRaw`	Alias para o método `whereRaw`
`orWhereRaw`	Adiciona uma cláusula or where raw

`whereJson`

Adicione uma cláusula where com um objeto para corresponder ao valor de uma coluna JSON dentro do banco de dados.

Database
  .from('users')
  .whereJson('address', { city: 'XYZ', pincode: '110001' })

O valor da coluna também pode ser computado usando uma subconsulta.

Database
  .from('users')
  .whereJson(
    'address',
    Database
      .select('address')
      .from('user_address')
      .where('address.user_id', 1)
  )

Variantes do método `whereJson`

A seguir está a lista das variações do método whereJson e compartilha a mesma API.

Método	Descrição
`orWhereJson`	Adicione uma cláusula or where correspondente ao valor de uma coluna JSON
`andWhereJson`	Alias para `whereJson`
`whereNotJson`	Adicione uma cláusula where not em uma coluna JSON
`orWhereNotJson`	Adicione uma cláusula or where not em uma coluna JSON
`andWhereNotJson`	Alias para `whereNotJson`

`whereJsonSuperset`

Adicione uma cláusula onde o valor da coluna JSON é o superconjunto do objeto definido. No exemplo a seguir, o endereço do usuário é armazenado como JSON e encontramos pelo usuário pelo seu código PIN.

Database
  .from('users')
  .whereJsonSuperset('address', { pincode: '110001' })

Variantes do método `whereJsonSuperset`

A seguir está a lista das variações do método whereJsonSuperset e compartilha a mesma API.

Método	Descrição
`orWhereJsonSuperset`	Adicione uma cláusula or where correspondente ao valor de uma coluna JSON
`andWhereJsonSuperset`	Alias para `whereJsonSuperset`
`whereNotJsonSuperset`	Adicione uma cláusula where not em uma coluna JSON
`orWhereNotJsonSuperset`	Adicione uma cláusula or where not em uma coluna JSON
`andWhereNotJsonSuperset`	Alias para `whereNotJsonSuperset`

`whereJsonSubset`

Adicione uma cláusula onde o valor da coluna JSON é o subconjunto do objeto definido. No exemplo a seguir, o endereço do usuário é armazenado como JSON e encontramos o usuário pelo código PIN ou pelo nome da cidade.

Database
  .from('users')
  .whereJsonSubset('address', { pincode: '110001', city: 'XYZ' })

Variantes do método `whereJsonSubset`

A seguir está a lista das variações do método whereJsonSubset e compartilha a mesma API.

Método	Descrição
`orWhereJsonSubset`	Adicione uma cláusula ou where correspondente ao valor de uma coluna JSON
`andWhereJsonSubset`	Alias para `whereJsonSubset`
`whereNotJsonSubset`	Adicione uma cláusula where not em uma coluna JSON
`orWhereNotJsonSubset`	Adicione uma cláusula or where not em uma coluna JSON
`andWhereNotJsonSubset`	Alias para `whereNotJsonSubset`

`join`

O método join permite especificar junções SQL entre duas tabelas. Por exemplo: selecione as colunas ip_address e country unindo a tabela user_logins.

Database
  .from('users')
  .join('user_logins', 'users.id', '=', 'user_logins.user_id')
  .select('users.*')
  .select('user_logins.ip_address')
  .select('user_logins.country')

Você pode passar um retorno de chamada como o segundo argumento para definir mais restrições de junção.

Database
  .from('users')
  // highlight-start
  .join('user_logins', (query) => {
    query
      .on('users.id', '=', 'user_logins.user_id')
      .andOnVal('user_logins.created_at', '>', '2020-10-09')
  })
  // highlight-end
  .select('users.*')
  .select('user_logins.ip_address')
  .select('user_logins.country')

Para agrupar restrições de junção, você pode passar um retorno de chamada para o método on.

Database
  .from('users')
  .join('user_logins', (query) => {
    query
      // highlight-start
      .on((subquery) => {
        subquery
          .on('users.id', '=', 'user_logins.user_id')
          .andOnVal('user_logins.created_at', '>', '2020-10-09')
      })
      .orOn((subquery) => {
        subquery
          .on('users.id', '=', 'user_logins.account_id')
          .andOnVal('user_logins.created_at', '>', '2020-10-09')
      })
      // highlight-end
  })
  .select('users.*')
  .select('user_logins.ip_address')
  .select('user_logins.country')

SQL de saída

sql

SELECT
  "users".*,
  "user_logins"."ip_address",
  "user_logins"."country"
FROM "users"
  INNER JOIN "user_logins" ON (
    "users"."id" = "user_logins"."user_id" AND "user_logins"."created_at" > ?
  )
  or (
    "users"."id" = "user_logins"."account_id" AND "user_logins"."created_at" > ?
  )

O método join usa a junção interna por padrão, e você pode usar uma junção diferente usando um dos seguintes métodos disponíveis.

leftJoin
leftOuterJoin
rightJoin
rightOuterJoin
fullOuterJoin
crossJoin

`joinRaw`

Você pode usar o método joinRaw para expressar condições não cobertas pela API padrão do construtor de consultas.

Database
  .from('users')
  .joinRaw('natural full join user_logins')

Métodos On

A seguir está a lista de métodos on disponíveis que você pode usar com uma consulta join.

`onIn`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onIn('user_logins.country', ['India', 'US', 'UK'])
  })

`onNotIn`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotIn('user_logins.country', ['India', 'US', 'UK'])
  })

`onNull`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNull('user_logins.ip_address')
  })

`onNotNull`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotNull('user_logins.ip_address')
  })

`onExists`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onExists((subquery) => {
      subquery
        .select('*')
        .from('accounts')
        .whereRaw('users.account_id = accounts.id')
    })
  })

`onNotExists`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotExists((subquery) => {
      subquery
        .select('*')
        .from('accounts')
        .whereRaw('users.account_id = accounts.id')
    })
  })

`onBetween`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onBetween('user_logins.login_date', ['2020-10-01', '2020-12-31'])
  })

`onNotBetween`

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotBetween('user_logins.login_date', ['2020-10-01', '2020-12-31'])
  })

`having`

O método having adiciona a cláusula having. Ele aceita o nome da coluna como o primeiro argumento, seguido pelo operador opcional e o valor.

Database
  .from('exams')
  .select('user_id')
  .groupBy('user_id')
  .having('score', '>', 80)

`havingRaw`

Na maioria das vezes, você se verá usando o método havingRaw, pois pode definir os agregados para a cláusula having.

Database
  .from('exams')
  .select('user_id')
  .groupBy('user_id')
  .havingRaw('SUM(score) > ?', [200])

Variantes do método `having`

A seguir está a lista de todos os métodos having disponíveis.

Método	Descrição
`havingIn`	Adiciona uma cláusula having in à consulta. Ele aceita uma matriz de valores.
`havingNotIn`	Adiciona uma cláusula having not in à consulta. Ele aceita uma matriz de valores.
`havingNull`	Adiciona uma cláusula having null à consulta.
`havingNotNull`	Adiciona uma cláusula having not null à consulta.
`havingExists`	Adiciona uma cláusula having exists à consulta.
`havingNotExists`	Adiciona uma cláusula having not exists à consulta.
`havingBetween`	Adiciona uma cláusula having between à consulta. Ela aceita uma matriz de valores.
`havingNotBetween`	Adiciona uma cláusula having not between à consulta. Ela aceita uma matriz de valores

`distinct`

O método distinct aplica a cláusula distinct à instrução select. Você pode definir um ou mais nomes de colunas como vários argumentos.

Database
  .from('users')
  .distinct('country')

Database
  .from('users')
  .distinct('country', 'locale')

Você pode chamar o método distinct sem nenhum parâmetro para eliminar linhas duplicadas.

Database.from('users').distinct()

Há outro método somente para PostgreSQL, distinctOn. Aqui está um artigo explicando SELECT DISTINCT ON.

Database
  .from('logs')
  .distinctOn('url')
  .orderBy('created_at', 'DESC')

`groupBy`

O método groupBy aplica a cláusula group by à consulta.

Database
  .from('logs')
  .select('url')
  .groupBy('url')

`groupByRaw`

O método groupByRaw permite escrever uma consulta SQL para definir a instrução group by.

Database
  .from('sales')
  .select('year')
  .groupByRaw('year WITH ROLLUP')

`orderBy`

O método orderBy aplica a cláusula order by à consulta.

Database
  .from('users')
  .orderBy('created_at', 'desc')

Você pode classificar por várias colunas chamando o método orderBy várias vezes.

Database
  .from('users')
  .orderBy('username', 'asc')
  .orderBy('created_at', 'desc')

Ou passe uma matriz de objetos.

Database
  .from('users')
  .orderBy([
    {
      column: 'username',
      order: 'asc',
    },
    {
      column: 'created_at',
      order: 'desc',
    }
  ])

Usando subconsultas

Você também pode passar uma instância de subconsulta para o método orderBy — por exemplo, Ordenar postagens pelo número de comentários que receberam.

const commentsCountQuery = Database
  .from('comments')
  .count('*')
  .whereColumn('posts.id', '=', 'comments.post_id')

Database
  .from('posts')
  .orderBy(commentsCountQuery, 'desc')

`orderByRaw`

Use o método orderByRaw para definir a ordem de classificação de uma string SQL.

const commentsCountQuery = Database
  .raw(
    'select count(*) from comments where posts.id = comments.post_id'
  )
  .wrap('(', ')')

Database
  .from('posts')
  .orderBy(commentsCountQuery, 'desc')

`offset`

Aplicar offset à consulta SQL

Database.from('posts').offset(11)

`limit`

Aplicar um limite à consulta SQL

Database.from('posts').limit(20)

`forPage`

O forPage é um método conveniente para aplicar offset e limit usando o número da página. Ele aceita um total de dois argumentos.

O primeiro argumento é o número da página (não o offset).
O segundo argumento é o número de linhas a serem buscadas. O padrão é 20

Database
  .from('posts')
  .forPage(request.input('page', 1), 20)

`count`

O método count permite que você use o count agregado em suas consultas SQL.

NOTA

As chaves para os valores agregados são específicas do dialeto e, portanto, recomendamos que você sempre defina aliases para saída previsível.

NOTA

No PostgreSQL, o método count retorna uma representação de string de um tipo de dado bigint.

const users = await Database
  .from('users')
  .count('* as total')

console.log(users[0].total)

Você também pode definir o agregado da seguinte forma:

const users = await Database
  .from('users')
  .count('*', 'total')

console.log(users[0].total)

Você pode contar várias colunas da seguinte forma:

const users = await Database
  .from('users')
  .count({
    'active': 'is_active',
    'total': '*',
  })

console.log(users[0].total)
console.log(users[0].active)

Outros métodos de agregação

A API para todos os métodos de agregação a seguir é idêntica ao método count.

Método	Descrição
`countDistinct`	Contar apenas as linhas distintas
`min`	Agregar valores usando a função min
`max`	Agregar valores usando a função max
`sum`	Agregar valores usando a função sum
`sumDistinct`	Agregar valores apenas para linhas distintas usando a função sum
`avg`	Agregar valores usando a função avg
`avgDistinct`	Agregar valores apenas para linhas distintas usando a função avg

`union`

O método union permite que você crie uma consulta de união usando várias instâncias do construtor de consultas. Por exemplo:

Database
  .from('users')
  .whereNull('last_name')
  .union((query) => {
    query.from('users').whereNull('first_name')
  })

/**
SELECT * FROM "users" WHERE "last_name" IS NULL
UNION
SELECT * FROM "users" WHERE "first_name" IS NULL
*/

Você também pode encapsular suas consultas de união passando um sinalizador booleano como o 2º argumento.

Database
  .from('users')
  .whereNull('last_name')
  .union((query) => {
    query.from('users').whereNull('first_name')
  }, true) // 👈

/**
SELECT * FROM "users" WHERE "last_name" IS NULL
UNION
(SELECT * FROM "users" WHERE "first_name" IS NULL)
*/

Você pode passar uma matriz de retornos de chamada para definir várias consultas de união.

Database
  .from('users')
  .whereNull('last_name')
  // highlight-start
  .union([
    (query) => {
      query.from('users').whereNull('first_name')
    },
    (query) => {
      query.from('users').whereNull('email')
    },
  ], true)
  // highlight-end

// highlight-start
/**
SELECT * FROM "users" WHERE "last_name" IS NULL
UNION
(SELECT * FROM "users" WHERE "first_name" IS NULL)
UNION
(SELECT * FROM "users" WHERE "email" IS NULL)
*/
// highlight-end

Usando subconsultas

Você também pode definir consultas de união passando uma instância de um construtor de consultas.

Database
  .from('users')
  .whereNull('last_name')
  // highlight-start
  .union([
    Database.from('users').whereNull('first_name'),
    Database.from('users').whereNull('email')
  ], true)
  // highlight-end

Os métodos a seguir têm a mesma API que o método union.

unionAll
intersect

`with`

O método with permite que você use CTE (Common table expression) em bancos de dados PostgreSQL, Oracle, SQLite3 e MSSQL.

Database
  .query()
  .with('aliased_table', (query) => {
    query.from('users').select('*')
  })
  .select('*')
  .from('aliased_table')

/**
WITH "aliased_table" AS (
  SELECT * FROM "users"
)
SELECT * FROM "aliased_table"
*/

O método também aceita um terceiro parâmetro opcional que é uma matriz de nomes de colunas. O número de nomes de colunas especificado deve corresponder ao número de colunas no conjunto de resultados da consulta CTE.

Database
  .query()
  .with('aliased_table', (query) => {
    query.from('users').select('id', 'email')
  }, ['id', 'email'])
  .select('*')
  .from('aliased_table')

/**
WITH "aliased_table" (id, email) AS (
  SELECT * FROM "users"
)
SELECT * FROM "aliased_table"
*/

`withMaterialized` / `withNotMaterialized`

Os métodos withMaterialized e withNotMaterialized permitem que você use CTE (Common table expression) como visualizações materializadas em bancos de dados PostgreSQL e SQLite3.

Database
  .query()
  .withMaterialized('aliased_table', (query) => {
    query.from('users').select('*')
  })
  .select('*')
  .from('aliased_table')

/**
WITH "aliased_table" AS MATERIALIZED (
  SELECT * FROM "users"
)
SELECT * FROM "aliased_table"
*/

`withRecursive`

O método withRecursive cria uma CTE (Common table expression) recursiva em bancos de dados PostgreSQL, Oracle, SQLite3 e MSSQL.

No exemplo a seguir, calculamos a soma de todas as contas filhas de uma conta pai. Além disso, assumimos a seguinte estrutura de tabela.

id	name	parent_id	amount
1	Expenses	NULL	NULL
2	Car Expenses	1	100
3	Food Expenses	1	40
4	Earnings	NULL	NULL
5	Freelance work	4	100
6	Blog post payment	4	78
7	Car service	2	60

Database
  .query()
  .withRecursive('tree', (query) => {
    query
      .from('accounts')
      .select('amount', 'id')
      .where('id', 1)
      .union((subquery) => {
        subquery
          .from('accounts as a')
          .select('a.amount', 'a.id')
          .innerJoin('tree', 'tree.id', '=', 'a.parent_id')
      })
  })
  .sum('amount as total')
  .from('tree')

O exemplo acima não pretende simplificar a complexidade do SQL. Em vez disso, ele demonstra o poder do construtor de consultas para construir tais consultas SQL sem escrevê-las como uma string SQL.

Database
  .query()
  .withRecursive('tree', (query) => {
    query
      .from('accounts')
      .select('amount', 'id')
      .where('id', 1)
      .union((subquery) => {
        subquery
          .from('accounts as a')
          .select('a.amount', 'a.id')
          .innerJoin('tree', 'tree.id', '=', 'a.parent_id')
      })
  }, ['amount', 'id'])
  .sum('amount as total')
  .from('tree')

Aqui está um ótimo artigo explicando a Consulta Recursiva PostgreSQL

`update`

O método update permite atualizar uma ou mais linhas do banco de dados. Você pode usar o construtor de consultas para adicionar restrições adicionais ao executar a atualização.

const affectedRows = Database
  .from('users')
  .where('id', 1)
  .update({ email: 'virk@adonisjs.com' })

O valor de retorno é o número de linhas afetadas. No entanto, ao usar PostgreSQL, Oracle ou MSSQL, você também pode especificar as colunas de retorno.

const rows = Database
  .from('users')
  .where('id', 1)
  .update(
    { email: 'virk@adonisjs.com' },
    ['id', 'email'] // colunas para retornar
  )

console.log(rows[0].id)
console.log(rows[0].email)

`increment`

O método increment permite incrementar o valor de uma ou mais colunas.

Database
  .from('accounts')
  .where('id', 1)
  .increment('balance', 10)

/**
UPDATE "accounts"
SET
  "balance" = "balance" + 10
WHERE
  "id" = 1
*/

Você também pode incrementar várias colunas passando um objeto.

Database
  .from('accounts')
  .where('id', 1)
  .increment({
    balance: 10,
    credit_limit: 5
  })

/**
UPDATE "accounts"
SET
  "balance" = "balance" + 10,
  "credit_limit" = "credit_limit" + 5
WHERE
  "id" = 1
*/

`decrement`

O método decrement é o oposto do método increment. No entanto, a API é a mesma.

Database
  .from('accounts')
  .where('id', 1)
  .decrement('balance', 10)

`delete`

O método delete emite uma consulta SQL delete. Você pode usar o construtor de consultas para adicionar restrições adicionais ao executar a exclusão.

Database
  .from('users')
  .where('id', 1)
  .delete()

O método delete também tem um alias chamado del.

`useTransaction`

O método useTransaction instrui o construtor de consultas a encapsular a consulta dentro de uma transação. O guia em transações de banco de dados abrange diferentes maneiras de criar e usar transações em seu aplicativo.

const trx = await Database.transaction()

Database
  .from('users')
  .useTransaction(trx) // 👈
  .where('id', 1)
  .update({ email: 'virk@adonisjs.com' })

await trx.commit()

`forUpdate`

O método forUpdate adquire um bloqueio de atualização nas linhas selecionadas no PostgreSQL e MySQL.

NOTA

Certifique-se de sempre fornecer o objeto de transação usando o método useTransaction antes de usar forUpdate ou bloqueios semelhantes.

const user = Database
  .from('users')
  .where('id', 1)
  .useTransaction(trx)
  .forUpdate() // 👈
  .first()

`forShare`

O forShare adiciona um FOR SHARE no PostgreSQL e um LOCK IN SHARE MODE para MySQL durante uma instrução select.

const user = Database
  .from('users')
  .where('id', 1)
  .useTransaction(trx)
  .forShare() // 👈
  .first()

`skipLocked`

O método skipLocked ignora as linhas bloqueadas por outra transação. O método é suportado apenas pelo MySQL 8.0+ e PostgreSQL 9.5+.

Database
  .from('users')
  .where('id', 1)
  .forUpdate()
  .skipLocked() // 👈
  .first()

/**
SELECT * FROM "users"
WHERE "id" = 1
FOR UPDATE SKIP LOCKED
*/

`noWait`

O método noWait falha se qualquer uma das linhas selecionadas estiver bloqueada por outra transação. O método é suportado apenas pelo MySQL 8.0+ e PostgreSQL 9.5+.

Database
  .from('users')
  .where('id', 1)
  .forUpdate()
  .noWait() // 👈
  .first()

/**
SELECT * FROM "users"
WHERE "id" = 1
FOR UPDATE NOWAIT
*/

`clone`

O método clone retorna um novo objeto construtor de consultas com todas as restrições aplicadas da consulta original.

const query = Database.from('users').select('id', 'email')
const clonedQuery = query.clone().clearSelect()

await query // select "id", "email" from "users"
await clonedQuery // select * from "users"

`debug`

O método debug permite habilitar ou desabilitar a depuração em um nível de consulta individual. Aqui está um guia completo sobre consultas de depuração.

Database
  .from('users')
  .debug(true)

`timeout`

Defina o timeout para a consulta. Uma exceção é gerada após o tempo limite ter sido excedido.

O valor do tempo limite é sempre em milissegundos.

Database
  .from('users')
  .timeout(2000)

Você também pode cancelar a consulta ao usar tempos limite com MySQL e PostgreSQL.

Database
  .from('users')
  .timeout(2000, { cancel: true })

`toSQL`

O método toSQL retorna o SQL de consulta e as ligações como um objeto.

const output = Database
  .from('users')
  .where('id', 1)
  .toSQL()

console.log(output)

O objeto toSQL também tem o método toNative para formatar a consulta SQL de acordo com o dialeto do banco de dados em uso.

const output = Database
  .from('users')
  .where('id', 1)
  .toSQL()
  .toNative()

console.log(output)

`toQuery`

Retorna a consulta SQL após aplicar os parâmetros de ligação.

const output = Database
  .from('users')
  .where('id', 1)
  .toQuery()

console.log(output)
// select * from "users" where "id" = 1

Executando consultas

O construtor de consultas estende a classe nativa Promise. Você pode executar as consultas usando a palavra-chave await ou encadeando os métodos then/catch.

Database
  .from('users')
  .then((users) => {
  })
  .catch((error) => {
  })

Usando async/await

const users = await Database.from('users')

Além disso, você pode executar uma consulta chamando explicitamente o método exec.

const users = await Database.from('users').exec()

`first`

As consultas select sempre retornam uma matriz de objetos, mesmo quando a consulta tem como objetivo buscar uma única linha. No entanto, usar o método first fornecerá a primeira linha ou nulo (quando não houver linhas).

NOTA

First NÃO significa a primeira linha na tabela. Significa a primeira linha da matriz results em qualquer ordem em que você a buscou do banco de dados.

const user = await Database
  .from('users')
  .where('id', 1)
  .first()

if (user) {
  console.log(user.id)
}

`firstOrFail`

O método firstOrFail é semelhante ao método first, exceto que ele gera uma exceção quando nenhuma linha é encontrada.

const user = await Database
  .from('users')
  .where('id', 1)
  .firstOrFail()

Paginação

O construtor de consultas tem suporte de primeira classe para paginação baseada em deslocamento. Ele também conta automaticamente o número total de linhas executando uma consulta separada nos bastidores.

const page = request.input('page', 1)
const limit = 20

const results = await Database
  .from('users')
  .paginate(page, limit)

O método paginate retorna uma instância da classe SimplePaginator. A classe tem as seguintes propriedades e métodos.

`firstPage`

Retorna o número da primeira página. É sempre 1.

results.firstPage

`perPage`

Retorna o valor do limite passado para o método paginate.

results.perPage

`currentPage`

Retorna o valor da página atual.

results.currentPage

`lastPage`

Retorna o valor da última página considerando o total de linhas.

results.lastPage

`total`

Mantém o valor do número total de linhas no banco de dados.

results.total

`hasPages`

Um booleano para saber se há páginas para paginação. Você pode confiar neste valor para decidir quando ou não mostrar os links de paginação.

A seguir, um exemplo da visualização Edge.

edge

@if(results.hasPages)

  {{-- Display pagination links --}}

@endif

`hasMorePages`

Um booleano para saber se há mais páginas para ir depois da página atual.

results.hasMorePages

`all()`

Retorna uma matriz de linhas retornadas pelas consultas SQL.

results.all()

`getUrl`

Retorna a URL para um número de página fornecido.

result.getUrl(1) // /?page=1

`getNextPageUrl`

Retorna a URL para a próxima página

// Assumindo que a página atual é 2

result.getNextPageUrl() // /?page=3

`getPreviousPageUrl`

Retorna a URL para a página anterior

// Assumindo que a página atual é 2

result.getPreviousPageUrl() // /?page=1

`getUrlsForRange`

Retorna URLs para um intervalo fornecido. Útil quando você deseja renderizar links para um intervalo fornecido.

A seguir, um exemplo de uso de getUrlsForRange dentro de um modelo Edge.

edge

@each(
  link in results.getUrlsForRange(results.firstPage, results.lastPage)
)
  <a
    href="{{ link.url }}"
    class="{{ link.isActive ? 'active' : '' }}"
  >
    {{ link.page }}
  </a>
@endeach

`toJSON`

O método toJSON retorna um objeto com propriedades meta e data. A saída do método é adequada para respostas da API JSON.

results.toJSON()

/**
{
  meta: {
    total: 200,
    per_page: 20,
    current_page: 1,
    first_page: 1,
    last_page: 20,
    ...
  },
  data: [
    {
    }
  ]
}
*/

`baseUrl`

Todos os URLs gerados pela classe paginadora usam o URL / (raiz). No entanto, você pode alterar isso definindo um URL base personalizado.

results.baseUrl('/posts')

results.getUrl(2) // /posts?page=2

`queryString`

Defina a string de consulta a ser anexada aos URLs gerados pela classe paginadora.

results.queryString({ limit: 20, sort: 'top' })

results.getUrl(2) // /?page=2&limit=20&sort=top

Propriedades e métodos úteis

A seguir está a lista de propriedades e métodos que você pode precisar ocasionalmente ao criar algo em cima do construtor de consultas.

`client`

Referência à instância do cliente de consulta de banco de dados subjacente.

const query = Database.query()
console.log(query.client)

`knexQuery`

Referência à instância da consulta KnexJS subjacente.

const query = Database.query()
console.log(query.knexQuery)

`hasAggregates`

Um booleano para saber se a consulta está usando algum dos métodos de agregação.

const query = Database.from('posts').count('* as total')
console.log(query.hasAggregates) // true

`hasGroupBy`

Um booleano para saber se a consulta está usando uma cláusula group by.

const query = Database.from('posts').groupBy('tenant_id')
console.log(query.hasGroupBy) // true

`hasUnion`

Um booleano para saber se a consulta está usando uma união.

const query = Database
  .from('users')
  .whereNull('last_name')
  .union((query) => {
    query.from('users').whereNull('first_name')
  })

console.log(query.hasUnion) // true

`clearSelect`

Chame este método para limpar colunas selecionadas.

const query = Database.query().select('id', 'title')
query.clone().clearSelect()

`clearWhere`

Chame este método para limpar cláusulas where.

const query = Database.query().where('id', 1)
query.clone().clearWhere()

`clearOrder`

Chame este método para limpar a ordem por restrição.

const query = Database.query().orderBy('id', 'desc')
query.clone().clearOrder()

`clearHaving`

Chame este método para limpar a cláusula having.

const query = Database.query().having('total', '>', 100)
query.clone().clearHaving()

`clearLimit`

Chame este método para limpar o limite aplicado.

const query = Database.query().limit(20)
query.clone().clearLimit()

`clearOffset`

Chame este método para limpar o deslocamento aplicado.

const query = Database.query().offset(20)
query.clone().clearOffset()

`reporterData`

O construtor de consultas emite o evento db:query e relata o tempo de execução da consulta com o profiler do framework.

Usando o método reporterData, você pode passar detalhes adicionais para o evento e o profiler.

const query = Database.from('users')

await query
  .reporterData({ userId: auth.user.id })
  .select('*')

Dentro do evento db:query, você pode acessar o valor de userId da seguinte forma.

Event.on('db:query', (query) => {
  console.log(query.userId)
})

`withSchema`

Especifique o esquema PostgreSQL a ser usado ao executar a consulta.

Database
  .from('users')
  .withSchema('public')
  .select('*')

`as`

Especifique o alias para uma consulta fornecida. Geralmente útil ao passar a instância do construtor de consultas como uma subconsulta. Por exemplo:

Database
  .from('users')
  .select(
    Database
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id')
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip') // 👈 Alias da consulta
  )

`if`

O auxiliar if permite que você adicione restrições condicionalmente ao construtor de consultas. Por exemplo:

Database
  .from('users')
  .if(searchQuery, (query) => {
    query.where('first_name', 'like', `%${searchQuery}%`)
    query.where('last_name', 'like', `%${searchQuery}%`)
  })

Você pode definir o método else passando outro retorno de chamada como o segundo argumento.

Database
  .from('users')
  .if(
    condition,
    (query) => {}, // se a condição for atendida
    (query) => {}, // caso contrário, execute isto
  )

`unless`

O método unless é o oposto do auxiliar if.

Database
  .from('projects')
  .unless(filters.status, () => {
    /**
     * Buscar projetos com status "ativo" quando
     * status não definido em filtros
     */
    query.where('status', 'active')
  })

Você pode passar outro retorno de chamada que é executado quando a declaração unless não é verdadeira.

Database
  .from('users')
  .unless(
    condition,
    (query) => {}, // se a condição for atendida
    (query) => {}, // caso contrário, execute isto
  )

`match`

O auxiliar match permite que você defina uma matriz de blocos condicionais para corresponder e executar o retorno de chamada correspondente.

No exemplo a seguir, o construtor de consultas percorrerá todos os blocos condicionais e executará o primeiro correspondente e descartará o outro. Pense nisso como uma declaração switch em JavaScript.

Database
  .query()
  .match(
    [
      // Execute isto se o usuário for um superusuário
      auth.isSuperUser, (query) => query.whereIn('status', ['published', 'draft'])
    ],
    [
      // Execute isto se o usuário estiver logado
      auth.user, (query) => query.where('user_id', auth.user.id)
    ],
    // caso contrário, execute isto
    (query) => query.where('status', 'published').where('is_public', true)
  )

`ifDialect`

O auxiliar ifDialect permite que você adicione restrições condicionalmente ao construtor de consultas quando o dialeto corresponde a um dos dialetos mencionados.

Database
  .from('users')
  .query()
  .ifDialect('postgres', (query) => {
      query.whereJson('address', { city: 'XYZ', pincode: '110001' })
    }, 
  )

Você pode definir o método else passando outro retorno de chamada como o segundo argumento.

Database
  .from('users')
  .ifDialect('postgres',
    (query) => {}, // se o dialeto for postgres
    (query) => {}, // caso contrário, execute isto
  )

`unlessDialect`

O método unlessDialect é o oposto do auxiliar ifDialect.

Database
  .from('users')
  .unlessDialect('postgres', (query) => {
      query.whereJson('address', { city: 'XYZ', pincode: '110001' })
    } 
  )

Você pode passar outro retorno de chamada que é executado quando a declaração unlessDialect não é verdadeira.

Database
  .from('users')
  .query()
  .unlessDialect('postgres',
    (query) => {}, // se o dialeto for qualquer coisa diferente de postgres
    (query) => {}  // caso contrário, execute isto
  )

Construtor de consultas ​

Métodos/propriedades ​

select ​

Aliases de coluna ​

Usando subconsultas ​

Usando consultas brutas ​

from ​

where ​

Grupos where ​

Usando subconsultas ​

Usando consultas brutas ​

Variantes do método where ​

whereColumn ​

Variantes do método whereColumn ​

whereLike ​

whereILike ​

whereIn ​

Usando subconsultas ​

Variantes do método whereIn ​

whereNull ​

Variantes do método whereNull ​

whereExists ​

Variantes do método whereExists ​

whereBetween ​

Usando subconsultas ​

Usando consultas brutas ​

Variantes do método whereBetween ​

whereRaw ​

❌ Codificando valores do usuário diretamente ​

✅ Usando parâmetros de vinculação ​

Variantes do método whereRaw ​

whereJson ​

Variantes do método whereJson ​

whereJsonSuperset ​

Variantes do método whereJsonSuperset ​

whereJsonSubset ​

Variantes do método whereJsonSubset ​

join ​

joinRaw ​

Métodos On ​

onIn ​

onNotIn ​

onNull ​

onNotNull ​

onExists ​

onNotExists ​

onBetween ​

onNotBetween ​

having ​

havingRaw ​

Variantes do método having ​

distinct ​

groupBy ​

groupByRaw ​

orderBy ​

Usando subconsultas ​

orderByRaw ​

offset ​

limit ​

forPage ​

count ​

Outros métodos de agregação ​

union ​

Usando subconsultas ​

with ​

withMaterialized / withNotMaterialized ​

withRecursive ​

update ​

increment ​

decrement ​

delete ​

useTransaction ​

forUpdate ​

forShare ​

skipLocked ​

noWait ​

clone ​

debug ​

timeout ​

toSQL ​

Construtor de consultas

Métodos/propriedades

`select`

Aliases de coluna

Usando subconsultas

Usando consultas brutas

`from`

`where`

Grupos where

Usando subconsultas

Usando consultas brutas

Variantes do método `where`

`whereColumn`

Variantes do método `whereColumn`

`whereLike`

`whereILike`

`whereIn`

Usando subconsultas

Variantes do método `whereIn`

`whereNull`

Variantes do método `whereNull`

`whereExists`

Variantes do método `whereExists`

`whereBetween`

Usando subconsultas

Usando consultas brutas

Variantes do método `whereBetween`

`whereRaw`

❌ Codificando valores do usuário diretamente

✅ Usando parâmetros de vinculação

Variantes do método `whereRaw`

`whereJson`

Variantes do método `whereJson`

`whereJsonSuperset`

Variantes do método `whereJsonSuperset`

`whereJsonSubset`

Variantes do método `whereJsonSubset`

`join`

`joinRaw`

Métodos On

`onIn`

`onNotIn`

`onNull`

`onNotNull`

`onExists`

`onNotExists`

`onBetween`

`onNotBetween`

`having`

`havingRaw`

Variantes do método `having`

`distinct`

`groupBy`

`groupByRaw`

`orderBy`

Usando subconsultas

`orderByRaw`

`offset`

`limit`

`forPage`

`count`

Outros métodos de agregação

`union`

Usando subconsultas

`with`

`withMaterialized` / `withNotMaterialized`

`withRecursive`

`update`

`increment`

`decrement`

`delete`

`useTransaction`

`forUpdate`

`forShare`

`skipLocked`

`noWait`

`clone`

`debug`

`timeout`

`toSQL`