选择至少有一个相关子行的父行

Drizzle | 选择至少有一个相关子行的父行

PostgreSQL

MySQL

SQLite

This guide assumes familiarity with:

本指南演示如何选择父行，条件是至少有一个相关的子行。下面是模式定义和相应的数据库数据示例：

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  userId: integer('user_id').notNull().references(() => users.id),
});

users.db

posts.db

+----+------------+----------------------+
| id |    name    |        email         |
+----+------------+----------------------+
|  1 | John Doe   | john_doe@email.com   |
+----+------------+----------------------+
|  2 | Tom Brown  | tom_brown@email.com  |
+----+------------+----------------------+
|  3 | Nick Smith | nick_smith@email.com |
+----+------------+----------------------+

+----+--------+-----------------------------+---------+
| id | title  |          content            | user_id |
+----+--------+-----------------------------+---------+
|  1 | Post 1 | This is the text of post 1  |       1 |
+----+--------+-----------------------------+---------+
|  2 | Post 2 | This is the text of post 2  |       1 |
+----+--------+-----------------------------+---------+
|  3 | Post 3 | This is the text of post 3  |       3 |
+----+--------+-----------------------------+---------+

要选择至少有一个相关子行的父行并检索子数据，可以使用 .innerJoin() 方法：

import { eq } from 'drizzle-orm';
import { users, posts } from './schema';

const db = drizzle(...);

await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.userId));
  .orderBy(users.id);

select users.*, posts.* from users
  inner join posts on users.id = posts.user_id
  order by users.id;

// 结果数据，ID为2的用户没有，因为他没有帖子
[
  {
    user: { id: 1, name: 'John Doe', email: 'john_doe@email.com' },
    post: {
      id: 1,
      title: 'Post 1',
      content: 'This is the text of post 1',
      userId: 1
    }
  },
  {
    user: { id: 1, name: 'John Doe', email: 'john_doe@email.com' },
    post: {
      id: 2,
      title: 'Post 2',
      content: 'This is the text of post 2',
      userId: 1
    }
  },
  {
    user: { id: 3, name: 'Nick Smith', email: 'nick_smith@email.com' },
    post: {
      id: 3,
      title: 'Post 3',
      content: 'This is the text of post 3',
      userId: 3
    }
  }
]

要仅选择至少有一个相关子行的父行，可以使用带有 exists() 函数的子查询，如下所示：

import { eq, exists, sql } from 'drizzle-orm';

const sq = db
  .select({ id: sql`1` })
  .from(posts)
  .where(eq(posts.userId, users.id));

await db.select().from(users).where(exists(sq));

select * from users where exists (select 1 from posts where posts.user_id = users.id);

// 结果数据，ID为2的用户没有，因为他没有帖子
[
  { id: 1, name: 'John Doe', email: 'john_doe@email.com' },
  { id: 3, name: 'Nick Smith', email: 'nick_smith@email.com' }
]