29d - ORM与查询优化

┌─────────────────────────────────────────────────────────────────┐
│              AI 辅助 ORM 开发能力矩阵                            │
├─────────────────────┬───────────────────────────────────────────┤
│  ✅ AI 擅长          │  ⚠️ AI 需要人工审查                       │
│                     │                                           │
│  • Schema → Model   │  • 复杂关联查询优化                        │
│    代码生成          │  • 分布式事务设计                           │
│  • CRUD 操作生成     │  • 连接池参数调优                           │
│  • 基础关联定义      │  • 缓存失效策略                             │
│  • 类型定义生成      │  • 读写分离路由                             │
│  • 索引建议          │                                           │
│  • N+1 检测         │                                           │
│  • 查询重写建议      │                                           │
├─────────────────────┼───────────────────────────────────────────┤
│  ❌ AI 容易犯错      │  🔴 AI 不应自主决策                        │
│                     │                                           │
│  • 过度 eager load  │  • 生产数据库索引变更                       │
│  • 忽略查询复杂度    │  • 大表 Schema 迁移                        │
│  • 不当使用原生 SQL  │  • 数据库引擎选择                           │
│  • 忽略并发场景      │  • 分库分表策略                             │
│  • 缓存一致性问题    │  • 备份与恢复策略                           │
└─────────────────────┴───────────────────────────────────────────┘

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  avatarUrl String?  @map("avatar_url")
  isActive  Boolean  @default(true) @map("is_active")
  role      Role     @default(USER)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
 
  posts     Post[]
  comments  Comment[]
  profile   Profile?
 
  @@map("users")
  @@index([email])
  @@index([createdAt])
}
 
model Post {
  id          String    @id @default(uuid())
  title       String
  content     String?
  slug        String    @unique
  published   Boolean   @default(false)
  publishedAt DateTime? @map("published_at")
  authorId    String    @map("author_id")
  categoryId  String?   @map("category_id")
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")
 
  author    User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  category  Category? @relation(fields: [categoryId], references: [id])
  comments  Comment[]
  tags      Tag[]
 
  @@map("posts")
  @@index([authorId])
  @@index([categoryId])
  @@index([publishedAt])
  @@index([slug])
}
 
model Category {
  id       String @id @default(uuid())
  name     String @unique
  slug     String @unique
  parentId String? @map("parent_id")
 
  parent   Category?  @relation("CategoryTree", fields: [parentId], references: [id])
  children Category[] @relation("CategoryTree")
  posts    Post[]
 
  @@map("categories")
}
 
model Comment {
  id        String   @id @default(uuid())
  content   String
  authorId  String   @map("author_id")
  postId    String   @map("post_id")
  parentId  String?  @map("parent_id")
  createdAt DateTime @default(now()) @map("created_at")
 
  author  User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  post    Post      @relation(fields: [postId], references: [id], onDelete: Cascade)
  parent  Comment?  @relation("CommentThread", fields: [parentId], references: [id])
  replies Comment[] @relation("CommentThread")
 
  @@map("comments")
  @@index([postId])
  @@index([authorId])
}
 
model Tag {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]
 
  @@map("tags")
}
 
model Profile {
  id     String  @id @default(uuid())
  bio    String?
  userId String  @unique @map("user_id")
 
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@map("profiles")
}
 
enum Role {
  USER
  ADMIN
  MODERATOR
}

# 生成 Prisma Client（自动生成类型安全的查询 API）
npx prisma generate
 
# 生成的类型文件位于 node_modules/.prisma/client/
# 包含所有模型的 TypeScript 类型定义

// src/repositories/user.repository.ts
import { PrismaClient, Prisma } from '@prisma/client';
 
const prisma = new PrismaClient();
 
// ✅ 基础 CRUD 操作（AI 可以完美生成）
export class UserRepository {
  // 创建用户（含关联 Profile）
  async create(data: {
    email: string;
    name: string;
    bio?: string;
  }) {
    return prisma.user.create({
      data: {
        email: data.email,
        name: data.name,
        profile: data.bio ? {
          create: { bio: data.bio }
        } : undefined,
      },
      include: {
        profile: true,
      },
    });
  }
 
  // 分页查询（含筛选和排序）
  async findMany(params: {
    page: number;
    pageSize: number;
    search?: string;
    role?: 'USER' | 'ADMIN' | 'MODERATOR';
    orderBy?: 'createdAt' | 'name' | 'email';
    order?: 'asc' | 'desc';
  }) {
    const { page, pageSize, search, role, orderBy = 'createdAt', order = 'desc' } = params;
 
    const where: Prisma.UserWhereInput = {
      isActive: true,
      ...(search && {
        OR: [
          { name: { contains: search, mode: 'insensitive' } },
          { email: { contains: search, mode: 'insensitive' } },
        ],
      }),
      ...(role && { role }),
    };
 
    const [users, total] = await Promise.all([
      prisma.user.findMany({
        where,
        skip: (page - 1) * pageSize,
        take: pageSize,
        orderBy: { [orderBy]: order },
        include: {
          profile: true,
          _count: { select: { posts: true, comments: true } },
        },
      }),
      prisma.user.count({ where }),
    ]);
 
    return {
      data: users,
      pagination: {
        page,
        pageSize,
        total,
        totalPages: Math.ceil(total / pageSize),
      },
    };
  }
 
  // 带关联的详情查询
  async findByIdWithPosts(id: string) {
    return prisma.user.findUnique({
      where: { id },
      include: {
        profile: true,
        posts: {
          where: { published: true },
          orderBy: { publishedAt: 'desc' },
          take: 10,
          include: {
            category: true,
            tags: true,
            _count: { select: { comments: true } },
          },
        },
      },
    });
  }
 
