Drizzle ORM - 选择至少含有一个相关子行的父行

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

PostgreSQL

MySQL

SQLite

本指南假定您熟悉

本指南演示了如何选择至少含有一个相关子行的父行。下面是 schema 定义和相应数据库数据的示例

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   | [email protected]   |
+----+------------+----------------------+
|  2 | Tom Brown  | [email protected]  |
+----+------------+----------------------+
|  3 | Nick Smith | [email protected] |
+----+------------+----------------------+

+----+--------+-----------------------------+---------+
| 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;

// result data, there is no user with id 2 because he has no posts
[
  {
    user: { id: 1, name: 'John Doe', email: '[email protected]' },
    post: {
      id: 1,
      title: 'Post 1',
      content: 'This is the text of post 1',
      userId: 1
    }
  },
  {
    user: { id: 1, name: 'John Doe', email: '[email protected]' },
    post: {
      id: 2,
      title: 'Post 2',
      content: 'This is the text of post 2',
      userId: 1
    }
  },
  {
    user: { id: 3, name: 'Nick Smith', email: '[email protected]' },
    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);

// result data, there is no user with id 2 because he has no posts
[
  { id: 1, name: 'John Doe', email: '[email protected]' },
  { id: 3, name: 'Nick Smith', email: '[email protected]' }
]