如何基于PostgreSQL与Prisma实现帖子的「热门」排序？

前提：确认Prisma Schema关联 #

首先确保你的Prisma Schema中Post和PostVote的关联配置正确，保证能正常统计单帖投票数：

model Post {
  id        Int      @id @default(autoincrement())
  name      String
  timestamp Int      // 建议用秒级Unix时间戳，需与后续计算的当前时间单位统一
  votes     PostVote[]
}

model PostVote {
  userId Int
  postId Int
  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)

  @@id([userId, postId]) // 复合主键，避免同一用户重复投票
}

方案1：使用Prisma原生API结合自定义排序表达式 #

利用Prisma的groupBy聚合投票数，再通过sql模板编写热门排序的计算公式（以Reddit经典热门算法为例）：

import { PrismaClient, sql } from '@prisma/client'

const prisma = new PrismaClient()

async function getHotPosts(limit: number) {
  // 获取当前秒级时间戳，与Post表的timestamp单位对齐
  const currentTimestamp = Math.floor(Date.now() / 1000)

  return prisma.post.groupBy({
    by: ['id', 'name', 'timestamp'], // 需包含所有要查询的非聚合字段
    _count: {
      votes: true // 统计每个帖子的投票总数
    },
    take: limit,
    // 核心排序逻辑：(投票数-1) / (发布时长+2)^1.8，数值越高排名越靠前
    orderBy: sql`
      (_count_votes - 1) / POWER((${currentTimestamp} - timestamp) + 2, 1.8) DESC
    `
  })
}

方案2：直接使用Raw SQL（适配复杂排序算法） #

如果需要更复杂的排序逻辑（比如Wilson得分算法），直接用Prisma的$queryRaw执行原生SQL会更灵活：

async function getHotPosts(limit: number) {
  const currentTimestamp = Math.floor(Date.now() / 1000)

  return prisma.$queryRaw`
    SELECT 
      p.id, 
      p.name, 
      p.timestamp, 
      COUNT(v.post_id) AS vote_count
    FROM "Post" p
    LEFT JOIN "PostVote" v ON p.id = v.post_id
    GROUP BY p.id
    -- 可替换为任意热门排序公式，示例沿用Reddit算法
    ORDER BY (COUNT(v.post_id) - 1) / POWER((${currentTimestamp} - p.timestamp) + 2, 1.8) DESC
    LIMIT ${limit};
  `
}

算法调整说明 #

你可以根据业务需求替换排序公式：

• Reddit热门算法：侧重新内容加权，适合需要给新帖曝光机会的场景
• Wilson得分算法：基于统计置信度排序，适合区分高票老帖与低票新帖的可信度
• 自定义加权：比如投票数 * 0.7 + (当前时间-发布时间)/3600 * 0.3，简单线性平衡票数和时效性

注意事项 #

• 确保timestamp字段的时间单位（秒/毫秒）与当前时间戳完全统一，否则时间差计算会严重失真
• 使用Prisma的sql模板或$queryRaw时，必须通过${}传递参数，避免SQL注入风险
• 若Post表包含更多字段，需在groupBy或SELECT语句中添加所有非聚合字段

内容的提问来源于stack exchange，提问作者Astro Bear