  // 事务操作：转移用户所有文章
  async transferPosts(fromUserId: string, toUserId: string) {
    return prisma.$transaction(async (tx) => {
      const posts = await tx.post.findMany({
        where: { authorId: fromUserId },
        select: { id: true },
      });
 
      await tx.post.updateMany({
        where: { authorId: fromUserId },
        data: { authorId: toUserId },
      });
 
      return { transferredCount: posts.length };
    });
  }
}

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';
import { withOptimize } from '@prisma/extension-optimize';
 
const prisma = new PrismaClient().$extends(
  withOptimize({
    apiKey: process.env.PRISMA_OPTIMIZE_API_KEY!,
  })
);
 
export default prisma;

┌─────────────────────────────────────────────────────────────────┐
│              Prisma Optimize 检测能力                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  🔍 N+1 查询检测                                                │
│  ├── 检测循环中的重复查询                                         │
│  ├── 建议使用 include/select 预加载                               │
│  └── 提供具体的代码修改建议                                       │
│                                                                 │
│  📊 缺失索引建议                                                 │
│  ├── 分析 WHERE 子句中的字段                                      │
│  ├── 检测 ORDER BY 未索引字段                                     │
│  └── 推荐复合索引组合                                             │
│                                                                 │
│  ⚡ 全表扫描告警                                                  │
│  ├── 检测缺少 WHERE 条件的大表查询                                 │
│  ├── 建议添加 LIMIT 限制                                          │
│  └── 标记潜在的性能瓶颈                                           │
│                                                                 │
│  📈 查询延迟分析                                                  │
│  ├── P50/P95/P99 延迟统计                                        │
│  ├── 查询分组和模式识别                                            │
│  └── 历史趋势对比                                                 │
│                                                                 │
│  🔄 不必要的数据获取                                               │
│  ├── 检测 SELECT * 模式                                           │
│  ├── 建议使用 select 精确选择字段                                   │
│  └── 减少网络传输和内存占用                                         │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

// ❌ N+1 问题：循环中逐个查询作者信息
async function getPostsWithAuthors_BAD() {
  const posts = await prisma.post.findMany({
    where: { published: true },
    take: 20,
  });
 
  // N+1：每篇文章都会触发一次额外查询获取作者
  const postsWithAuthors = await Promise.all(
    posts.map(async (post) => {
      const author = await prisma.user.findUnique({
        where: { id: post.authorId },
      });
      return { ...post, author };
    })
  );
 
  return postsWithAuthors;
}
// 总查询数：1（获取文章）+ 20（获取每个作者）= 21 次查询！
 
// ✅ 修复方案 1：使用 include 预加载
async function getPostsWithAuthors_GOOD_1() {
  return prisma.post.findMany({
    where: { published: true },
    take: 20,
    include: {
      author: {
        select: { id: true, name: true, avatarUrl: true },
      },
    },
  });
}
// 总查询数：1 次（Prisma 自动 JOIN 或批量查询）
 
// ✅ 修复方案 2：使用 select 精确控制返回字段
async function getPostsWithAuthors_GOOD_2() {
  return prisma.post.findMany({
    where: { published: true },
    take: 20,
    select: {
      id: true,
      title: true,
      slug: true,
      publishedAt: true,
      author: {
        select: { id: true, name: true, avatarUrl: true },
      },
      _count: { select: { comments: true } },
    },
  });
}
// 总查询数：1 次，且只返回需要的字段
 
// ✅ 修复方案 3：复杂场景使用 $queryRaw
async function getPostStats() {
  return prisma.$queryRaw`
    SELECT
      p.id,
      p.title,
      u.name AS author_name,
      COUNT(c.id) AS comment_count,
      COUNT(DISTINCT t.id) AS tag_count
    FROM posts p
    JOIN users u ON p.author_id = u.id
    LEFT JOIN comments c ON c.post_id = p.id
    LEFT JOIN "_PostToTag" pt ON pt."A" = p.id
    LEFT JOIN tags t ON t.id = pt."B"
    WHERE p.published = true
    GROUP BY p.id, p.title, u.name
    ORDER BY comment_count DESC
    LIMIT 10
  `;
}

你是一位 Prisma ORM 专家。请根据以下需求生成高性能的 Prisma 查询代码。

## Prisma Schema
[粘贴相关的 schema.prisma 模型定义]

## 查询需求
[描述需要实现的查询功能]

## 性能要求
- 避免 N+1 查询（使用 include 或 select 预加载关联数据）
- 只返回必要的字段（使用 select 而非返回整个模型）
- 大列表查询必须分页（使用 skip/take）
- 复杂聚合考虑使用 $queryRaw
- 写操作使用事务（$transaction）保证一致性

## 输出要求
1. TypeScript 代码（含完整类型定义）
2. 预估查询次数和复杂度
3. 建议的索引（如果当前 Schema 缺少）
4. 潜在的性能风险和优化建议

你是一位数据库性能专家。请审查以下 Prisma 查询代码的性能问题。

## 代码
[粘贴 Prisma 查询代码]

## Schema
[粘贴相关 Schema]

## 数据规模
- [表名] 约 [行数] 行
- 日均查询量：[次数]

## 审查清单
1. 是否存在 N+1 查询？
2. 是否有不必要的数据获取（SELECT *）？
3. 是否缺少分页？
4. 关联查询是否可以优化？
5. 是否需要添加索引？
6. 是否应该使用 $queryRaw 替代 ORM 查询？
7. 事务使用是否合理？

## 输出
- 🔴 严重问题（必须修复）
- 🟡 性能隐患（建议修复）
- 🟢 可接受
- 每个问题提供修复后的代码

// src/db/schema.ts
import {
  pgTable, uuid, varchar, text, boolean, timestamp,
  integer, pgEnum, index, uniqueIndex, primaryKey
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
// 枚举定义
export const roleEnum = pgEnum('role', ['USER', 'ADMIN', 'MODERATOR']);
 
// 用户表
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),
  avatarUrl: text('avatar_url'),
  isActive: boolean('is_active').notNull().default(true),
  role: roleEnum('role').notNull().default('USER'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
}, (table) => [
  index('users_email_idx').on(table.email),
  index('users_created_at_idx').on(table.createdAt),
]);
 
