Orchid ORM Join方法

暮冬拾柒
45 阅读6分钟

选择关系

在执行表连接前，建议先评估选择关系是否能满足需求：

// 选择包含个人资料的用户（一对一关系）
// 结果类型为 Array<{ name: string, profile: Profile | null }>
await db.user.select('name', {
  profile: (q) => q.profile, // 隐式使用 LEFT JOIN LATERAL
});

// 选择包含评论计数的帖子，按评论数过滤排序
// 结果类型为 Array<Post & { commentsCount: number }>
await db.post
  .select('*', {
    commentsCount: (q) => q.comments.count(), // 聚合关联表数据
  })
  .where({ commentsCount: { gt: 10 } })
  .order({ commentsCount: 'DESC' });

// 选择包含书籍标题数组的作者（一对多关系）
// 结果类型为 Array<Author & { books: string[] }>
await db.author.select('*', {
  books: (q) => q.books.pluck('title'), // 提取关联表特定列
});

内部通过 LEFT JOIN LATERAL 实现关系连接：

  • 一对一关系中，若主表记录无匹配关联数据，对应属性为 NULL
  • 如需过滤无关联数据的主表记录，在关系调用中添加 .join()：
// 仅加载存在个人资料的用户（内连接）
await db.user.select('*', {
  profile: (q) => q.profile.join(), // 等价于 INNER JOIN
});

// 加载个人资料满足特定条件的用户
await db.user.select('*', {
  profile: (q) => q.profile.join().where({ age: { gt: 20 } }),
});

// 一对多关系中过滤无关联数据的主表记录
// 无标签的帖子不会被加载
await db.post.select('*', {
  tags: (q) => q.tags.join(), // 结果类型为 Array<Post & { tags: Tag[] }>
});

连接方法

join 系列方法支持连接其他表、预定义关系、CTE 表或子查询：

  • join/leftJoin 等方法参数一致，但返回类型不同（join 保证关联数据存在，leftJoin 允许关联字段为 null）

基础表结构定义示例：

// 用户表定义（含一对多关系）
export class UserTable extends BaseTable {
  readonly table = 'user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.text(),
  }));
  relations = {
    messages: this.hasMany(() => MessageTable, {
      columns: ['id'], // 主表关联列
      references: ['userId'], // 从表外键列
    }),
  };
}

// 消息表定义（含多对一关系）
export class MessageTable extends BaseTable {
  readonly table = 'message';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    userId: t.integer(), // 外键关联用户表
    text: t.text(),
    ...t.timestamps(),
  }));
  relations = {
    user: this.belongsTo(() => UserTable, {
      columns: ['userId'], // 从表关联列
      references: ['id'], // 主表主键列
    }),
  };
}

join：内连接（INNER JOIN）

join 方法对应 SQL JOIN（等同于 INNER JOIN），无匹配记录时跳过主表记录：

  • 重复连接相同表且条件完全一致时自动去重：
// 重复连接同一表（条件相同，生成单连接SQL）
db.post.join('comments').join('comments'); // 等价于单次join

// 带条件的重复连接（动态值导致无法去重）
db.post
  .join('comments', (q) => q.where({ rating: { gt: 5 } }))
  .join('comments', (q) => q.where({ rating: { gt: 5 } })); // 生成两次连接

关系连接（预定义关系）

通过预定义关系名称快速连接，支持在 where/select 中引用关联表字段：

// 通过关系名称连接并过滤关联表数据
const result = await db.user
  .join('messages') // 自动应用预定义关联条件
  .where({ 'messages.text': { startsWith: 'Hi' } }) // 关联表字段条件
  .select('name', 'messages.text'); // 显式指定关联表字段

// 结果类型：{ name: string; text: string }[]

支持通过回调配置连接别名、条件及查询方法：

// 为关联表设置别名并添加条件
const result = await db.user.join((q) =>
  q.messages.as('m').where({ text: 'some text' }), // 别名为m并过滤
);

// 自定义连接条件（补充预定义关系的ON条件）
const result = await db.user.join(
  (q) => q.messages.as('m'), // 关系连接
  (q) => q.on('messages.text', 'user.name'), // 额外的连接条件
);

选择完整关联记录

通过 .* 语法选择完整关联表记录：

// 选择书籍及其作者完整信息（一对一关系）
const result = await db.book.join('author').select('title', {
  author: 'author.*', // 选择作者表所有字段
});

// 结果类型：{ title: string; author: Author }[]

// 一对多关系中的重复数据（用户与多条消息）
const result = await db.user
  .join('messages')
  .select('name', { messages: 'messages.*' });

// 结果示例（用户数据随消息重复）：
// [
//   { name: 'user1', messages: { id: 1, text: 'msg1' } },
//   { name: 'user1', messages: { id: 2, text: 'msg2' } }
// ]

非关系表连接

手动连接未定义关系的表，需指定连接列：

// 显式指定连接列（用户表与消息表）
db.user
  .join(db.message, 'userId', 'user.id') // 外键列 vs 主键列
  .where({ 'message.text': { startsWith: 'Hi' } })
  .select('name', 'message.text');

