连接 [SQL]

SQL 中的连接子句用于基于它们之间的相关列将 2 个或更多表组合在一起。 Drizzle ORM 连接语法是 SQL 相似性和类型安全性的平衡。

连接类型

Drizzle ORM 提供了 INNER JOIN、FULL JOIN、LEFT JOIN 和 RIGHT JOIN 的 API。让我们快速看一下基于以下表模式示例：

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const pets = pgTable('pets', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  ownerId: integer('owner_id').notNull().references(() => users.id),
})

左连接

const result = await db.select().from(users).leftJoin(pets, eq(users.id, pets.ownerId))

select ... from "users" left join "pets" on "users"."id" = "pets"."owner_id"

// result type
const result: {
    user: {
        id: number;
        name: string;
    };
    pets: {
        id: number;
        name: string;
        ownerId: number;
    } | null;
}[];

右连接

const result = await db.select().from(users).rightJoin(pets, eq(users.id, pets.ownerId))

select ... from "users" right join "pets" on "users"."id" = "pets"."owner_id"

// result type
const result: {
    user: {
        id: number;
        name: string;
    } | null;
    pets: {
        id: number;
        name: string;
        ownerId: number;
    };
}[];

内连接

const result = await db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId))

select ... from "users" inner join "pets" on "users"."id" = "pets"."owner_id"

// result type
const result: {
    user: {
        id: number;
        name: string;
    };
    pets: {
        id: number;
        name: string;
        ownerId: number;
    };
}[];

全连接

const result = await db.select().from(users).fullJoin(pets, eq(users.id, pets.ownerId))

select ... from "users" full join "pets" on "users"."id" = "pets"."owner_id"

// result type
const result: {
    user: {
        id: number;
        name: string;
    } | null;
    pets: {
        id: number;
        name: string;
        ownerId: number;
    } | null;
}[];

部分选择

如果您需要选择特定字段子集或需要平铺响应类型，Drizzle ORM 支持带部分选择的连接，并将自动推断返回类型基于 .select({ ... }) 结构。

await db.select({
  userId: users.id,
  petId: pets.id,
}).from(user).leftJoin(pets, eq(users.id, pets.ownerId))

select "users"."id", "pets"."id" from "users" left join "pets" on "users"."id" = "pets"."owner_id"

// result type
const result: {
  userId: number;
  petId: number | null;
}[];

您可能已经注意到 petId 现在可以是 null，这是因为我们进行了左连接，可能存在没有宠物的用户。

在使用 sql 运算符进行部分选择字段和聚合时，当需要时，您应该使用 sql<type | null> 以正确推断结果类型，这是您的责任！

const result = await db.select({
  userId: users.id,
  petId: pets.id,
  petName1: sql`upper(${pets.name})`,
  petName2: sql<string | null>`upper(${pets.name})`,
  //˄we should explicitly tell 'string | null' in type, since we're left joining that field
}).from(user).leftJoin(pets, eq(users.id, pets.ownerId))

select "users"."id", "pets"."id", upper("pets"."name")... from "users" left join "pets" on "users"."id" = "pets"."owner_id"

// result type
const result: {
  userId: number;
  petId: number | null;
  petName1: unknown;
  petName2: string | null;
}[];

为了在连接具有大量列的表时避免出现大量可空字段，我们可以使用我们的嵌套选择对象语法，我们的智能类型推断将使整个对象变为可空，而不是使所有表字段都变为可空！

await db.select({
  userId: users.id,
  userName: users.name,
  pet: {
    id: pets.id,
    name: pets.name,
    upperName: sql<string>`upper(${pets.name})`
  }
}).from(user).fullJoin(pets, eq(users.id, pets.ownerId))

select ... from "users" full join "pets" on "users"."id" = "pets"."owner_id"

// result type
const result: {
    userId: number | null;
    userName: string | null;
    pet: {
        id: number;
        name: string;
        upperName: string;
    } | null;
}[];

别名和自连接

Drizzle ORM 支持表别名，这在您需要进行自连接时非常有用。

假设您需要获取带有其父级用户的用户信息：

index.ts

schema.ts

import { user } from "./schema";

const parent = aliasedTable(user, "parent")
const result = db
  .select()
  .from(user)
  .leftJoin(parent, eq(parent.id, user.parentId));

select ... from "user" left join "user" "parent" on "parent"."id" = "user"."parent_id"

// result type
const result: {
    user: {
        id: number;
        name: string;
        parentId: number;
    };
    parent: {
        id: number;
        name: string;
        parentId: number;
    } | null;
}[];

export const user = pgTable("user", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  parentId: integer("parent_id").notNull().references((): AnyPgColumn => user.id)
});

聚合结果

Drizzle ORM 从驱动程序中提供名称映射的结果，而不改变结构。

您可以自由地以您想要的方式操作结果，这里是一个映射一对多关系数据的示例：

type User = typeof users.$inferSelect;
type Pet = typeof pets.$inferSelect;

const rows = db.select({
    user: users,
    pet: pets,
  }).from(users).leftJoin(pets, eq(users.id, pets.ownerId)).all();

const result = rows.reduce<Record<number, { user: User; pets: Pet[] }>>(
  (acc, row) => {
    const user = row.user;
    const pet = row.pet;

    if (!acc[user.id]) {
      acc[user.id] = { user, pets: [] };
    }

    if (pet) {
      acc[user.id].pets.push(pet);
    }

    return acc;
  },
  {}
);

// result type
const result: Record<number, {
    user: User;
    pets: Pet[];
}>;

一对多示例

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { drizzle } from 'drizzle-orm/better-sqlite3';

const cities = sqliteTable('cities', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
  cityId: integer('city_id').references(() => cities.id)
});

const db = drizzle(sqlite);

const result = db.select().from(cities).leftJoin(users, eq(cities.id, users.cityId)).all();

多对多示例

const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

const chatGroups = sqliteTable('chat_groups', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

const usersToChatGroups = sqliteTable('usersToChatGroups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => chatGroups.id),
});


// querying user group with id 1 and all the participants(users)
db.select()
  .from(usersToChatGroups)
  .leftJoin(users, eq(usersToChatGroups.userId, users.id))
  .leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id))
  .where(eq(chatGroups.id, 1))
  .all();

Become a Gold Sponsor