// 文章表
export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  slug: varchar('slug', { length: 255 }).notNull().unique(),
  published: boolean('published').notNull().default(false),
  publishedAt: timestamp('published_at'),
  authorId: uuid('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  categoryId: uuid('category_id').references(() => categories.id),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
}, (table) => [
  index('posts_author_id_idx').on(table.authorId),
  index('posts_category_id_idx').on(table.categoryId),
  index('posts_published_at_idx').on(table.publishedAt),
  uniqueIndex('posts_slug_idx').on(table.slug),
]);
 
// 分类表（自引用树形结构）
export const categories = pgTable('categories', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 100 }).notNull().unique(),
  slug: varchar('slug', { length: 100 }).notNull().unique(),
  parentId: uuid('parent_id'),
});
 
// 评论表
export const comments = pgTable('comments', {
  id: uuid('id').primaryKey().defaultRandom(),
  content: text('content').notNull(),
  authorId: uuid('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  postId: uuid('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  parentId: uuid('parent_id'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
}, (table) => [
  index('comments_post_id_idx').on(table.postId),
  index('comments_author_id_idx').on(table.authorId),
]);
 
// 标签表（多对多）
export const tags = pgTable('tags', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 50 }).notNull().unique(),
});
 
export const postsToTags = pgTable('posts_to_tags', {
  postId: uuid('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: uuid('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
}, (table) => [
  primaryKey({ columns: [table.postId, table.tagId] }),
]);
 
// 用户资料表
export const profiles = pgTable('profiles', {
  id: uuid('id').primaryKey().defaultRandom(),
  bio: text('bio'),
  userId: uuid('user_id').notNull().unique().references(() => users.id, { onDelete: 'cascade' }),
});

// src/db/relations.ts
import { relations } from 'drizzle-orm';
import { users, posts, comments, tags, postsToTags, profiles, categories } from './schema';
 
export const usersRelations = relations(users, ({ one, many }) => ({
  profile: one(profiles, {
    fields: [users.id],
    references: [profiles.userId],
  }),
  posts: many(posts),
  comments: many(comments),
}));
 
export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
  category: one(categories, {
    fields: [posts.categoryId],
    references: [categories.id],
  }),
  comments: many(comments),
  postsToTags: many(postsToTags),
}));
 
export const commentsRelations = relations(comments, ({ one }) => ({
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
  }),
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
  }),
}));
 
export const postsToTagsRelations = relations(postsToTags, ({ one }) => ({
  post: one(posts, {
    fields: [postsToTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postsToTags.tagId],
    references: [tags.id],
  }),
}));
 
export const tagsRelations = relations(tags, ({ many }) => ({
  postsToTags: many(postsToTags),
}));
 
export const categoriesRelations = relations(categories, ({ many }) => ({
  posts: many(posts),
}));
 
export const profilesRelations = relations(profiles, ({ one }) => ({
  user: one(users, {
    fields: [profiles.userId],
    references: [users.id],
  }),
}));

// src/repositories/post.repository.ts
import { db } from '../db';
import { posts, users, comments, tags, postsToTags, categories } from '../db/schema';
import { eq, and, or, like, desc, asc, sql, count, ilike, isNotNull } from 'drizzle-orm';
 
// ✅ 关系查询 API（推荐：自动处理 JOIN，避免 N+1）
async function getPostsWithRelations() {
  return db.query.posts.findMany({
    where: eq(posts.published, true),
    limit: 20,
    orderBy: [desc(posts.publishedAt)],
    with: {
      author: {
        columns: { id: true, name: true, avatarUrl: true },
      },
      category: {
        columns: { id: true, name: true, slug: true },
      },
      comments: {
        columns: { id: true },
        // 只计数，不返回完整评论
      },
      postsToTags: {
        with: {
          tag: true,
        },
      },
    },
    columns: {
      id: true,
      title: true,
      slug: true,
      publishedAt: true,
    },
  });
}
 
// ✅ SQL-like 查询构建器（适合复杂查询）
async function getPostStats() {
  return db
    .select({
      postId: posts.id,
      title: posts.title,
      authorName: users.name,
      commentCount: count(comments.id).as('comment_count'),
    })
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .leftJoin(comments, eq(comments.postId, posts.id))
    .where(eq(posts.published, true))
    .groupBy(posts.id, posts.title, users.name)
    .orderBy(desc(sql`comment_count`))
    .limit(10);
}
 
// ✅ 分页查询（含搜索和筛选）
async function searchPosts(params: {
  search?: string;
  categoryId?: string;
  page: number;
  pageSize: number;
}) {
  const { search, categoryId, page, pageSize } = params;
 
  const conditions = [eq(posts.published, true)];
 
  if (search) {
    conditions.push(
      or(
        ilike(posts.title, `%${search}%`),
        ilike(posts.content, `%${search}%`)
      )!
    );
  }
 
  if (categoryId) {
    conditions.push(eq(posts.categoryId, categoryId));
  }
 
  const whereClause = and(...conditions);
 
  const [data, totalResult] = await Promise.all([
    db.query.posts.findMany({
      where: whereClause,
      limit: pageSize,
      offset: (page - 1) * pageSize,
      orderBy: [desc(posts.publishedAt)],
      with: {
        author: { columns: { id: true, name: true } },
        category: { columns: { id: true, name: true } },
      },
    }),
    db.select({ count: count() }).from(posts).where(whereClause),
  ]);
 
  return {
    data,
    pagination: {
      page,
      pageSize,
      total: totalResult[0].count,
      totalPages: Math.ceil(totalResult[0].count / pageSize),
    },
  };
}
 
