コンテンツにスキップ

Kysely Where

このコンテンツはまだ日本語訳がありません。

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