使用Knex限制联表查询仅返回一行的实现方案问询

这个问题我之前也碰到过，核心就是要先给每个帖子线程找到对应的第一条帖子，再和线程表关联就行。给你两种常用的解决方案，你可以根据自己的需求选择：

方法一：子查询分组取第一条帖子ID #

这种方法兼容性较好，适合大多数数据库，思路是先通过分组查询拿到每个thread_id对应的第一条帖子ID（按ID升序取最小的ID，对应最早创建的帖子），再通过这个ID关联帖子表拿到内容：

function getByGroup(groupId) {
  // 子查询：获取每个thread对应的第一条帖子ID
  const firstPostIds = knex('forum_posts')
    .select('thread_id', knex.raw('MIN(id) as first_post_id'))
    .groupBy('thread_id');

  return knex('forum_threads')
    .select(
      'forum_threads.id',
      'forum_threads.updated_at',
      'forum_posts.content'
    )
    .where('forum_threads.group_id', '=', groupId)
    // 关联子查询，拿到每个线程对应的第一条帖子ID
    .leftJoin(firstPostIds, function() {
      this.on('firstPostIds.thread_id', '=', 'forum_threads.id');
    })
    // 通过帖子ID关联帖子表，获取对应内容
    .leftJoin('forum_posts', function() {
      this.on('forum_posts.id', '=', 'firstPostIds.first_post_id');
    })
    .orderBy('forum_threads.updated_at', 'desc'); // 保持原有的排序逻辑
}

如果想按创建日期来筛选第一条帖子，只需要把子查询里的MIN(id)改成匹配最早创建日期的ID即可，调整子查询如下：

const firstPostIds = knex('forum_posts')
  .select('thread_id', knex.raw('id as first_post_id'))
  .whereIn('id', function() {
    this.select(knex.raw('id'))
      .from('forum_posts as fp')
      .whereRaw('fp.thread_id = forum_posts.thread_id')
      .orderBy('fp.created_at', 'asc')
      .limit(1);
  })
  .groupBy('thread_id');

方法二：使用窗口函数（更灵活） #

如果你的数据库支持窗口函数（比如PostgreSQL、MySQL 8.0+），可以用ROW_NUMBER()给每个线程的帖子按指定规则排序，然后只取行号为1的记录，这种方法在需要复杂排序规则时更灵活：

function getByGroup(groupId) {
  // 子查询：给每个线程的帖子编号，按ID升序取第一条
  const rankedPosts = knex('forum_posts')
    .select(
      'thread_id',
      'content',
      knex.raw('ROW_NUMBER() OVER (PARTITION BY thread_id ORDER BY id ASC) as rn')
    )
    .as('rankedPosts');

  return knex('forum_threads')
    .select(
      'forum_threads.id',
      'forum_threads.updated_at',
      'rankedPosts.content'
    )
    .where('forum_threads.group_id', '=', groupId)
    // 关联时只取每个线程行号为1的帖子
    .leftJoin(rankedPosts, function() {
      this.on('rankedPosts.thread_id', '=', 'forum_threads.id')
         .andOn('rankedPosts.rn', '=', 1);
    })
    .orderBy('forum_threads.updated_at', 'desc');
}

如果要按创建日期排序，只需要把ORDER BY id ASC改成ORDER BY created_at ASC即可。

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