// ✅ 子查询和 CTE
async function getActiveAuthorsWithPostCount() {
  const postCountSubquery = db
    .select({
      authorId: posts.authorId,
      postCount: count(posts.id).as('post_count'),
    })
    .from(posts)
    .where(eq(posts.published, true))
    .groupBy(posts.authorId)
    .as('post_counts');
 
  return db
    .select({
      userId: users.id,
      userName: users.name,
      email: users.email,
      postCount: postCountSubquery.postCount,
    })
    .from(users)
    .innerJoin(postCountSubquery, eq(users.id, postCountSubquery.authorId))
    .where(eq(users.isActive, true))
    .orderBy(desc(postCountSubquery.postCount));
}
 
// ✅ 事务操作
async function createPostWithTags(data: {
  title: string;
  content: string;
  slug: string;
  authorId: string;
  categoryId?: string;
  tagIds: string[];
}) {
  return db.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values({
      title: data.title,
      content: data.content,
      slug: data.slug,
      authorId: data.authorId,
      categoryId: data.categoryId,
    }).returning();
 
    if (data.tagIds.length > 0) {
      await tx.insert(postsToTags).values(
        data.tagIds.map((tagId) => ({
          postId: post.id,
          tagId,
        }))
      );
    }
 
    return post;
  });
}

// ❌ N+1 问题：使用 select 构建器时手动循环查询
async function getPostsWithAuthors_BAD() {
  const allPosts = await db.select().from(posts).where(eq(posts.published, true)).limit(20);
 
  // N+1：每篇文章单独查询作者
  const result = await Promise.all(
    allPosts.map(async (post) => {
      const [author] = await db
        .select({ name: users.name, avatarUrl: users.avatarUrl })
        .from(users)
        .where(eq(users.id, post.authorId));
      return { ...post, author };
    })
  );
  return result;
}
// 总查询：1 + 20 = 21 次
 
// ✅ 修复方案 1：使用 JOIN
async function getPostsWithAuthors_GOOD_1() {
  return db
    .select({
      post: posts,
      authorName: users.name,
      authorAvatar: users.avatarUrl,
    })
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .where(eq(posts.published, true))
    .limit(20);
}
// 总查询：1 次
 
// ✅ 修复方案 2：使用关系查询 API
async function getPostsWithAuthors_GOOD_2() {
  return db.query.posts.findMany({
    where: eq(posts.published, true),
    limit: 20,
    with: {
      author: {
        columns: { name: true, avatarUrl: true },
      },
    },
  });
}
// 总查询：Drizzle 自动优化为最少查询次数

# app/models/base.py
from datetime import datetime
from uuid import uuid4
from sqlalchemy import String, Text, Boolean, DateTime, ForeignKey, Index, Enum as SAEnum
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column, relationship,
    MappedAsDataclass
)
import enum
 
 
class Base(DeclarativeBase):
    pass
 
 
class Role(str, enum.Enum):
    USER = "USER"
    ADMIN = "ADMIN"
    MODERATOR = "MODERATOR"

# app/models/user.py
from __future__ import annotations
from typing import Optional, List
from sqlalchemy import String, Text, Boolean, DateTime, Index
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from datetime import datetime
from uuid import uuid4
from .base import Base, Role
 
 
class User(Base):
    __tablename__ = "users"
 
    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4())
    )
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    avatar_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    role: Mapped[Role] = mapped_column(
        SAEnum(Role, name="role"), default=Role.USER, nullable=False
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False
    )
 
    # 关系
    profile: Mapped[Optional["Profile"]] = relationship(
        back_populates="user", cascade="all, delete-orphan", uselist=False
    )
    posts: Mapped[List["Post"]] = relationship(
        back_populates="author", cascade="all, delete-orphan"
    )
    comments: Mapped[List["Comment"]] = relationship(
        back_populates="author", cascade="all, delete-orphan"
    )
 
    __table_args__ = (
        Index("ix_users_email", "email"),
        Index("ix_users_created_at", "created_at"),
    )
 
    def __repr__(self) -> str:
        return f"<User(id={self.id}, email={self.email})>"

# app/models/post.py
from __future__ import annotations
from typing import Optional, List
from sqlalchemy import String, Text, Boolean, DateTime, ForeignKey, Index, Table, Column
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from datetime import datetime
from uuid import uuid4
from .base import Base
 
 
# 多对多关联表
posts_tags = Table(
    "posts_tags",
    Base.metadata,
    Column("post_id", UUID(as_uuid=False), ForeignKey("posts.id", ondelete="CASCADE"), primary_key=True),
    Column("tag_id", UUID(as_uuid=False), ForeignKey("tags.id", ondelete="CASCADE"), primary_key=True),
)
 
 
class Post(Base):
    __tablename__ = "posts"
 
    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4())
    )
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    content: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    slug: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    published: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    published_at: Mapped[Optional[datetime]] = mapped_column(DateTime, nullable=True)
    author_id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )
    category_id: Mapped[Optional[str]] = mapped_column(
        UUID(as_uuid=False), ForeignKey("categories.id"), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False
    )
 
    # 关系
    author: Mapped["User"] = relationship(back_populates="posts")
    category: Mapped[Optional["Category"]] = relationship(back_populates="posts")
    comments: Mapped[List["Comment"]] = relationship(
        back_populates="post", cascade="all, delete-orphan"
    )
    tags: Mapped[List["Tag"]] = relationship(
        secondary=posts_tags, back_populates="posts"
    )
 
    __table_args__ = (
        Index("ix_posts_author_id", "author_id"),
        Index("ix_posts_category_id", "category_id"),
        Index("ix_posts_published_at", "published_at"),
        Index("ix_posts_slug", "slug"),
    )
 
 
