跳转到内容
Saasfly Docs

Kysely Join

此内容尚不支持你的语言。

Examples of queries that use joins.

Simple inner join

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 result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  // `select` needs to come after the call to `innerJoin` so
  // that you can select from the joined table.
  .select(['person.id', 'pet.name as pet_name'])
  .execute()

Result

SELECT
  "person"."id",
  "pet"."name" AS "pet_name"
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"

Aliased inner join

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

await db.selectFrom('person')
  .innerJoin('pet as p', 'p.owner_id', 'person.id')
  .where('p.name', '=', 'Doggo')
  .selectAll()
  .execute()

Result

SELECT
  *
FROM
  "person"
  INNER JOIN "pet" AS "p" ON "p"."owner_id" = "person"."id"
WHERE
  "p"."name" = $1


-- Parameters
-- [1] Doggo

Complex join

You can provide a function as the second argument to get a join builder for creating more complex joins. The join builder has a bunch of on* methods for building the on clause of the join. There’s basically an equivalent for every where method (on, onRef etc.).

You can do all the same things with the on method that you can with the corresponding where method (like OR expressions for example). See the where method documentation for more examples.

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

await db.selectFrom('person')
  .innerJoin(
    'pet',
    (join) => join
      .onRef('pet.owner_id', '=', 'person.id')
      .on('pet.name', '=', 'Doggo')
      .on((eb) => eb.or([eb("person.age", ">", 18), eb("person.age", "<", 100)]))
  )
  .selectAll()
  .execute()

Result

SELECT
  *
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"
  AND "pet"."name" = $1
  AND (
    "person"."age" > $2
    OR "person"."age" < $3
  )


-- Parameters
-- [1] Doggo
-- [2] 18
-- [3] 100

Subquery join

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 result = await db.selectFrom('person')
  .innerJoin(
    (eb) => eb
      .selectFrom('pet')
      .select(['owner_id as owner', 'name'])
      .where('name', '=', 'Doggo')
      .as('doggos'),
    (join) => join
      .onRef('doggos.owner', '=', 'person.id'),
  )
  .selectAll('doggos')
  .execute()

Result

SELECT
  "doggos".*
FROM
  "person"
  INNER JOIN (
    SELECT
      "owner_id" AS "owner",
      "name"
    FROM
      "pet"
    WHERE
      "name" = $1
  ) AS "doggos" ON "doggos"."owner" = "person"."id"


-- Parameters
-- [1] Doggo