Bỏ qua nội dung
Saasfly Docs

Kysely Where

Nội dung này không tồn tại trong ngôn ngữ của bạn

Short and simple examples of how to use the where method to add a WHERE statement. While most of the examples show a SELECT query, the where method works exactly the same in UPDATE and DELETE queries too.

Simple where clause

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const person = await db
  .selectFrom('person')
  .selectAll()
  .where('first_name', '=', 'Jennifer')
  .where('age', '>', 40)
  .executeTakeFirst()

Result

SELECT
  *
FROM
  "person"
WHERE
  "first_name" = $1
  AND "age" > $2


-- Parameters
-- [1] Jennifer
-- [2] 40

Where in

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where('id', 'in', ['1', '2', '3'])
  .execute()

Result

SELECT
  *
FROM
  "person"
WHERE
  "id" IN ($1, $2, $3)


-- Parameters
-- [1] 1
-- [2] 2
-- [3] 3

Object filter

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const persons = await db
  .selectFrom('person')
  .selectAll()
  .where((eb) => eb.and({
    first_name: 'Jennifer',
    last_name: eb.ref('first_name')
  }))
  .execute()

Result

SELECT
  *
FROM
  "person"
WHERE
  (
    "first_name" = $1
    AND "last_name" = "first_name"
  )


-- Parameters
-- [1] Jennifer

OR where

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const persons = await db
  .selectFrom('person')
  .selectAll()
  // 1. Using the `or` method on the expression builder:
  .where((eb) => eb.or([
    eb('first_name', '=', 'Jennifer'),
    eb('first_name', '=', 'Sylvester')
  ]))
  // 2. Chaining expressions using the `or` method on the
  // created expressions:
  .where((eb) =>
    eb('last_name', '=', 'Aniston').or('last_name', '=', 'Stallone')
  )
  .execute()

Result

SELECT
  *
FROM
  "person"
WHERE
  (
    "first_name" = $1
    OR "first_name" = $2
  )
  AND (
    "last_name" = $3
    OR "last_name" = $4
  )


-- Parameters
-- [1] Jennifer
-- [2] Sylvester
-- [3] Aniston
-- [4] Stallone

Conditional where calls

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

import { Expression, SqlBool } from 'kysely'


const firstName: string | undefined = 'Jennifer'
const lastName: string | undefined = 'Aniston'
const under18 = true
const over60 = true


let query = db
  .selectFrom('person')
  .selectAll()


if (firstName) {
  // The query builder is immutable. Remember to reassign
  // the result back to the query variable.
  query = query.where('first_name', '=', firstName)
}


if (lastName) {
  query = query.where('last_name', '=', lastName)
}


if (under18 || over60) {
  // Conditional OR expressions can be added like this.
  query = query.where((eb) => {
    const ors: Expression<SqlBool>[] = []


    if (under18) {
      ors.push(eb('age', '<', 18))
    }


    if (over60) {
      ors.push(eb('age', '>', 60))
    }


    return eb.or(ors)
  })
}


const persons = await query.execute()

Result

SELECT
  *
FROM
  "person"
WHERE
  "first_name" = $1
  AND "last_name" = $2
  AND (
    "age" < $3
    OR "age" > $4
  )


-- Parameters
-- [1] Jennifer
-- [2] Aniston
-- [3] 18
-- [4] 60

Complex where clause

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

const firstName = 'Jennifer'
const maxAge = 60


const persons = await db
  .selectFrom('person')
  .selectAll('person')
  .where(({ eb, or, and, not, exists, selectFrom }) => and([
    or([
      eb('first_name', '=', firstName),
      eb('age', '<', maxAge)
    ]),
    not(exists(
      selectFrom('pet')
        .select('pet.id')
        .whereRef('pet.owner_id', '=', 'person.id')
    ))
  ]))
  .execute()

Result

SELECT
  "person".*
FROM
  "person"
WHERE
  (
    (
      "first_name" = $1
      OR "age" < $2
    )
    AND NOT EXISTS (
      SELECT
        "pet"."id"
      FROM
        "pet"
      WHERE
        "pet"."owner_id" = "person"."id"
    )
  )


-- Parameters
-- [1] Jennifer
-- [2] 60