class Category(Base):
    __tablename__ = "categories"
 
    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4())
    )
    name: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    slug: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    parent_id: Mapped[Optional[str]] = mapped_column(
        UUID(as_uuid=False), ForeignKey("categories.id"), nullable=True
    )
 
    parent: Mapped[Optional["Category"]] = relationship(
        remote_side="Category.id", back_populates="children"
    )
    children: Mapped[List["Category"]] = relationship(back_populates="parent")
    posts: Mapped[List["Post"]] = relationship(back_populates="category")
 
 
class Comment(Base):
    __tablename__ = "comments"
 
    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4())
    )
    content: Mapped[str] = mapped_column(Text, nullable=False)
    author_id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )
    post_id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), ForeignKey("posts.id", ondelete="CASCADE"), nullable=False
    )
    parent_id: Mapped[Optional[str]] = mapped_column(
        UUID(as_uuid=False), ForeignKey("comments.id"), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow, nullable=False
    )
 
    author: Mapped["User"] = relationship(back_populates="comments")
    post: Mapped["Post"] = relationship(back_populates="comments")
 
    __table_args__ = (
        Index("ix_comments_post_id", "post_id"),
        Index("ix_comments_author_id", "author_id"),
    )
 
 
class Tag(Base):
    __tablename__ = "tags"
 
    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4())
    )
    name: Mapped[str] = mapped_column(String(50), unique=True, nullable=False)
 
    posts: Mapped[List["Post"]] = relationship(
        secondary=posts_tags, back_populates="tags"
    )
 
 
class Profile(Base):
    __tablename__ = "profiles"
 
    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4())
    )
    bio: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    user_id: Mapped[str] = mapped_column(
        UUID(as_uuid=False), ForeignKey("users.id", ondelete="CASCADE"),
        unique=True, nullable=False
    )
 
    user: Mapped["User"] = relationship(back_populates="profile")

# app/repositories/post_repository.py
from sqlalchemy import select, func, and_, or_, desc
from sqlalchemy.orm import selectinload, joinedload, subqueryload, load_only
from sqlalchemy.ext.asyncio import AsyncSession
from app.models.post import Post, Comment, Tag, Category
from app.models.user import User
 
 
class PostRepository:
    def __init__(self, session: AsyncSession):
        self.session = session
 
    # ✅ 使用 selectinload 避免 N+1（推荐用于一对多关系）
    async def get_posts_with_authors(self, limit: int = 20):
        stmt = (
            select(Post)
            .where(Post.published == True)
            .options(
                joinedload(Post.author).load_only(User.id, User.name, User.avatar_url),
                selectinload(Post.tags),
                joinedload(Post.category),
            )
            .order_by(desc(Post.published_at))
            .limit(limit)
        )
        result = await self.session.execute(stmt)
        return result.scalars().unique().all()
 
    # ✅ 分页查询（含搜索和筛选）
    async def search_posts(
        self,
        page: int = 1,
        page_size: int = 20,
        search: str | None = None,
        category_id: str | None = None,
    ):
        conditions = [Post.published == True]
 
        if search:
            conditions.append(
                or_(
                    Post.title.ilike(f"%{search}%"),
                    Post.content.ilike(f"%{search}%"),
                )
            )
 
        if category_id:
            conditions.append(Post.category_id == category_id)
 
        where_clause = and_(*conditions)
 
        # 数据查询
        data_stmt = (
            select(Post)
            .where(where_clause)
            .options(
                joinedload(Post.author).load_only(User.id, User.name),
                joinedload(Post.category).load_only(Category.id, Category.name),
            )
            .order_by(desc(Post.published_at))
            .offset((page - 1) * page_size)
            .limit(page_size)
        )
 
        # 计数查询
        count_stmt = select(func.count(Post.id)).where(where_clause)
 
        data_result = await self.session.execute(data_stmt)
        count_result = await self.session.execute(count_stmt)
 
        posts = data_result.scalars().unique().all()
        total = count_result.scalar_one()
 
        return {
            "data": posts,
            "pagination": {
                "page": page,
                "page_size": page_size,
                "total": total,
                "total_pages": (total + page_size - 1) // page_size,
            },
        }
 
    # ✅ 聚合查询（使用原生 SQL 表达式）
    async def get_post_stats(self):
        stmt = (
            select(
                Post.id,
                Post.title,
                User.name.label("author_name"),
                func.count(Comment.id).label("comment_count"),
            )
            .join(User, Post.author_id == User.id)
            .outerjoin(Comment, Comment.post_id == Post.id)
            .where(Post.published == True)
            .group_by(Post.id, Post.title, User.name)
            .order_by(desc(func.count(Comment.id)))
            .limit(10)
        )
        result = await self.session.execute(stmt)
        return result.all()

# ❌ N+1 问题：默认 lazy loading
async def get_posts_bad(session: AsyncSession):
    stmt = select(Post).where(Post.published == True).limit(20)
    result = await session.execute(stmt)
    posts = result.scalars().all()
 
    # 每次访问 post.author 都会触发一次额外查询！
    for post in posts:
        print(f"{post.title} by {post.author.name}")  # N+1！
    return posts
 
# ✅ 修复方案 1：joinedload（LEFT JOIN，适合一对一/多对一）
async def get_posts_good_1(session: AsyncSession):
    stmt = (
        select(Post)
        .where(Post.published == True)
        .options(joinedload(Post.author))
        .limit(20)
    )
    result = await session.execute(stmt)
    posts = result.scalars().unique().all()
 
    for post in posts:
        print(f"{post.title} by {post.author.name}")  # 无额外查询
    return posts
 
# ✅ 修复方案 2：selectinload（SELECT IN，适合一对多）
async def get_posts_good_2(session: AsyncSession):
    stmt = (
        select(Post)
        .where(Post.published == True)
        .options(
            joinedload(Post.author),        # 多对一：用 JOIN
            selectinload(Post.comments),     # 一对多：用 SELECT IN
            selectinload(Post.tags),         # 多对多：用 SELECT IN
        )
        .limit(20)
    )
    result = await session.execute(stmt)
    return result.scalars().unique().all()
 
