Select query builder

import db from '@adonisjs/lucid/services/db'

/**
 * Creates query builder instance
 */
const query = db.query()

/**
 * Creates query builder instance and also selects
 * the table
 */
const queryWithTableSelection = db.from('users')

import db from '@adonisjs/lucid/services/db'

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

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

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

    // Key is alias name
    userEmail: 'email'
  })

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

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

import db from '@adonisjs/lucid/services/db'

db.from('users')

import db from '@adonisjs/lucid/services/db'

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

import db from '@adonisjs/lucid/services/db'

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

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

db
  .from('users')
  .where('created_at', '>', DateTime.local().toSQLDate())

db
  .from('posts')
  .where('title', 'like', '%Adonis 101%')

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

db
  .from('users')
  .join('user_logins', (query) => {
    query
      .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')
      })
  })
  .select('users.*')
  .select('user_logins.ip_address')
  .select('user_logins.country')

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" > ?
  )

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

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

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

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

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

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

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

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

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

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

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

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

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

db.from('users').distinct()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

console.log(users[0].total)

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

console.log(users[0].total)

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

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

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

db
  .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)
*/

db
  .from('users')
  .whereNull('last_name')
  .union([
    (query) => {
      query.from('users').whereNull('first_name')
    },
    (query) => {
      query.from('users').whereNull('email')
    },
  ], true)

/**
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)
*/

db
  .from('users')
  .whereNull('last_name')
  .union([
    db.from('users').whereNull('first_name'),
    db.from('users').whereNull('email')
  ], true)

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

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

db
  .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"
*/

db
  .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"
*/

db
  .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')

db
  .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')

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

const rows = db
  .from('users')
  .where('id', 1)
  .update(
    { email: 'virk@adonisjs.com' },
    ['id', 'email'] // columns to return
  )

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

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

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

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

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

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

const trx = await db.transaction()

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

await trx.commit()

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

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

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

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

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

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

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

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

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

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

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

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

console.log(output)

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

console.log(output)

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

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

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

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

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

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

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

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

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

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

results.firstPage

results.perPage

results.currentPage

results.lastPage

results.total

@if(results.hasPages)

  {{-- Display pagination links --}}

@endif

results.hasMorePages

results.all()

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

// Assuming the current page is 2

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

// Assuming the current page is 2

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

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

results.toJSON()

/**
{
  meta: {
    total: 200,
    perPage: 20,
    currentPage: 1,
    firstPage: 1,
    lastPage: 20,
    ...
  },
  data: [
    {
    }
  ]
}
*/

results.baseUrl('/posts')

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

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

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

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

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

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

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

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

console.log(query.hasUnion) // true