// 为连接表设置别名以简化引用
db.user
  .join(db.message.as('m'), 'message.userId', 'user.id') // 别名m
  .where({ 'm.text': { startsWith: 'Hi' } })
  .select('name', 'm.text');

// 选择完整关联记录并设置别名
const result = await db.user
  .join(db.message.as('m'), 'message.userId', 'user.id')
  .select('name', { msg: 'm.*' }); // 关联记录别名为msg

支持自定义连接条件（运算符 / 原始 SQL / 多列匹配）：

// 使用自定义比较运算符
db.user.join(db.message, 'userId', '!=', 'user.id'); // 不等连接

// 基于原始SQL的全文匹配
db.user.join(
  db.message,
  sql`lower("message"."text")`, // 转换为小写比较
  'LIKE',
  sql`lower("user"."name") || '%'`,
);

// 多列连接条件（对象形式）
db.user.join(db.message, {
  'message.userId': 'user.id', // 外键列匹配
  text: sql`lower("user"."name")`, // 文本转换后匹配
});

// 无条件连接（返回所有组合）
db.user.join(db.message, true); // 笛卡尔积（谨慎使用）

通过回调构建复杂连接条件（支持 on/orOn 方法）：

// 组合多个连接条件
db.user.join(
  db.message,
  (q) => 
    q.on('message.userId', 'user.id') // 主键-外键匹配
     .orOn('message.text', 'user.name') // 文本内容匹配
     .where({ 'user.id': { in: [1, 2, 3] } }) // 主表条件
);

隐式 LATERAL 连接

当连接回调包含排序 / 限制等复杂逻辑时，自动生成 JOIN LATERAL：

// 获取用户的最后10条消息（需访问主表字段排序）
await db.user.join('messages', (q) => 
  q.order({ createdAt: 'DESC' }).limit(10) // 依赖主表字段的子查询
);

// 生成的SQL包含LATERAL关键字：
// SELECT "user".*
// FROM "user"
// JOIN LATERAL (
//   SELECT * FROM "message"
//   WHERE "message"."userId" = "user"."id"
//   ORDER BY "createdAt" DESC LIMIT 10
// ) "messages" ON true

joinLateral：显式横向连接

允许子查询引用主表字段，适用于依赖主表数据的关联查询：

// 连接子查询并设置别名
User.joinLateral(Message.as('m'), (q) => 
  q.select('text') // 选择特定列
   .on('authorId', 'user.id') // 关联条件
   .where({ 'user.name': 'Alice' }) // 引用主表字段
)
.select('id', 'name', 'm.text'); // 选择主表与关联表字段

// 结果类型：{ id: number; name: string; text: string | null }[]

leftJoin：左外连接

对应 SQL LEFT JOIN，无匹配记录时关联字段为 null：

// 左连接获取用户及其消息（包括无消息的用户）
const result = await db.user
  .leftJoin('messages') // 等价于 LEFT OUTER JOIN
  .select('name', 'messages.text');

// 结果类型：{ name: string; text: string | null }[]

leftJoinLateral：左横向连接

结合 LEFT JOIN 与 LATERAL，支持复杂子查询并允许关联字段为 null：

// 左连接子查询，保留无匹配的主表记录
const result = await db.user
  .leftJoinLateral('messages', (q) => q.as('message'))
  .select('name', 'message.text');

// 结果类型：{ name: string; text: string | null }[]

rightJoin：右外连接

对应 SQL RIGHT JOIN，以关联表为驱动表，主表字段可能为 null：

// 右连接获取消息及其用户（包括无用户的消息）
const result = await db.user
  .rightJoin('messages') 
  .select('name', 'messages.text');

// 结果类型：{ name: string | null; text: string }[]

fullJoin：全外连接

对应 SQL FULL JOIN，返回所有主表和关联表记录，无匹配字段为 null：

// 全连接获取用户与消息的所有组合
const result = await db.user
  .fullJoin('messages') 
  .select('name', 'messages.text');

// 结果类型：{ name: string | null; text: string | null }[]

onJsonPathEquals：JSON 路径连接

基于 JSON 列的路径表达式进行连接：

// 根据JSON字段匹配连接表
db.table.join(db.otherTable, (q) =>
  q.onJsonPathEquals(
    'otherTable.data', '$.key', // 关联表JSON路径
    'table.data', '$.key'       // 主表JSON路径
  )
);

joinData：数据组合插入

与 createManyFrom 配合生成笛卡尔积数据插入：

// 插入主表与数据数组的组合记录
const data = [{ column2: 'one' }, { column2: 'two' }];
await db.table.createManyFrom(
  db.otherTable
    .joinData('data', (t) => ({ column2: t.text() }), data) // 关联数据数组
    .select('otherTable.column1', 'data.column2')
);

// 若otherTable返回2条记录，data含3条，将插入2*3=6条记录
avatar
文章
阅读
粉丝