# ✅ 修复方案 3：subqueryload（子查询，适合大数据集）
async def get_posts_good_3(session: AsyncSession):
    stmt = (
        select(Post)
        .where(Post.published == True)
        .options(
            subqueryload(Post.comments),  # 使用子查询加载
        )
        .limit(20)
    )
    result = await session.execute(stmt)
    return result.scalars().all()

// src/entities/User.ts
import {
  Entity, PrimaryGeneratedColumn, Column, CreateDateColumn,
  UpdateDateColumn, Index, OneToMany, OneToOne, JoinColumn
} from 'typeorm';
import { Post } from './Post';
import { Comment } from './Comment';
import { Profile } from './Profile';
 
export enum Role {
  USER = 'USER',
  ADMIN = 'ADMIN',
  MODERATOR = 'MODERATOR',
}
 
@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;
 
  @Column({ type: 'varchar', length: 255, unique: true })
  @Index('idx_users_email')
  email: string;
 
  @Column({ type: 'varchar', length: 100 })
  name: string;
 
  @Column({ type: 'text', nullable: true, name: 'avatar_url' })
  avatarUrl: string | null;
 
  @Column({ type: 'boolean', default: true, name: 'is_active' })
  isActive: boolean;
 
  @Column({ type: 'enum', enum: Role, default: Role.USER })
  role: Role;
 
  @CreateDateColumn({ name: 'created_at' })
  @Index('idx_users_created_at')
  createdAt: Date;
 
  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
 
  @OneToMany(() => Post, (post) => post.author, { cascade: true })
  posts: Post[];
 
  @OneToMany(() => Comment, (comment) => comment.author, { cascade: true })
  comments: Comment[];
 
  @OneToOne(() => Profile, (profile) => profile.user, { cascade: true })
  profile: Profile;
}

// src/entities/Post.ts
import {
  Entity, PrimaryGeneratedColumn, Column, CreateDateColumn,
  UpdateDateColumn, Index, ManyToOne, OneToMany, ManyToMany,
  JoinColumn, JoinTable
} from 'typeorm';
import { User } from './User';
import { Category } from './Category';
import { Comment } from './Comment';
import { Tag } from './Tag';
 
@Entity('posts')
export class Post {
  @PrimaryGeneratedColumn('uuid')
  id: string;
 
  @Column({ type: 'varchar', length: 255 })
  title: string;
 
  @Column({ type: 'text', nullable: true })
  content: string | null;
 
  @Column({ type: 'varchar', length: 255, unique: true })
  @Index('idx_posts_slug')
  slug: string;
 
  @Column({ type: 'boolean', default: false })
  published: boolean;
 
  @Column({ type: 'timestamp', nullable: true, name: 'published_at' })
  @Index('idx_posts_published_at')
  publishedAt: Date | null;
 
  @Column({ name: 'author_id' })
  @Index('idx_posts_author_id')
  authorId: string;
 
  @Column({ name: 'category_id', nullable: true })
  @Index('idx_posts_category_id')
  categoryId: string | null;
 
  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;
 
  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
 
  @ManyToOne(() => User, (user) => user.posts, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'author_id' })
  author: User;
 
  @ManyToOne(() => Category, (category) => category.posts)
  @JoinColumn({ name: 'category_id' })
  category: Category | null;
 
  @OneToMany(() => Comment, (comment) => comment.post, { cascade: true })
  comments: Comment[];
 
  @ManyToMany(() => Tag, (tag) => tag.posts, { cascade: true })
  @JoinTable({
    name: 'posts_tags',
    joinColumn: { name: 'post_id', referencedColumnName: 'id' },
    inverseJoinColumn: { name: 'tag_id', referencedColumnName: 'id' },
  })
  tags: Tag[];
}

// src/repositories/PostRepository.ts
import { Repository, DataSource, Brackets } from 'typeorm';
import { Post } from '../entities/Post';
 
export class PostRepository {
  private repo: Repository<Post>;
 
  constructor(dataSource: DataSource) {
    this.repo = dataSource.getRepository(Post);
  }
 
  // ✅ 使用 QueryBuilder 避免 N+1
  async getPostsWithRelations(limit: number = 20) {
    return this.repo
      .createQueryBuilder('post')
      .leftJoinAndSelect('post.author', 'author')
      .leftJoinAndSelect('post.category', 'category')
      .leftJoinAndSelect('post.tags', 'tag')
      .loadRelationCountAndMap('post.commentCount', 'post.comments')
      .where('post.published = :published', { published: true })
      .orderBy('post.publishedAt', 'DESC')
      .take(limit)
      .getMany();
  }
 
  // ✅ 分页搜索查询
  async searchPosts(params: {
    search?: string;
    categoryId?: string;
    page: number;
    pageSize: number;
  }) {
    const { search, categoryId, page, pageSize } = params;
 
    const qb = this.repo
      .createQueryBuilder('post')
      .leftJoinAndSelect('post.author', 'author')
      .leftJoinAndSelect('post.category', 'category')
      .where('post.published = :published', { published: true });
 
    if (search) {
      qb.andWhere(
        new Brackets((sub) => {
          sub
            .where('post.title ILIKE :search', { search: `%${search}%` })
            .orWhere('post.content ILIKE :search', { search: `%${search}%` });
        })
      );
    }
 
    if (categoryId) {
      qb.andWhere('post.categoryId = :categoryId', { categoryId });
    }
 
    const [data, total] = await qb
      .orderBy('post.publishedAt', 'DESC')
      .skip((page - 1) * pageSize)
      .take(pageSize)
      .getManyAndCount();
 
    return {
      data,
      pagination: {
        page,
        pageSize,
        total,
        totalPages: Math.ceil(total / pageSize),
      },
    };
  }
 
