联接 [SQL]

SQL中的联接子句用于基于两个或多个表之间的相关列组合它们。 Drizzle ORM 的联接语法在 SQL 语法和类型安全之间达成了平衡。

联接类型

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

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

export const pets = pgTable("pets", {
  id: serial().primaryKey(),
  name: text().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"

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    };
    pets: {
        id: number;
        name: string;
        ownerId: number;
    } | null;
}[];

左联接 Lateral

const subquery = db.select().from(pets).where(gte(users.age, 16)).as('userPets')
const result = await db.select().from(users).leftJoinLateral(subquery, sql`true`)

select ... from "users" left join lateral (select ... from "pets" where "users"."age" >= 16) "userPets" on true

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    };
    userPets: {
        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"

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    } | 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"

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    };
    pets: {
        id: number;
        name: string;
        ownerId: number;
    };
}[];

内联接 Lateral

const subquery = db.select().from(pets).where(gte(users.age, 16)).as('userPets')
const result = await db.select().from(users).innerJoinLateral(subquery, sql`true`)

select ... from "users" inner join lateral (select ... from "pets" where "users"."age" >= 16) "userPets" on true

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    };
    userPets: {
        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"

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    } | null;
    pets: {
        id: number;
        name: string;
        ownerId: number;
    } | null;
}[];

交叉联接

const result = await db.select().from(users).crossJoin(pets)

select ... from "users" cross join "pets"

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    };
    pets: {
        id: number;
        name: string;
        ownerId: number;
    };
}[];

交叉联接 Lateral

const subquery = db.select().from(pets).where(gte(users.age, 16)).as('userPets')
const result = await db.select().from(users).crossJoinLateral(subquery)

select ... from "users" cross join lateral (select ... from "pets" where "users"."age" >= 16) "userPets"

// 结果类型
const result: {
    users: {
        id: number;
        name: string;
        age: number | null;
    };
    userPets: {
        id: number;
        name: string;
        ownerId: number;
    };
}[];

部分选择

如果您需要选择特定字段的子集或希望有一个平坦的响应类型，Drizzle ORM 支持带有部分选择的联接，并会根据 .select({ ... }) 结构自动推断返回类型。

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

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

// 结果类型
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})`,
  //˄我们应该在类型中显式告诉 'string | null'，因为我们正在对该字段进行左联接
}).from(users).leftJoin(pets, eq(users.id, pets.ownerId))

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

// 结果类型
const result: {
  userId: number;
  petId: number | null;
  petName1: unknown;
  petName2: string | null;
}[];

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

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

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

// 结果类型
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";
import { alias } from "drizzle-orm/pg-core";

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

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

// 结果类型
const result: {
    user: {
        id: number;
        name: string;
        parentId: number;
    };
    parent: {
        id: number;
        name: string;
        parentId: number;
    } | null;
}[];

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

聚合结果

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

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

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

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

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

    if (typeof acc[user.id] === "undefined") {
      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 { pgTable, text, integer } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";

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

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

const db = drizzle(process.env.DATABASE_URL);

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

多对多示例

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

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

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

// 查询 id 为 1 的用户组以及所有参与者（用户）
await db.select()
  .from(usersToChatGroups)
  .leftJoin(users, eq(usersToChatGroups.userId, users.id))
  .leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id))
  .where(eq(chatGroups.id, 1));