  // ✅ 聚合查询
  async getPostStats() {
    return this.repo
      .createQueryBuilder('post')
      .select('post.id', 'postId')
      .addSelect('post.title', 'title')
      .addSelect('author.name', 'authorName')
      .addSelect('COUNT(comment.id)', 'commentCount')
      .innerJoin('post.author', 'author')
      .leftJoin('post.comments', 'comment')
      .where('post.published = :published', { published: true })
      .groupBy('post.id')
      .addGroupBy('post.title')
      .addGroupBy('author.name')
      .orderBy('COUNT(comment.id)', 'DESC')
      .limit(10)
      .getRawMany();
  }
 
  // ✅ 事务操作
  async createPostWithTags(
    dataSource: DataSource,
    data: {
      title: string;
      content: string;
      slug: string;
      authorId: string;
      categoryId?: string;
      tagIds: string[];
    }
  ) {
    return dataSource.transaction(async (manager) => {
      const post = manager.create(Post, {
        title: data.title,
        content: data.content,
        slug: data.slug,
        authorId: data.authorId,
        categoryId: data.categoryId,
      });
 
      const savedPost = await manager.save(post);
 
      if (data.tagIds.length > 0) {
        await manager
          .createQueryBuilder()
          .relation(Post, 'tags')
          .of(savedPost.id)
          .add(data.tagIds);
      }
 
      return savedPost;
    });
  }
}

// ❌ N+1 问题：使用 find 时未指定 relations
async function getPostsBad(repo: Repository<Post>) {
  const posts = await repo.find({
    where: { published: true },
    take: 20,
  });
 
  // 每次访问 post.author 都会触发 lazy loading（如果配置了 lazy）
  // 或者返回 undefined（如果没配置 lazy）
  for (const post of posts) {
    console.log(post.author?.name); // 可能是 undefined 或触发 N+1
  }
}
 
// ✅ 修复方案 1：使用 relations 选项
async function getPostsGood1(repo: Repository<Post>) {
  return repo.find({
    where: { published: true },
    take: 20,
    relations: {
      author: true,
      category: true,
      tags: true,
    },
    select: {
      id: true,
      title: true,
      slug: true,
      publishedAt: true,
      author: { id: true, name: true, avatarUrl: true },
      category: { id: true, name: true },
    },
  });
}
 
// ✅ 修复方案 2：使用 QueryBuilder（更灵活）
async function getPostsGood2(repo: Repository<Post>) {
  return repo
    .createQueryBuilder('post')
    .leftJoinAndSelect('post.author', 'author')
    .leftJoinAndSelect('post.category', 'category')
    .leftJoinAndSelect('post.tags', 'tag')
    .select([
      'post.id', 'post.title', 'post.slug', 'post.publishedAt',
      'author.id', 'author.name', 'author.avatarUrl',
      'category.id', 'category.name',
      'tag.id', 'tag.name',
    ])
    .where('post.published = :published', { published: true })
    .orderBy('post.publishedAt', 'DESC')
    .take(20)
    .getMany();
}

// src/schema.rs（由 diesel print-schema 自动生成）
diesel::table! {
    users (id) {
        id -> Uuid,
        email -> Varchar,
        name -> Varchar,
        avatar_url -> Nullable<Text>,
        is_active -> Bool,
        role -> Varchar,
        created_at -> Timestamp,
        updated_at -> Timestamp,
    }
}
 
diesel::table! {
    posts (id) {
        id -> Uuid,
        title -> Varchar,
        content -> Nullable<Text>,
        slug -> Varchar,
        published -> Bool,
        published_at -> Nullable<Timestamp>,
        author_id -> Uuid,
        category_id -> Nullable<Uuid>,
        created_at -> Timestamp,
        updated_at -> Timestamp,
    }
}
 
diesel::table! {
    categories (id) {
        id -> Uuid,
        name -> Varchar,
        slug -> Varchar,
        parent_id -> Nullable<Uuid>,
    }
}
 
diesel::table! {
    comments (id) {
        id -> Uuid,
        content -> Text,
        author_id -> Uuid,
        post_id -> Uuid,
        parent_id -> Nullable<Uuid>,
        created_at -> Timestamp,
    }
}
 
diesel::table! {
    tags (id) {
        id -> Uuid,
        name -> Varchar,
    }
}
 
diesel::table! {
    posts_tags (post_id, tag_id) {
        post_id -> Uuid,
        tag_id -> Uuid,
    }
}
 
diesel::table! {
    profiles (id) {
        id -> Uuid,
        bio -> Nullable<Text>,
        user_id -> Uuid,
    }
}
 
diesel::joinable!(posts -> users (author_id));
diesel::joinable!(posts -> categories (category_id));
diesel::joinable!(comments -> users (author_id));
diesel::joinable!(comments -> posts (post_id));
diesel::joinable!(posts_tags -> posts (post_id));
diesel::joinable!(posts_tags -> tags (tag_id));
diesel::joinable!(profiles -> users (user_id));
 
diesel::allow_tables_to_appear_in_same_query!(
    users, posts, categories, comments, tags, posts_tags, profiles,
);

// src/models/user.rs
use diesel::prelude::*;
use uuid::Uuid;
use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
 
/// 查询用：从数据库读取完整用户记录
#[derive(Queryable, Selectable, Debug, Serialize)]
#[diesel(table_name = crate::schema::users)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct User {
    pub id: Uuid,
    pub email: String,
    pub name: String,
    pub avatar_url: Option<String>,
    pub is_active: bool,
    pub role: String,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}
 
/// 插入用：创建新用户
#[derive(Insertable, Debug, Deserialize)]
#[diesel(table_name = crate::schema::users)]
pub struct NewUser<'a> {
    pub email: &'a str,
    pub name: &'a str,
    pub avatar_url: Option<&'a str>,
    pub role: Option<&'a str>,
}
 
/// 更新用：部分更新用户信息
#[derive(AsChangeset, Debug, Deserialize)]
#[diesel(table_name = crate::schema::users)]
pub struct UpdateUser<'a> {
    pub name: Option<&'a str>,
    pub avatar_url: Option<Option<&'a str>>,  // Option<Option<>> 支持设置为 NULL
    pub is_active: Option<bool>,
    pub role: Option<&'a str>,
}
 
/// 精简查询用：只返回必要字段（避免 SELECT *）
#[derive(Queryable, Selectable, Debug, Serialize)]
#[diesel(table_name = crate::schema::users)]
pub struct UserSummary {
    pub id: Uuid,
    pub name: String,
    pub avatar_url: Option<String>,
}

// src/models/post.rs
use diesel::prelude::*;
use uuid::Uuid;
use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
 
#[derive(Queryable, Selectable, Debug, Serialize, Associations, Identifiable)]
#[diesel(table_name = crate::schema::posts)]
#[diesel(belongs_to(crate::models::user::User, foreign_key = author_id))]
pub struct Post {
    pub id: Uuid,
    pub title: String,
    pub content: Option<String>,
    pub slug: String,
    pub published: bool,
    pub published_at: Option<NaiveDateTime>,
    pub author_id: Uuid,
    pub category_id: Option<Uuid>,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}
 
#[derive(Insertable, Debug, Deserialize)]
#[diesel(table_name = crate::schema::posts)]
pub struct NewPost<'a> {
    pub title: &'a str,
    pub content: Option<&'a str>,
    pub slug: &'a str,
    pub author_id: Uuid,
    pub category_id: Option<Uuid>,
}
 
/// 文章摘要（列表展示用）
#[derive(Queryable, Selectable, Debug, Serialize)]
#[diesel(table_name = crate::schema::posts)]
pub struct PostSummary {
    pub id: Uuid,
    pub title: String,
    pub slug: String,
    pub published_at: Option<NaiveDateTime>,
    pub author_id: Uuid,
}

// src/repositories/post_repository.rs
use diesel::prelude::*;
use diesel::pg::PgConnection;
use diesel::dsl::count;
use uuid::Uuid;
use crate::schema::{posts, users, comments, categories};
use crate::models::post::{Post, PostSummary, NewPost};
use crate::models::user::{User, UserSummary};
 
/// 获取已发布文章（含作者信息，使用 JOIN 避免 N+1）
pub fn get_published_posts_with_authors(
    conn: &mut PgConnection,
    limit: i64,
) -> QueryResult<Vec<(PostSummary, UserSummary)>> {
    posts::table
        .inner_join(users::table.on(posts::author_id.eq(users::id)))
        .filter(posts::published.eq(true))
        .order(posts::published_at.desc())
        .limit(limit)
        .select((PostSummary::as_select(), UserSummary::as_select()))
        .load::<(PostSummary, UserSummary)>(conn)
}
 
/// 分页查询（含搜索）
pub fn search_posts(
    conn: &mut PgConnection,
    search: Option<&str>,
    category_id: Option<Uuid>,
    page: i64,
    page_size: i64,
) -> QueryResult<(Vec<(PostSummary, UserSummary)>, i64)> {
    let mut query = posts::table
        .inner_join(users::table.on(posts::author_id.eq(users::id)))
        .filter(posts::published.eq(true))
        .into_boxed();
 
    if let Some(search_term) = search {
        let pattern = format!("%{}%", search_term);
        query = query.filter(
            posts::title.ilike(pattern.clone())
                .or(posts::content.ilike(pattern))
        );
    }
 
    if let Some(cat_id) = category_id {
        query = query.filter(posts::category_id.eq(cat_id));
    }
 
    // 计数查询
    let total = query
        .count()
        .get_result::<i64>(conn)?;
 
    // 数据查询
    let data = query
        .order(posts::published_at.desc())
        .offset((page - 1) * page_size)
        .limit(page_size)
        .select((PostSummary::as_select(), UserSummary::as_select()))
        .load::<(PostSummary, UserSummary)>(conn)?;
 
    Ok((data, total))
}
 
/// 聚合查询：文章评论统计
pub fn get_post_comment_stats(
    conn: &mut PgConnection,
    limit: i64,
) -> QueryResult<Vec<(Uuid, String, String, i64)>> {
    posts::table
        .inner_join(users::table.on(posts::author_id.eq(users::id)))
        .left_join(comments::table.on(comments::post_id.eq(posts::id)))
        .filter(posts::published.eq(true))
        .group_by((posts::id, posts::title, users::name))
        .select((
            posts::id,
            posts::title,
            users::name,
            count(comments::id),
        ))
        .order(count(comments::id).desc())
        .limit(limit)
        .load::<(Uuid, String, String, i64)>(conn)
}
 
/// 事务操作：创建文章并关联标签
pub fn create_post_with_tags(
    conn: &mut PgConnection,
    new_post: &NewPost,
    tag_ids: &[Uuid],
) -> QueryResult<Post> {
    conn.transaction(|conn| {
        // 插入文章
        let post = diesel::insert_into(posts::table)
            .values(new_post)
            .returning(Post::as_returning())
            .get_result(conn)?;
 
        // 关联标签
        if !tag_ids.is_empty() {
            use crate::schema::posts_tags;
            let values: Vec<_> = tag_ids
                .iter()
                .map(|tag_id| {
                    (
                        posts_tags::post_id.eq(post.id),
                        posts_tags::tag_id.eq(*tag_id),
                    )
                })
                .collect();
 
            diesel::insert_into(posts_tags::table)
                .values(&values)
                .execute(conn)?;
        }
 
        Ok(post)
    })
}